Computer Support Forum

Solved: AC2007 Find Date()-1 And Date()-2 in Query

Question: Solved: AC2007 Find Date()-1 And Date()-2 in Query

Hello, I am having trouble finding the previous 2 days totals in a query. I have a totals query with the following fields:
JobID - Criteria: Between 500 And 599
WorkType
JulianDate
Batch
TotalProcessed - calculated field, grouped on Sum. sums field entry
ProdDate - Short Date - Grouped on Where - Criteria Date()-1 Or Date()-2

Today's Julian Date is 199 so query should give me Julian Dates 197 & 198, but instead returns Julian Dates 188 - 196.

Thank you for your help!

Relevance 100%
Preferred Solution: Solved: AC2007 Find Date()-1 And Date()-2 in Query

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Solved: AC2007 Find Date()-1 And Date()-2 in Query

The criteria that you should use for a date period is
Between Date()-1 and Date()-2

2 more replies
Relevance 79.46%

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Code:
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

Answer:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

16 more replies
Relevance 92.25%
Question: Solved: Query date

Access 2003
I am trying to query a table that contains only dates from 1 month, for example 09/01/2009 thru 09/30/2009 table field name is [ACTFINISH]

How do I write the query to return only the month and year, for example 09/2009 ???

Thanks, Joe
 

Answer:Solved: Query date

9 more replies
Relevance 90.2%

Hi,

I believe this is a really easy thing to do but i can not get it to work,

I have a table that contains a name and the dates that they are on holiday. i am wanting to have a query that will show me everyone who is on holiday in February.

I thought this was simple but i have not been able to get it to work, i think i am missing something really really simple.

I have attached a copy of the table, the table is part of a college project and all data is fictional.

The attachment is zipped so it could be attached

Thanks
James
 

Answer:Solved: Access 2007: date Query

James, to find all dates for February can be done in 2 ways, you can use from the 1st (startdate) to the 29th or 28th (eneddate) and then type in the Query Criteria Row
between 01/02/2008 and 29/02/2008
or you could use the Date part or date Format to extract February form you date and then compare it to February, but that only works if you have one years worth of data.
I can't help with your zipped file as I do bnot have Access 2007.
 

2 more replies
Relevance 90.2%

Hi to All!

I have a database of vendors. 2 of the fields are "Exp_Month" & "Exp_Yr". The data for "Exp_Month" is entered as "JAN" etc. The data entered for "Exp_Yr" is "01", etc. This was uploaded from a program called ADPICS and imported into Access.

I am trying to generate a report that will give me all vendors who will expire in 90 days. I was tying to make this happen in a query then would base my report off the query. But I'm having trouble with proper syntax for the date and year.

Will someone please help me??? THANKS!!!!!
 

Answer:Solved: Access 03 - Date Format in Query

ddw, what format are the 2 fields in are they "Date" fields, Text or Numbers?
 

3 more replies
Relevance 90.2%

Hi

I have a Users DB that I cannot make any changes on it. One of the fields has a Date field. I need to run a query on that table to create reports, but the query creates multiple records for the same user with the only difference in the Date filed. I need to have only the records with the latest date in the Date field.

Is there a way to set a query that does this?

Thanks,
Barbos
 

Answer:Solved: access 2003 query by last date

You need 2 queries, the first has the userrecordId and date fields, set Totals>Grouping the userrecordid Grouped and the date set to Max.
Then in the second query you want the main table and the 1st query joined by the userrecordid and the date.
So the first query finds the last and the second show the data from just that record.
 

2 more replies
Relevance 90.2%

Hello,

I'm relatively new to access and need a bit of help (more like a lot).

I have a table that lists record id's, inspection purposes, and inspection dates (looks like this):

1234 Routine 3/30/2013
4567 Investigation 1/20/2013
4567 Routine 2/20/2013
7890 Approval 12/21/2012
7890 Routine 1/12/2013

I want to recover the maximum date for each record id (without duplicate records). I want the result to look like this:

1234 Routine 3/30/2013
4567 Routine 2/20/2013
7890 Routine 1/12/2013

Can anyone explain to me how this can be done?

Thanks much!
 

Answer:Solved: Access Query: Most Recent Date

6 more replies
Relevance 88.97%

Hi Everyone,
I am trying to create a query that selects all records with a future date. The table that I am trying to select the records from is named "Events" and the field containing the date is "Date"
I am using Access 2007 and the date field is formated as 'Date/Time' data. I tried multiples ways, and even tried the 1000 year add date function pasted in below. Does anyone have any insights--I'm sure its something that is quite simple.

Thanks,
Joe

SELECT Events.Date, Events.Event
FROM Events
WHERE (((Events.Date)=DateAdd("yyyy",1000,Now())));
 

Answer:Solved: Access 2007 Future Date Query

6 more replies
Relevance 88.97%

Hi all.

I hope somone can help this is starting to annoy me.

I am building a database for the charity i work for to track our collection boxes that are out and about.
I am trying to put a button in so it will pull out all boxes that have been out for greater than 30 days. To do this i am using the field "Date Out" in the query and the criteria is:

>Date()-30

This works but pulls our 200,000 results when there are only 1400 records in the table.

any ideas?
 

Answer:Solved: Access 2007 Date Query Problem.

7 more replies
Relevance 88.97%

Hello!

I have an issue I've looked at 50 different ways from Sunday and am stuck on the resolution. I am tracking metrics for a project, stored on SharePoint, which I dump into Excel and use Access 2007 for my reports.

The report has multiple date fields, i.e.: R4RPlan_Date, R4RActual_Date, BusPlan_Date, BusActual_Date, etc... on table RICEFW_Dates.

What I am trying to do is set a parameter to query all the fields for a specific date, i.e. <[Report Date], without having to run multiple parameter queries. The end result will be a count of the number of dates in each field meeting the criteria.

The problem comes in when teams complete their criteria before it was planned, i.e., R4RPlan_Date = 3/23/12, R4RActual_Date = 2/6/12, <[Report Date] = 2/22/12.

If I add <[Report Date] as a criteria for the R4RPlan_Date, I will get all the plan dates I'm looking for, but I will not get all of the actuals since the plan date is after the specified [Report Date], but the actual was completed w/in the frame of the parameter. If I put the [Report Date] parameter in the R4RActual_Date field, I get the correct number of actuals, but since I am querying the field with lesser amount of data, I do not get all of the planned dates which meet the [Report Date] parameter. So if the plan date is greater than the parameter date, I want it to show up IF the actual date is not null, otherwise it should follow the parameter criteria (<[Report Date]). This needs to completed fo... Read more

Answer:Solved: Access multiple date query issue

Solved in another forum.
 

1 more replies
Relevance 88.56%

hello to everybody

I need batch code to check if date is modified in three files and that date should be compared with current date (%date%).If so,do some job,otherwise quit (exit /b)

I found a bunch of solutions but what troubles me is date format.
On my machine it is dd.mm.yyyy

Thanks in advance.
 

Answer:Solved: batch to compare file modified date with current date

10 more replies
Relevance 88.56%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 88.15%

I need to set up a query that pulls information matching a certain date of the current month and year. I'm not sure about the syntax. Someone help please?
 

Answer:Solved: Query for specific date, current month and year.

16 more replies
Relevance 88.15%

Can someone help me with this query? I am trying to pull the record with the most recent date. I'm clearly not grasping the concept somehow; what am I doing wrong here? Created an overly simple example, attached.
 

Answer:Solved: Access Query Pulling Record with Most Recent Date

You should not have the FundID and the Nav in the query because their "Grouping" stops the Max from working.
Unless of course you want the max date for each fund.
You should have just the EffDate and then create a query based on that query and the table joined by the date to bring in the other data.
 

3 more replies
Relevance 87.33%

I have a Date/Time field that I need converted to just a Time field.

It comes back with 2/11/12 0:00:00

I need just the 2/11/12

I thought about changing it to a text field but I think there will be an issue with the month because it does not bring back a 0 in from the months with only 1 digit.

This table that I am creating is being sent to a txt file and that is where I see the issue. It looks good in Access, but not the txt file.

Any suggestions?
 

Answer:Solved: Access - Convert Date Time to Date

10 more replies
Relevance 87.33%

Hello,
Long time since I posted here.

Basically, I have an Access database which calculates the cost of jobs by multiplying the number of hours worked by the Labour Rate.
The labour rate changes over time, but we need to keep historical data - i.e. When the labour rate changes I can't just go and change it in the query, I have to amend the formula so it uses the correct labour rate for jobs done on certain dates.

At present, in my query (CALC) I'm using nested Iifs, as follows (note: dates are in UK format):

LabourCost: IIf([JobDate]>=#01/09/2005# And [JobDate]<#01/04/2007#,[HoursWorked]*9.52,IIf([JobDate]>=#01/04/2007# And [JobDate]<#01/06/2007#,[HoursWorked]*9.82,IIf([JobDate]>=#01/06/2007# And [JobDate]<#01/09/2007#,[HoursWorked]*9.85,IIf([JobDate]>=#01/09/2007# And [JobDate]<#01/01/2008#,[HoursWorked]*9.93,IIf([JobDate]>=#01/01/2008# And [JobDate]<#24/01/2008#,[HoursWorked]*11.62,IIf([JobDate]>=#24/01/2008# And [JobDate]<#01/04/2008#,[HoursWorked]*11.68,[HoursWorked]*11.73))))))

The liklihood is that these labour rate changes are going to become more frequent, so I want to scrap the Iifs and use a table instead.

I've already set up a (unlinked) table called LabourRateStd with the following fields:
ID (autonumber)
Date1 (Date)
Date2 (Date)
LabourRate (currency)

The dates and the values have been entered as per the Iif statement above. For the final Date 2, I've just entered a date way off in the future.

So, wha... Read more

Answer:Solved: Access query: check date against range in table and return value???

16 more replies
Relevance 86.51%

Hello everyone:

We are trying to build an update query in excel to update one date in b2 to the date that is a h2. However instead of returning a date it returns random numbers.

Any ideas?
 

Answer:Solved: Building Update SQL Query in Excel Return Wrong Date Format

6 more replies
Relevance 84.46%

For tracking sales i need to get the following formula for Access.

Example:

Nov 20th sales end date. I need the sunday after this date. So Nov 20th becomes Nov 24th. Then I need to take today's date and compare against this date. (which i can do <Now()-14)

But how do I get Nov 20th to become Nov 24th? and for this to work for all dates?
 

Answer:Solved: Access - need to find the next Sunday after a date

I got this to work in excel with A1 being the date entered =A1+(8-WEEKDAY(A1)). I assume similar could be coded in access.
 

3 more replies
Relevance 82.41%

Hi,

I want to find and replace the Date format in word, if there is any macro coding for this. I try to using the following codes

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " 7/"
.Replacement.Text = " 07/"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

But "7" is relates to month, so if there is any code for change the numeric character to specific date format as "mm".

For ex :

BUARDC 04294008295 010321 6000 7/17/2010 0000354652 000224

The above is the example data , in that month needs to added a leading zero.

Thanks in advance
 

Answer:Solved: Find and replace the date format in Word file

16 more replies
Relevance 81.59%

I've taken a copy of each of a set of 5 linked spreadsheets (annual cash flow, profit etc projections) and pasted them into a separate folder, so that I can preserve the originals and also update a set of working copies.

I'm now trying to do a simple find & replace in the NEW set of spreadsheets, to replace a bit of the old file link references with the new address of the other 4 files in the set i.e. a kind of "find all cells with file links including the word 'original' and replace with 'new' " exercise.

First (the dumb bit) - file/edit/find brings up the find dialogue box, but the 'replace' button is greyed out - why???

Next - is there a simpler way of changing the link references than find/replacing bits of their address wherever they appear in each file?
 

Answer:Solved: FIND & REPLACE - possibly my dumbest Excel question to date...

16 more replies
Relevance 81.18%

Windows repeatedly asks me to update with the little yellow exclamation mark in the tray but it always fails and then keeps asking over and over again. It's trying to update something I don't even use I think PowerPoint or something; I had the option off at one point for those programs so it wouldn't keep asking but then a message pops up saying are you sure you want to leave these off you might need them.

Any ideas on how to get past this?
 

Answer:Windows Update...date...date...date....

Have a look at this link and see if it helps.

http://support.microsoft.com/kb/910339/en-us
 

1 more replies
Relevance 80.77%

HI guys,

I am using Microsoft Excel 2003 and am trying to create a scheduler for my work place.
What I have to do is allow the user to enter data in the input sheet (by input meaning Dates) and then accordingly scan the schedule sheet and find the date entered on the input sheet, following which print the appropriate schedule opposite that date for that particular project.

For Example .....
If a person is working on a project called apple in cell D (effectively making it the 3rd project in the list) and the user enters the project start and end dates as 5th June and 13th June. So what I have to do is scan the Schedule sheet for these dates and enter the appropriate text of Start and End before these dates in the D column (i.e under the respective project).

How to do this I am clueless as I am a complete idiot as far as excel is concerned. SO any idea or any sort of help as to how this can be achieved will help.

The sheet must be automated as far as possible and there are about 25 to 30 projects which need to be updated into the sheet before the final output.

Thanks in advance.
 

Answer:Solved: Formula to find a date and enter schedule in a specific cell in Excel

13 more replies
Relevance 80.77%

Hi, I've got something that keeps going wrong.\
I have a large file which I use at work to register and plan my colleague's tasks and resgiter (outside our regular vacation/sick/abscence, etc.) the different calls they are working on allocating this. But that works, that's not the issue.
I have a sheet named "Medewerkers", yes it's Dutch for Employees of Colleagues.

I extracted and stripped this portion. (June 16: removed this attachment, correct attachment in final post)

On this sheet double clicking in Column A on the name will open a userform to edit this one or if you double click the first empty row below it you get a blank form to enter the data.
Since I regularly ran into problems useing the ....ocx calendar picker I wrote my own date picker and it works. Using son supporting code I found the funtion GetDateInLocalFormat() does it's job.

But now the issue:

The person on the sheet has as starting date 01-03-2012 (in Dutch format this March 1, 2012) and ends August 2012

On my system and at work The date pickers selects it correctly, show is and the userfrom shows it as 01-03-2012 but when I press 'Vastleggen" (Commit) the date is saved as 03-01-2012 which in Dutch January 3, 2012 when I double click again the datpicker shows the default data as January 3, 2012.

The cell's format is dd-mm-yyyy;@ the system's settings are for code 31 which is Dutch and I have pasted screen shost of the settings that show this.

I... Read more

Answer:Solved: Excel (2010) Date format issue dd-mm-yyyy and I can't find the solution!

I changed my code a littel and instead of passing the date in local format I pass it as a serial date and then there is no doubt and the result is correct. But if somebody can offer a better solution, I'm all eyes and ears, and ... maybe it will help others who struggel with date values and the international settings
 

2 more replies
Relevance 80.36%

Hi There

I can't seem to get this line of macro to work properly

If ActiveCell<=Range("C4") then......(Some lines get deleted)

Both the active cell and Range("C4") have dates in them. This line of code does not work properly. It does't matter if the date in the Active Cell is greater than the date in Range("C4"), the macro still executes the next lines of code and still deletes lines that it should not delete.

What am I doing wrong??

Mario
 

Answer:Solved: Date: VBA - One date less than another date

This would happen if either or both of the cells being evaluated are not dates or if the ActiveCell is blank. Try this test to see if this is the case; in either case you should use the DateValue function when evaluating ActiveCell vs C4. This is because the VBA interpretation of a date is much more forgiving than the Excel interpretation. VBA will interpret anything that looks like a date as a date e.g. " 12/21/2013" (with the leading spaces) will be interpreted by Excel as text whilst VBA will recognise it as a date.

Code:
Sub test()
If IsDate(ActiveCell) = False Then
MsgBox (ActiveCell.Address & " is not a Date")
ElseIf IsDate(Range("C4")) = False Then
MsgBox ("Cell C4 is not a Date")
Else
If DateValue(ActiveCell) <= DateValue(Range("C4")) Then [B] [COLOR=red]'use this in your macro[/COLOR][/B]
MsgBox ("OK")
End If
End If
End Sub
 

2 more replies
Relevance 80.36%
Question: date query

I am building a database in msaccess and I would like to build a query that has a criteria that will let me enter only the month and year (i.e. jan-05) and it will find all the records that have fit that criteria and ignore the day. Could someone tell me how to do this?
 

Answer:date query

16 more replies
Relevance 80.36%
Question: Date query

I am running query where I want Price data from price table for particular month.Here is my
tables.

See the attachment for tables.


Customer table has Date and Product code and Price table has date, code and price.I need query that will give me price from Price table based on Product code in Customer table.Example: In customer table product 100 sold in Jan , May and Aug. Price table has price for code 100 for Jan which got changed in Feb. then got changed again in April and so on.....I need a query for product 100 which got sold in may in customer table but available price in price table is in April. Query should pick first if the record available in same month (may) in price table if not in may the then the previous month like April.Thanks for help in advance.
 

Answer:Date query

6 more replies
Relevance 79.54%

I am having trouble getting a query to work! I have dates stored which are when a service was last completed eg. DateServiced = 10/07/02. Services have to be completed yearly. I need to enable the user to enter a start date and an end date as to when services are due to be completed again. eg 01/07/03 and 31/07/03 for services due in July. This needs to pull up all records where the DateServiced was in July 2002. I have tried to have an expression in the query which calculates the due date (as below) and then query on the value of this field. It brings up incorrect records. Hope this makes sense!!! I am confused!!!! Please help!!!

Here is the SQL:

SELECT tblHousing.strUPRN, tblHousing.dtmDateServiced, [dtmDateServiced]+365 AS DueDate
FROM tblHousing
WHERE ((([dtmDateServiced]+365) Between [Enter start date] And [Enter end date])) OR ((([dtmDateServiced]+365) Is Null));
UPRN is the primary key and is a reference code.
 

Answer:Date Query in Access

10 more replies
Relevance 79.54%
Question: Access query date

In Access I want to view all the records for one week. I was hoping this would work. "Between [Enter Beginning Date] and [Enter Beginning Date]+6" What am I missing? The goal is to only have to enter one date.
 

Answer:Access query date

mcookaz, it is better to use a form to either enter a date or select a date from an ActiveX calendar, you then use
Between forms![Formname]![Enter Beginning Date] and forms![Formname]![Enter Beginning Date]+6

It can be used on as many queries as you like as well.
 

1 more replies
Relevance 79.54%

Guys - I need to do a date collumn which I want to do a list of dates 01 June 2004 - 2010, but! I do not want it to show saturdays, and sundays, as they are no working days. How would I do this?

Answer:MS Excel date query

there are none of the Excel experts about, but I would suggest you click hereThe best help you can get, believe me!

3 more replies
Relevance 79.54%

Hello,
I am pulling information from another program into Excel via Microsoft Query and one of the columns I want to pull in involves a date a payment is due. I am wondering how to write the query to have it always pull today's date +1 so essentially it would always pull tomorrow's payments due. Thank you!
 

Answer:SQL date query in Excel

...
Where date = DateAdd("d",1,date())
...

I skimped on the initial SQL statement but DateAdd("d",1,date()) will return the next day in the date.
 

2 more replies
Relevance 79.54%
Question: Access query date

Hi,

I'm doing a database at the moment and I'm going to base a report on a query I'm doing. I want to know how many people this business has served over a year.
Can anyone tell me how i can filter down results so that they only fall between a certain year?
Eg i have their arrive data and then add criteria to the query to then filter down those people who have come in 09 from those in 08.
Hopefully that make sense
 

Answer:Access query date

16 more replies
Relevance 79.54%

Is there a way to select all records of a specific month & day, regardless of the year in an MS Access database? For example: I may want to select all records occuring during the week of 5/24 through 6/1. This is for a church db to list anniversaries, birthdays, etc that are happening during said week. Basically, I would like one query to find all 'events' occuring during any given date range.

Any ideas are extremely appreciated!

Thanks,

Ringer

PS. Of course the MS Access Help section hasn't seemed to be very helpful so far.
 

Answer:MS Access date query

Format the date first in the column before setting the criteria. Format([DateField],"m/yy")
 

1 more replies
Relevance 79.54%
Question: Date Picker Query

Hi

I am in the middle of a Biorhythm VB program and am wondering how to use Date Picker

Currently I am working on a phase of 18 days, so I assume MOD18 will come into it somewhere (you can tell I am a beginner)

My Date Picker seems to go back to Jan 1st 1601

On that day, the 18 day cycle is on day 16 of the 18 day cycle.

So what I need to know is - if I use Date Picker to choose a date of, say, March 9th 1967 - how can I get it to calculate what day of the 18 day cycle March 9th 1967 falls on (still assuming that Jan 1st 1601 is day 16 of the 18 day cycle)

AND is there a site which simply explains how to use DP ?

Thanks for looking

Mike
 

Answer:Date Picker Query

What exactly is Date Picker? I know that VB6 has a ton of built-in functions for managing and formatting dates and times, but I'm not framiliar with one named this.
 

2 more replies
Relevance 79.54%
Question: Date Format Query

Hi - Hoping someone can help me with this!I'm using MS Excel 2003.I have about 2000 dates in the following format; dd.mm.yy but I need the dates to display in this format; dd/mm/yy.I've tried selecting the column and then going to Format -> Cells -> Number -> Date -> dd/mm/yy but it doesn't change the exisiting format.I've also tried Format -> Cells -> Number -> Custom --> dd/mm/yy to no provail.I've attempted creating a basic macro where I record changing the date manually, but when I run it the actual dates change to the date I changed manually whilst recording the macro.Also Data -> Text to Columns --> Delimited --> Next --> Next doesn't offer a solution to my problem either :(Does anybody out there know how I can get arounf this?

Answer:Date Format Query

Hi,The reason for your problem is that Excel did not recognize dd.mm.yy as a date, and it has left it as text.Because it is text, Excel can't display the date in different formats.The solution is to create a date that Excel recognizes, out of the text.If you have a date (dd.mm.yy) in cell A11 for example, put this formula in the adjacent cell:=DATE(RIGHT(A11,2),MID(A11,4,2),LEFT(A11,2))The formula can be dragged down alongside columns of dates as required.Copy the new date cells and use PasteSpecial - Paste Values, to paste the new Excel dates back into the cells with the original dates. Format the cells as required, e.g., dd/mm/yyRegards

16 more replies
Relevance 79.54%

I need to set up a query that will look for the latest date so I have a single record for each date. Can anyone help?

Basically, I have duplicate part numbers - each having their own date revision.

123456 Rev. 3/3/03
123456 Rev. 4/4/03
888888 Rev. 5/5/03
999999 Rev. 2/2/03
999999 Rev. 6/6/03

After the query, I want these results:

123456 Rev. 4/4/03
888888 Rev. 5/5/03
999999 Rev. 6/6/03

Thanks,
mslieder
 

Answer:Query on the latest date

7 more replies
Relevance 78.72%

I am looking to run a query on a date field to begin on a date say Monday 08/25/2008 and having only every other Monday show up or be eliminated (whichever is easiest).

My data is split into two tables one with the (Dates) in one field and (days) in another going out several years and then the (start date) in another table.

[Date][Date Tbl],[Day][Date Tbl] comparing to [Bi-Weekly Start Date][Team_Mbrs] and showing [ID][Team_Mbrs], the employee.

I am attempting to show the days off an employee will have when they start a day off and have every other off. I hope this makes sense.

Thanks.
 

Answer:Access 2000 Query on Date

Date()+7
 

2 more replies
Relevance 78.72%

I am new to using Access 2007. Please help if possible. I have an access 2007 query and I need to sort it by dates. I have 3 field dates... need to sort each field if not null.

I need for it to sort by field1 date first, and it field1 date is null, sort by field2, and if field2 date is null, sort on field3.

I hope I explain it good enough...I am a rookie.
 

Answer:Access 2007 Query Date

Hi and welcome to the forums

Please only make one thread per issue, as having duplicates will confuse things . I've removed your old thread and moved this one to a section that may get some more answers.
 

3 more replies
Relevance 78.72%

Can someone help please?

I'm setting up a query in Excel from an SQL database. How do I (or what do I) do to ensure only todays data is shown - I suppose I'm looking for an"=TODAY" type thing but don't know enough to be able to do it.

Thanks in anticipation.

Moll )
 

Answer:Excel query on Date field

it can depend on how the date system is set up but the following page has pretty much every SQL date function going

http://www.connx.com/products/connx/Connx%208.5%20UserGuide/sql_date.htm
 

1 more replies
Relevance 78.72%

I'm trying to create a query that prompts the end-user for the date fields to search.

For example, I have an Invoice table that contains a "InvoiceDate" field. I want the end-user to be able to search for invoice dates between 'date A' and 'date B'.

More or less, I need to know how to setup the "criteria" in design view of the query.
Any/all help is appriciated...

I'm using MS Access 2K
 

Answer:MS Access 2K: Date (Criteria) Query

Between [Begin Date"] and [End Date:]

It will prompt them for each value.
 

2 more replies
Relevance 78.72%

Hi!
I have one question about Date() in Access:
Ex:
One customer ordering one product so there is Date Order (2-Jan-02) in table.
I want to know how can I make one query which can check that the period of time from Date Order to Now is over 2 years or not. I have tried using Date() and Now() but could not.
Please help me
Thanks a lot!
 

Answer:Date query in MS Access 2000

dungnhat,

the following code snippet will return the number of days between OrderDate and date now.

Sub test()

Dim datenow As Date
Dim OrderDate As Date

datenow = Now
OrderDate = DateValue("1 January, 2002")

MsgBox DateDiff("d", OrderDate, datenow)
End Sub

this will return 499 days

you can then calculate if the elapsed time is greater than 2 years by comparing the result of the date function to 730 days (2years)

if (DateDiff("d", OrderDate, datenow) < 730 then
do something
else
do someting else
end if

If you use the yyyy interval argument for the datediff function the returned answer possibly will not be the result you expect.

For example using the values above
DateDiff("yyyy", OrderDate, datenow)

will return 2 years

Hope this helps,

Tom
 

1 more replies
Relevance 78.72%

how can i create or enter a multiple date range for a query. The date is in a 5/28/2007 format. I tried what I thought would work.
 

Answer:multiple date range for a query

7 more replies
Relevance 78.72%

I.ve developed a Work Order form in access that has an request date field. I would like to create an query that will show the request from the last month or 30 days. I looked at datediff but I'm not too familar on the syntax. Please Help.
 

Answer:Date query in Access 2000

12 more replies
Relevance 78.72%

Hey all, I have created a database that I need to retrieve information from a table (linked table with another database) based on the user typing in a start date and an end date.

The original table has the date fields as text (accepts date and time).

So far I have this in the query;

>=[Start Date] & "*" And <=[End Date] & "*"

This works if the data in the original table is in the same month e.g.
if i want to retrieve data from 18/02/2013 to 22/02/2013 then it works, however if I cross over months e.g. 28/01/2013 to 01/02/2013 then it brings up a blank table without any data.

I have also worked with BETWEEN [Start Date] & "*" AND [End Date] & "*" but this doesn't seem to work either.

Any suggestions will be much appreciated
 

More replies
Relevance 78.72%

Help! I'm using Microsoft access 2003. I have clients that are booked in for a block of ten treatment sessions. I can only schedule SMS reminders for them a week in advance. I need to create a query that allows me to see who is due for an SMS reminder in the next two weeks ie who has a treamtent coming up. Treatments are entered in as Treatment 1, Treatment 2 etc. When I add all the 10 treatments on as fields in a query in design view and enter the code <Date()+14 for all of them, it comes up with nothing. However, the query works if I only have Treatments 1 and 2 added, as all clients will have these treatments coming up (they aren't up to the later treatments yet). Is there a way that the fields can be searched as an "or" rather than "and" ? Do I need to change the code? I don't want to have to run a separate query for all ten treatments!
I'm an academic and a health practitioner and really not very good on Access! hope you can help
 

Answer:Date query in Microsoft Access

In your query designer if you put the criteria all all on the same criteria row it will be an AND. If you put them on separate lines for each column it will be OR
 

1 more replies
Relevance 78.72%

Hello,
I have two date fields, Arrived and Passed.
I need to be able to count the numbers of arrivals and passes for each date in a date range entered by the user.

So, I just need an expression that effectively combines the two fields. It is not an either/or situation - the item could pass on the same day it arrives.

The result I'm after would include each date in the date range even if there were no arrivals or passes on that date. So if I included parameter criteria and the user entered the date range 15/05/06 to 20/05/06, the result should look something like:

EventDate__Arrivals___Passes
15/05/06____10______20
16/05/06_____6______10
17/05/06_____0_______0
18/05/06____18_______5
19/05/06_____0______12
20/05/06_____0_______0

I'm sure this is dead easy, I just can't find what how to create the EventDate expression.
 

Answer:MS Access - Simple date query?

14 more replies
Relevance 77.9%

I got a Refurbished Dell Latitude D600 Laptop from ebay yesterday on Tuesday, September 26th 2017, It had Intel Pentium M (Non-Centrino) and Windows XP Pro SP3, but when i boot it up, it resets to The Default BIOS Date, can someone help me?

Answer:Dell Latitude D600 Current Date Resets to The Default BIOS Date

The coin cell battery on the motherboard that keeps time while the system is powered off is probably dead.  Not too surprising given that the D600 is about 15 years old at this point.  If you open the system up, it should be easy to find, so just note the type of battery, e.g. CR2032, and then buy that type of battery at any grocery/convenience store.  Should only be about $2-3.

2 more replies
Relevance 77.9%

Greetings, and thanks in advance for your help..

Awhile back I formatted my Windows 10 PCs to show ddd dd-mmm-yyyy on my taskbar clock. This worked happily on my desktop and laptop for months, maybe even a year +

Now (probably starting in the past couple days, if not today), on one of those two PC's, this format will no longer display. It is Okay on the other PC.
What has changed that the taskbar clock space used to expand to fit the "short date" format, but now it will no longer expand - and instead just refuses to display the date at all. (NOTE: I've verified the issue by testing shorter formats -- short formats, like "ddd" show up correctly. Only if you request a longer format will the date not display - evidently because it is too long for the clock area. )

Additional Notes that may help:

* "Working" pc is running build 10586 PC with too small a clock space that won't expand -- Build 14393

*Other differences: Notification/Action center Icon is left of clock on the working PC - Right of clock on the non-working
Right- Clicking on task bar gives "properties" option on working PC -- But "Settings" option on the non-working

( I don't want to prejudice responses - but clearly the implication to me is that some recent OS update is most likely the culprit.)

Thank You

More replies
Relevance 77.9%

Greetings, and thanks in advance for your help..

Awhile back I formatted my Windows 10 PCs to show ddd dd-mmm-yyyy on my taskbar clock. This worked happily on my desktop and laptop for months, maybe even a year +

Now (probably starting in the past couple days, if not today), on one of those two PC's, this format will no longer display. It is Okay on the other PC.
What has changed that the taskbar clock space used to expand to fit the "short date" format, but now it will no longer expand - and instead just refuses to display the date at all. (NOTE: I've verified the issue by testing shorter formats -- short formats, like "ddd" show up correctly. Only if you request a longer format will the date not display - evidently because it is too long for the clock area. )

Additional Notes that may help:

* "Working" pc is running build 10586 PC with too small a clock space that won't expand -- Build 14393

*Other differences: Notification/Action center Icon is left of clock on the working PC - Right of clock on the non-working
Right- Clicking on task bar gives "properties" option on working PC -- But "Settings" option on the non-working

( I don't want to prejudice responses - but clearly the implication to me is that some recent OS update is most likely the culprit.)

Thank You

Answer:Taskbar Time / Date area quit expanding to fit desired date format

I had a similar problem which has been greatly ameliorated thanks to a kind respondant - see https://www.tenforums.com/installatio...-new-post.html

1 more replies
Relevance 77.9%

Help! I am using a Seagate Central 4TB NAS drive attached to my router via Ethernet. It has been working fine so far for all kinds of file activities. But whenever I modify a file in one of its folders, the creation date for the file also updates and is
changed to the same time as the new modified date.
Maybe relevant details:
I can move/copy folders to the drive and they maintain their created dates.
It happens for both a private user share on the NAS and for the Public share on it.
It happens for at least image files and text files.
I am able to edit files across two computers on the network just fine, the create date stays the same.
This happens from a new Windows 10 machine and Vista machine both.
I notice sometimes the date created seems to stay same until I access the file the next time.
I can't update the NAS firmware because of error.

If it could be a permissions problem, I have been unable to change it from:
Everyone: Special Permissions only
nobody (Unix Group\nobody): Special Permissions only
nogroup (Unix Group\nogroup): Special Permissions only
(Those were there automatically, they are not something I manually put there. I get "Access is denied" whenever try to add permissions or change ownership.)

I have Googled many phrases of this problem far and wide and come up with nothing. I really need to save the original create date because that's how I sort them in programs like Windows Photo Gallery or Picasa. Does anyone know what's goin... Read more

More replies
Relevance 77.9%

Hi all
Have started up a new machine and upgraded from Access 2000 to 2003 and transferred our database over. We have just over 5,000 records in the contact table which we update daily with the current date as various repeat contacts are made.
The relevant field is set to ‘Long date’.
Every evening we have a Word 2003 file that completes a mail merge to a database query that pulls records with the current date. That query obtains its data from the contacts table. Or at least, it used to.
Since the change something has gone wrong and I cannot sort out what. The query will not let me use the =Date() function in the criteria row. As a test I set up a dummy database with a simple table and a single query. In the query grid I added =Date() in the criteria row and low and behold it worked. So obviously there is a problem in the original table. Now I am lost. Does anyone have any pointers please?

BigAl
 

Answer:Using the =Date() function in Access 2003 query

That is odd I have just answered another poster with date problems.
can you open your New table and check the Actual dates in the field ensuring that you expand it>
Does it have the "Time" in the field with the date, you may have to change the Date/Time format to Short or long time to see it?
Has the Date field always been "Long" date rather than Short date?
Also try in the Query in a new Column
Format([Your Date Field],"dd/mm/yyyy") and put the date() criteria in that column instead.
I have just checked it and it works whereas Long date with a time element won't.
 

1 more replies
Relevance 77.9%

I am trying to design a query where only records with the current date are printed. When I put &lt;Now() in the criteria of the query for some reason records with today’s date and less than today’s date are printed When I put &gt;Now() only records greater than are printed, when I put =Now() I get nothing. I would appreciate any input thanks.
 

Answer:ACCESS - Query base on current date

=NOW() means RIGHT NOW, not TODAY, which is what you want.

=today()

=now() is 10:29 EST on 10/3/2001 (at least right now it does, LOL)

=today() is 10/3/2001
 

3 more replies
Relevance 77.9%

Trying to finish my group project in my senior AIS class. I need to design a query that pulls the date value from an employee list if the current date is 6 months from the date when employment began.

This is the only part of the query that I cannot seem to get to work and is holding up the update function of the query to increase the employee's pay rate.

I followed what was in the help function of Access and thought I had corrected it look back over 6 months but it didn't work at all. Myself and the rest of my group would appreciate the help.

The spider web as we have nick named it.

Disclaimer: The above is a fictional company created for the project including fictional data for testing purposes. Just saw the disclaimer at the header of the business app forum and wanted to get that in there.
 

Answer:Access 2007 and date query functions

16 more replies
Relevance 77.9%

I have a table in which I want to calculate Field 1 to pull all dates between 21-56 days. Does anyone know how I should type the expression?

Also, in the same table, I'd like to write a query to calculate a certain number of days between Field 2 and Field 3. I would like to pull all dates that are within 42 days. How should I type this expression? Also, where exactly in Design query should I type this expression? I know it would go under criteria, but under what field?

Lastly, I would also like to run a query that will calculate the number of weeks/days between Field 2 and Field 3.

If anyone has any tips on how to create calculated date expressions, I would very much appreciate it.

Thanks in advance for your input.
 

Answer:Access: Writing Date Calculations within a Query

I see no one answered my previous question, but that's okay because I figured out the correct criteria.

Now I have another problem. I have several tables in my database. Table one lists a lot of information related to deliveries. My primary key in this table is SSN. Table two lists demographic information with the primary key being Sample Order. There is a field in table two called SSN that I've linked to table one. I am trying to add the "DOB" and "Sample Order" fields from table 2 to table one. However, table one does not have every record that table 2 has. Table two has 493 records whereas table one has 392 records. Table one has everyone in table two so I figured I should still be able to link the two records so that I can create query that will add the "DOB" field and "Sample Order" field to table one. Is this possible and how? I really would appreciate help on this issue.

Thanks.
 

3 more replies
Relevance 77.9%

For some MS Office buff. Have workbook with 4 sheets with similar tables of data on each sheet.This is a "brand new" document.Sheets labelled FEB / MAR / APR / MAYNow if I format the date fields on all sheets except MAR as dd-MON-2007 ( eg 12-Apr-2007 ) format takes OK. If I attempt to format date fields on MAR sheet ( eg 05-Mar-2007 ) I get ######## returned as the value ? Very peculiar indeed. Any advice ?

Answer:Excel 2000 - date format query

Make the column wider.

3 more replies
Relevance 77.9%

Hi,

I am working with a database that is added to every day and I need to be able to pull out those records that apply to an overnight shift. The start time is given as dd/mm/yyyy hh:mm:ss and I want to be able to select all the records for shifts that started between 19:00 on the day before and 06:00 of the morning I run the query.

As this needs to be done everyday I was hoping that a query could use the criteria of Date()-1 and Date() along with the times to select those records, so I have tried this (and a few variations of this) but nothing has worked so far

SELECT Table1
WHERE StartTime BETWEEN Date()-1 #19:00:00# And Date() #06:00:00#

Can anyone help me with this?

Many thanks!
 

More replies
Relevance 77.9%

Greetings

It's been a long time since I last posted here, but hopefully all you wonderful people will be able to help...

Here is my problem:

A customer of mine has a SQL database where one of the fields is a date/time field that looks like this:

13/02/2003 14:32:58
14/02/2003 08:59:10
15/02/2003 17:09:37

Now, I need to delete all the records for a particular day (One of their HD's failed, and they had to restore the data and lost a day, but the entries are still in the database, and can not be re-entered until the old entries are deleted).

How do I structure a query to delete all the records for a particular day? Preferably, I would like a SELECT statement first, just to check that the records found correspond to the delete criteria, and then the DELETE statement.

Thanks

Reuel Miller

The Ex-NT moderator on this board

Every morning is the dawn of a new error
 

Answer:SQL Query help required with date/time field

Greetings

Not to worry chaps, I figured out the answer all by my lonesome

the statements I used were:

select * from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'

delete from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'
cheers

Reuel Miller
 

1 more replies
Relevance 77.9%

I have a date column displaying all payment dates.

Is it possible for the user to query by year only, thus displaying payments only from the desired year? [DD/MM/####]

I know how to set up input queries, but am not sure how to prompt for only the year correctly. Thanks for any help!!
 

Answer:Date Column, Query Criteria only Year

6 more replies
Relevance 77.9%

Hello.
I'd like to see Long Date and Long Time in my System Tray instead of Short Date and Short Time. 
But due to some restrictions connected with my work I can not modify Short Date & Short Time to look how I want them to look in System Tray.
So basically I need Windows to show what is set as Long Date & Long Time in System Tray instead of showing Short ones.

Is it possible? Maybe some registry tweaks?

Answer:How to display Long Date in System Tray (without modifying Short Date)?

Hi,
There is no workaround to display the Long Date in taskbar without modifying the Short Date parameter.Ivan-Liu
TechNet Community Support

8 more replies
Relevance 77.9%

The title is self-explanatory. So far, I found a software that is able to change this called 'Attribute Changer' but it only changes the date modified in the folder and, when you go into the actual word application > file > info, the date created and modified are not changed. Any help would be greatly appreciated! Thank you!
 

Answer:Changing 'date created' and 'date modified' in Microsoft Word 2013

7 more replies
Relevance 77.9%

Searching for files modified in a specified date range (of the past 3 days) gets a search result list titled "date modified", and with the column heading "Date Modified" but the date listed against a file modified today is wrong. The date displayed matches the file creation date & is within the specified range ... but how do I know what the Search is doing?

Can't trust it... !

Answer:File search (by date range) displays incorrect Mod Date in results

That is very strange. Bump for a possible solution, since I can't think of one off the top of my head.

1 more replies
Relevance 77.9%

It looks like the 31 March deadline for the latest Edge promotions is the date of invoice rather than the date of order. Given Lenovo's reported long build time and slow delivery (= late invoice), I suspect that ording now from the Lenovo web site will see an invoice date outside the qualifying period. Even a dealer who says he can 'get one in' may not deliver and invoice until April or beyond. I think the only option is to find a dealer who unequivocally has current stock.Can anyone shed any light on the likely position. I do, though, wonder about trying to take advantage of Lenovo Cashback at all, given the horror stories reported here.

Answer:Edge 31 March promotion deadline - order date or invoice date?

My experience with cashback is a deathly silence from Lenovo to emails asking for progress on the cash or even recognition that there has been a registration for cashback...Currently considering giving Lenovo some publicity on the matter.Unless you have a lot of time to possibly get no where I would steer clear of the alleged cashback scheme...

1 more replies
Relevance 77.9%

Hi! Does anyone know what is the format of the date in the field "(battery) Manufacture Date" informed by Lenovo Vantage (Hardware Configuration > Energy)? It seems not to follow Windows setting. In my case, it is set to Brazilian Portuguese DD/MM/YYYY, but there is not a leading "0" in the returned value (see attached image). Does it really disregard the Windows setting and presents it in a fixed format (maybe American English)? When was my battery manufactured (Oct. 07 or July 10)?Thanks in advance.  

More replies
Relevance 77.9%

After running a large update of windows on May 18 I found that ALL my files in ALL my folders are showing a Date Modified of May 18 2013. Can't find a similar report of this problem. Any ideas?
Using Windows 7 Professional 64-bit

Answer:File Date Modified reset to today's date after windows Udate

Would this help? enter link description here

1 more replies
Relevance 77.9%

I forgot my new mobile password.Also i remove battery then time setting is now incorrect.I try 5-6 type to set password

Answer:I forgoet my scree lock password?Also i remove battery ow date setting is not current date?

hard reset your phone using following steps :-
1. Turn off your Windows Phone. Use power button to power off the phone.
2. Press and hold Volume down key and connect the charger to phone. You will see a big exclamation mark on the screen.
3. Press these keys one after another in the same sequence. Volume Up, Volume Down, Power, Volume Down.
4. You will see the phone reset and boot normally. You will see wheel gears working on the screen. It will take few minutes.
Your phone will be reset to factory settings and you need to set up the phone again.
Or try to reset phone's setting if it is connected to Wi-Fi or data is enable through your account.

more replies
Relevance 77.49%

Hi all,

I'm new to Windows 7 (Home Premium 32-bit) and I just can't figure out what's going on here! Basically I want my short date format to be dd/mm/yyyy (UK format) e.g. 05/11/2010, but when I key a date into Excel 2002 it is appearing as 05 11 2010. In Control Panel I have the short date format set to ddd dd/MM/yyyy and the example is shown as Fri 05/11/2010, but in Excel all the date formats are shown with space separators, e.g. 14 03 2001! I don't think it's an Excel problem though as in Windows Task Scheduler the dates are shown with spaces there e.g. '19:25 on Mon 01 11 2010'. The date by the clock though is shown correctly with slash separators!

I know in Windows XP you can select the date separator from a drop-down list, but this option seems to be absent in Windows 7. Under HKEY_CURRENT_USER\Control Panel\International in the registry, sDate was set to a space, but changing that to a slash (and rebooting) didn't fix the problem.

Does anyone have any ideas please what's wrong and how to fix it?

Thanks
Steven

Answer:Date format using space as date separator rather than forward slash!

Hi,

The option you're after is controlled from within Excel. If you right-click the cell(s) in question you can select 'format cells' and under 'Date' you can select the desired format.


OS

9 more replies
Relevance 77.49%

I need to return a number associated with a date based on today's date. For example: A1:A24 are numbers 1 to 24 and B1:B24 contain the 15th and 30th for each month of the year; 15-Jan, 30-Jan, 15-Feb, 28-Feb, ... 30-Dec.Cell C1 needs to return the number from A1:A24 based upon today's date compared to the date in B1:B24.For example today is 08/21/15, what number should be returned in Cell C1. It should be 15 as 08/21 is less than 30-Aug.message edited by jmichels

Answer:Return a number based on today's date compared another date.

Try this formula, see how it work for you:=INDEX(A1:B24,MATCH(TODAY(),B1:B24,1),1)This will work for most dates, but errors out if the Date is 01/14/15 or less.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

4 more replies
Relevance 77.49%

Hi all,I need dos command to compare the file creation date and the modified date.My scenario is,If user inputs the folder path to the batch file, i have to provide the file list (which are under sub folders of the given folder) ,which do ot have creation date and modified date as same.can anyone help in thisThanksVanisree

Answer:Dos command to compare file creation date and modified date

You need a CMD.EXE Command Prompt solution, XP does not have MS-DOShttp://ss64.com/nt/ARM Devices are the future, great British Design !

2 more replies
Relevance 77.49%

I'm working on a spreadsheet that lists monthly recurring expenses. I have one cell on the sheet listing the current date (using the "=Now()" formula), but I'd like to have the other cells change at certain times throughout the month.

For example, if one cell currently says "April-15", when the actual date reads "April-16", the respective cell would change to "June-15". This way, whenever the spreadsheet is viewed the next recurring payment date will be seen.

I know this is the wrong tool for something like this - I'm using SharePoint at the office with a calendar list with workflows that shoot off emails in a much better put together format - but this is just for home use and I'd like to keep it all in one document if possible.

Any ideas or suggestions would be greatly appreciated. Thanks in advance.
 

Answer:Excel 2010: Cell Date Change with Current Date

The way I read it was that you have fixed recurring payment dates, so you just want to know the next date based on the day today. Hope the attached might assist.
You need to list the payment dates somewhere and then the formula looks up the next date. You might want to look up today plus 1 (or something) to reflect the fact that if the next payment date is today, there's a good chance you'll not get any unplanned payments on that run.
 

1 more replies
Relevance 77.49%

I have a field that reads 2009-12-30-15.42.22.81298 as text. i want to make in be a date of 12/30/2009 15:42:23 and ignore the milliseconds. Is this possible?

Thanks
Ted
 

Answer:Converting complex date text fields into date or numbers

Ted, what software are you talking about?
Are there always 8 values in the date part?
 

3 more replies
Relevance 77.49%

Hi all,I wish to create a macro that when i click a button i can input what month i wish to print out data for. I have 7 columns (A-G).Date Day Activity Income 02/01/2012 Monday Work (8) £48.00 02/01/2012 HMOT £5.00 02/01/2012 Peter Taylor 134024855 HMOT £5.00 02/01/2012 Susan Taylor 134023422 HMOT £5.00 02/01/2012 Steve Davy 13402737404/01/2012 Wednesday Work (8) £48.00 04/01/2012 HMOT £5.00 04/01/2012 Tom Thompson 134027410 HMOT £5.00 04/01/2012 John Ackroyd 134025131 HMOT £5.00 04/01/2012 Aftab Ilahi 134022546Above is an example of the sheet.In column A we have the date, B we have the day, C we have the activity, D we have the income, E is a cell with the previous month date in to track data for the month, F is a name and G is a number.I wish to be able to print all of january for example, rows A-G by clicking a button and entering something like 'january' or '01/12'. This will then select all of the rows with data in based on the date range in column A and print the selection.Please feel free if that doesn't make sense to ask questions.Thanks in advance.

Answer:acro to input date and print based on date range

Below is the code I have for a macro which I can't seem to get to work right.Thanks.
Public Sub PrintMonth()
On Error GoTo Proc_Error

Dim wksCurr As Worksheet
Dim rngTarget As Range

Dim strInput As String
Dim datInput As Date
Dim datStart As Date
Dim datEnd As Date

strInput = InputBox("input start date for month", "Enter date")
If strInput > "" Then
datInput = CDate(strInput)
If datInput > 0 Then
Set wksCurr = ActiveSheet
Set rngTarget = wksCurr.Columns(4)
datStart = WorksheetFunction.EoMonth(datInput, -1) + 1
datEnd = WorksheetFunction.EoMonth(datInput, 0) + 1
With wksCurr
If .AutoFilterMode Then
.AutoFilterMode = False
End If
.UsedRange.AutoFilter Field:=rngTarget.Column, Criteria1:=">=" & datStart, _
Operator:=xlAnd, Criteria2:="<" & datEnd
.PrintOut
.AutoFilterMode = False
End With

Set rngTarget = Nothing
Set rngDate = Nothing
Set wksCurr = Nothing
End If
End If

Proc_Exit:

Exit Sub

Proc_Error:

Select Case Err
Case Else
MsgBox "Error " & CStr(Err) & ": " & Err.Description
Resume Proc_Exit
End Select

Exit Sub

End Sub

3 more replies
Relevance 77.49%

Somehow the date on one of my old Excel files has turned from 1999-10-04 (i.e. October 4, 1999) to 4617-04-11. How can I change all the rest of the incorrect dates back to what they should be? This is the only date that I can confirm. And I have no idea what the rest of all the dates should be on this spreadsheet. TIA.

Answer:Excel date problem - to convert incorrect date to original

I have no idea what the rest of all the dates should be on this spreadsheetThen how do you purpose to change them, when you don't know what year they are supposed to be?Somehow the date on one of my old Excel filesAny Macros running that may have in some way impacted the dates?How is the data stored? As Text or as a Date?MIKEhttp://www.skeptic.com/

5 more replies
Relevance 77.08%

This question was asked by someone several years ago, but I did not find the complete answer.

I need to know how to create a QBF using a date range as part of the criteria.

I have a form to run a query. The form has a combo box to limit "approved" or "pending" “new” purchase orders (PO) (criteria one), but I would like to add a second criteria for POs created between "07/01/2009" and "07/06/2009" (criteria two).

I want the end-users to be able to select the date range (enter the start date and end date). I don't want to limit their selection.

I know how to use the date range in "Design View" of a query (ex: >=[Start Date] And <=[End Date], or,
Between #07/01/2009# and #07/06/2009#) but I don't know how to implement that into the Form.

I know I need to put something like the following in the query criteria field for PO_Created_Date:

Between [Forms]![Formname]![BeginningDate] And [Forms]![Formname]![Text_EndDate]

But how to make it work in the form?

Please help!

Thanks,

Yuan
 

Answer:Access 2003: Query by Form using a date range

Yuan, welcome to the Forum.
You appear to understand the query requirement, especially the "Between [Forms]![Formname]![BeginningDate] And [Forms]![Formname]![Text_EndDate]" part.
So which part don't you understand?
as long as the you put in the name of your Form in the Fromname brackets and you have 2 fields on the form called BeginningDate and Text_EndDate that Criteria will work.
 

3 more replies
Relevance 77.08%

Hello,

In Access, I am trying to develop a process for generating a unique client number that follows a very specific formula: XXX - XXXX - X - XXXX. The first group should represent the quarter and year when the client is first introduced (non-standard year: Feb-Jan). The second group should be a unique, sequential ID number with leading zeros (missing numbers won't matter and leading zeros seems impossible, so I'm planning to use a DMax calculation on the form that generates a sequential number). The third number represents the client's initial program phase ID. The final number relates to the grant contract ID.

I have generated the number with the following in a Select query:

UniqueNumber: DatePart("q",DateAdd("m",-1,[MonthStart])) & Format([MonthStart],"yy") & "-" & [SeqNum] & "-" & [InitialPhaseID] & "-" & [ContractID]

The table already has a primary key, but since I am required to generate client numbers in this format, I want the UniqueNumber to be saved and searchable. This field never needs to change once the original record is saved. I've tried an Append query, but can't make it work. I assume it's related to the fact that I'm combining Date and Number fields, not to mention the DatePart function. I consistently get a key violation error. Any thoughts on how I can proceed?
 

More replies
Relevance 77.08%

Hi all,

I have problem with query a date range... I know how to use in "Design View" of a query (ex: Between #01/01/2004# and #05/07/2004#) but I don't know how to implement that into the Form, for eg: In Criteria: Between (Forms!FormName!From_textbox) And (Forms!FormName!To_textbox) but it just returns nothing...

Please help!

Thanks
 

More replies
Relevance 77.08%

I will need to combine two tables on a weekly basis. Table 1 has the Product ID and the date and some important data. Table 2 has the Product ID, the date, and some other important data. For table 1, I only need to append the table once a month because the value needed stays consistent. However, for table 2, the values needed change on a daily basis.

How do I create a query that will command you for the date from table 2 and take the values from the month on Table 1?

The ID is not unique in either table, but it is unique for the date in each table.
EX:
ID Date Forecast
1 June, 2012 5
2 June, 2012 3
3 June, 2012 2
1 July, 2012 7
2 July, 2012 9
3 July, 2012 1

Sorry if I'm not too clear in what I need!
 

Answer:Utilizing Date/Time in Query to combine tables

You can "Join" the 2 tables using the date fields, that will show all the values that match from both tables.
You don't actually show the end result you want.
 

3 more replies
Relevance 77.08%

I'm trying to query records based on date and time. We have a database that we print receipts off based on the date we received these records. The end-users of this database have requested that we add a time function to these receipts so that when they print receipts, they are not wasting paper with duplicate receipts that have been printed earlier that day.

Right now, the query prompts the user to enter a "From Date" and a "To Date." What can I add in query so that in the "From Date" also asks for time in military format (or regular, doesn't matter).

Any help is appreciated.

Answer:Microsoft Access Help Needed: Query using Date & Time

this shoudl point you in the right direction

Extract a part of a date or time value

http://office.microsoft.com/en-us/as...984871033.aspx

Using dates and times in Access

http://office.microsoft.com/en-us/as...21033.aspx#b50

1 more replies
Relevance 77.08%

I have a linked Excel spreadsheet to show invoices received and I insert a date when they are paid. I cannot figure out the expression to use to show all records where no date has been inserted. Can anyone help please.Thankyou

More replies
Relevance 77.08%

A while back you (I don't remember the exact members) helped me create a query by form in Access 2000... Thank you. Now I need to know how to create a QBF using a date range as part of the criteria.

I know how to use the date range in "Design View" of a query (ex: Between #07/01/2004# and #07/06/2004#) but I don't know how to implement that into the Form.

As an example, I would want to pull all records of "Apples" (criteria one) sold between "07/01/2004" and "07/06/2004" (criteria two).

Please help!!!
 

Answer:Access 2000: Query By Form Using A Date Range

7 more replies
Relevance 76.67%

So I have this excel spreadsheet and an example of it basically looks like this:
Project Phase Person Capacity Start Date End Date
A Design 1 25% 1/15 3/16
A Model 1 25% 3/17 5/11
A Design 2 25% 1/15 3/16
A Model 2 25% 3/17 5/11

I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

Dates
Person Jan Feb Mar Apr May
1 25% 25% 25% 25% 25%
2 25% 25% 25%

Is this even possible, either in a pivot table, or even using formulas?
 

Answer:Excel 2007 - Start Date and End Date question

You should have posted that in "Business Applications" here: http://forums.techguy.org/16-business-applications/
 

3 more replies
Relevance 76.67%

The AVG on my Dell thinks the Virus database is out of date even though it has the most current update?

How can I change the date back and what might have caused this to happen in the first place. I've heard of some AVG update problem but didn't find much relevant information on it.

Any suggestion or links would be great.

Thanks,
Andre
 

Answer:AVG Free wrong date set; Update thinks it's out-of-date

Check the date on your computer.. And, maybe uninstall AVG and reinstall it..

http://www.majorgeeks.com/AVG_Free_Edition_d886.html
 

2 more replies
Relevance 76.67%

Eg: In the below table (Excel) right now date is numeric want to convert them to DATE format Date of Birth Sex Date of diagnosis1 1/07/41 F 21.02.092 12/06/90 M 2/24/22614 1/07/62 M 11/02/095 1/07/48 F 26.05.096 1/07/39 F 11.02.097 1/07/76 F 2.05.098 1/07/60 F 30.05.099 1/07/80 F 1.03.0911 1/07/56 F 22.03.0914 1/07/65 F 22.02.0916 1/07/74 M 22.02.0917 1/07/67 F 24.03.0919 1/07/33 M 25.02.0920 1/07/45 F 21.03.09

Answer:Convert multiple date entries to DATE format

Whoever wrote the file couldn't even decide on a standard date format. Needs two routines. I prefer to normalize to yyyy/mm/dd.

7 more replies
Relevance 76.67%

I've just moved "My Documents" contents from one laptop to another
I now find that some of the Word Documents are showing the current date in letters to customers that were created & dated months ago
As these letters are records documents sent out is there a way of preventing them opening with the current date

I've already checked, Wotd - Insert - Date & Time & the Automatic Updates box is unchecked

Thanks for any help

Answer:In Word the Original Date in a letter changes to current date

Is this the file modified date or the file created date? Moving the file will give it a new "modified" date.

1 more replies
Relevance 76.67%

Hello. I have a cell on my spread sheet which calculates an earliest date due. The cell references other cells to achieve this and looks like this '=C51+(F8*7)' where cell C51 is a date field in the format DD/MM/CCYY and contains the date 26/12/2015. And cell F8 is a number field which contains the value '4'. This value '4' is indicating a number of weeks. So the calculation (F8*7) gets me the number of days and I then add those days to the date in cell C51 to get 23/01/2016.I want this cell to output the current date if the calculation above results in a date that is in the past. Can anyone tell me if there is a simple way to achieve this? Thank you.

Answer:Excel: Substitute a past date with the current date

If I understand you correctly, this should get you what you want:=IF("your calculation"< TODAY(),TODAY(),"your calculation")However, the TODAY() will update every day to always show the current date, assuming "your calculation" results in a date that is in the past.In other words, it will not lock in the first date it calculates. It will always display "today's" date.Is that what you want to see?One other issue: As explained at the link below, the TODAY() function is "volatile". That means that is will recalculate every time the sheet calculates, even if none of the arguments have changed. A large number of volatile functions can slow a worksheet down.https://msdn.microsoft.com/en-us/li...Volatile and Non-Volatile Functions

Excel supports the concept of a volatile function, that is,
one whose value cannot be assumed to be the same from one
moment to the next even if none of its arguments (if it
takes any) has changed. Excel reevaluates cells that contain
volatile functions, together with all dependents, every time
that it recalculates. For this reason, too much reliance on
volatile functions can make recalculation times slow. Use them
sparingly.

The following Excel functions are volatile:

NOW
TODAY
RAND
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 76.67%

Excel 2013 and Windows 10 Pro

Today is May-14

Date format is set to show: Mar-16
Pressing Ctrl+; shows: 05 14 2016 in the cell
and it converts to: May-16 after I exit that cell.

Any ideas as to how to fix this or why it adds 2 days to the date?

I don't do much on computer now because of health but when I do and there is a problem, I know some of the experts are here.

Thank you in advance, Ron
 

Answer:Excel Date shortcut posts proper date and then changes

Hey Ron,

This should be a fairly simple one. I'd hazard a guess and say the May-16 is the month and year rather than the month and day.
If you click the format drop down as shown below, and click the last option for more formatting options.

In the box that pops up, navigate to Custom using the left hand panel to navigate.
Here you can make your own (or use a preset if you wish) format to suit your needs.

Here is a quick key to help you get by:
dd = Day in Number format
ddd = Day in Short text
dddd = Day in Long text
mm = Month in Number format
mmm = Month in Short text
mmmm = Month in Long text
yy = year in 2 digits
yyyy = year in 4 digits

You can separate these with pretty much whatever you want.
All of this data is input into the "Type" field as shown in the picture above.

See the picture for an example, Ironically it is May 16th today so it will appear the same as your issue does, but rest assured, as long as you have those D's in there, it will display the day!

Best of luck,
Let us know how you get on,

Xsage
 

1 more replies
Relevance 76.67%

I have a file that lists a Julian date of, for instance, 37573. How do I convert that into something like 11/06/02?

Thanks in advance!
 

Answer:Julian Date--How to convert to calendar date in Excel

That's not a Julian date, Beth.

That is actually a date value that Excel provides.

That's how many days it's been since 1/1/1900.

To change it to a real date, you should be able to select that cell or entire column, hit Format-Cell, Number tab, and choose Date, and then any one of the date formats.

Julian dates are what numbered day of the year. For instance, January 3, 2002 would be 3.

Learn lots about dates and times in Excel:

http://www.cpearson.com/excel/datetime.htm

Be prepared to be there for a while.

Happy Thanksgiving!
 

2 more replies
Relevance 76.67%

Hi All,
I am using LUMIA 1320 that has Lumia Cyan.
Sometimes the time and date is getting reset to a specific date, i.e. 22nd May 2014
Due to this all reminder, alarms and new messages are not coming in sync.
Is this a virus? Does it have come from an app. If that is the case then it questions Microsoft App policies...
Anyone who has encountered this issue and has done something to eradicate it let me know.

Answer:Why is my phones time and date getting reset to a specific date?

First, windows phone can never get a virus. Its the safest platform after iOS . And no, a particular app can't affect the system's time and date. I think your should try to manually set the time and date in the settings. And see if it works.

3 more replies
Relevance 76.67%

Hello,

I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

Can anyone help, I've attached a small sample of the data.

Thanks

Daniel
 

Answer:Exported date field not recognized as date in Excel

Where does the data come from?
What do you get if you have no cell format?
 

3 more replies
Relevance 76.67%

I want to calculate how long a member of staff has worked for the company and therefore need to subtract their starting date from the current date.

Answer:How to subtract a date from a date ie years/months elapsed

If you use google to search:Time elapsed calculatorThere are a few online results to help. I hope they help. For the fun of it I calculated how long I have been in existence : 2,177.43 total weeks or: 15,242.00 total days or: 365,808.00 total hours or: 21,948,480.00 total minutes or: 1,316,908,800.00 total secondsThat was from the first result google gave me. Thank god no one will be able to figure out when I was born. message edited by btk1w1

9 more replies
Relevance 76.67%

Hi there

I know how to do add a criteria to a Query. For example in the QBE pane in design view in the criteria row I enter this statement: Between [Start Date] And [End Date]. When I open up the form that is bound to the query, a message box pops up and I enter the required dates and I get the required data that is between the dates that I specify. However, I'm now finding out that this method is now becoming a nuisance.

Is there a way that I can have 2 unbound controls - one for the start date and the other for the end date in a criteria form. This way I input the dates I want for a particular customer and the form with the required data shows up. For example, I have already developed a criteria form for customers. I have an unbound combo box control that has customer names and when I click on a particular customer name, all the data for that customer is displayed in a form. In the row source of the unbound combo box control there is a SELECT .... FROM ... statement. I also have a button next to the unbound combo box control that opens up the form for that particular customer. Now I want to add two more Unbound controls in the criteria form, one for Start Date and another for End Date.

This way I can select a customer from the customer combo Box, select the start start date from the start date combo box and the end date from the second combo box control and get the desired data for that particular customer between the specified dates. I have attempted to do this bu... Read more

Answer:Access - Start Date and End Date in a Criteria form

Mario: What you want is called a query by form (QBF). I don't know how to do them myself, offhand, but here's the MS Technet article:
http://support.microsoft.com/?kbid=235359
 

2 more replies
Relevance 76.67%

I am looking to set up a macro or formula that basically inserts the current time when a cell is filled SO:

when a2:a100 is populated with any thing then the corresponding C cell will be populated with the time.

AND

when e2:e1000 is populated wiith anything then the corresponding D Cell will be populated.

Currently I am using this macro.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A6:A100")) Is Nothing Then
With Target(1, 4)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

which works great for the A and C cells

but it does not work for the other one.. how do i add and modify this so all my needs are met.
I need another 2 sets of 2 macros accross the sheet for time entry.
 

Answer:Excel date auto populate date/time

11 more replies