Date and Time Issue

[djrudyn]

Good Day,
I am currently working on Access 2007, and importing an excel spread sheet (titled master) with the date and time in the "LastCoreUpdate" column (6/13/2011 6:12:02 AM)
I created a query to run:
SELECT master.UnitID, master.softwareversion, master.LastCoreUpdate
FROM master
GROUP BY master.UnitID, master.softwareversion, master.LastCoreUpdate
HAVING (((master.softwareversion)="5.37") AND ((master.LastCoreUpdate)>Now()-"2"));

How do I get access to ignore the time and only look at the date.
Because if I run the query later in the day it gives me a different value to earlier.

Please help

 

[James Dudden]

Try formatting the date/time column as just a date like this:
Format(LastCoreUpdate,"dd/mm/yyyy"))
Also, instead of using Now() in your query use Date()

 

[Galaxiom]

Firstly use Date() instead of Now() to just get the current date.

The Date component of Access is the number of days since 30 December 1899 while the Time is the decimal part.

Normally one does not subtract a string "2" from a date.
You can subtract a number 2 from a date and this will reduce it by two days.

There are a number ways to remove the time from a DateTime field and keep it as a date. Here is one that removes the time by geting the integer from the DateTime. CDate makes it back to a date datatype.

CDate(Int(fieldname())

However the safest way to deal with DateTime fields is using the DateDiff function. This example will give the difference in the day component of the date expression and today's date.

DateDiff("d", [date expression], Date())

There is also the DateAdd() function.

 

[Galaxiom]

Try formatting the date/time column as just a date like this:
Format(LastCoreUpdate,"dd/mm/yyyy"))

This turns the date into a string. Date functions cannot operate on a string. A string can be turned back into a Date with the CDate function but you must be aware that it uses the regional date format of the computer. It can be a dangerous step if the program is moved to a different region.

Jet/ACE SQL can use a string date but it must be presented as:
#mm/dd/yyyy#

 

[James Dudden]

This turns the date into a string. Date functions cannot operate on a string.

Is this true?
I use this method alot and have never had a problem!?

 

[Galaxiom]

Is this true?
I use this method alot and have never had a problem!?

Yes, strings do work with the date functions but only by first performing an implicit datatype conversion from the string back to a date so it is not really best practice. Letting Access "think" for itself, especially with dates, can sometimes lead to unpleasant surprises.

Of course it "should" be OK with both the Format and the subsequent date based function using the same regional settings but it is important to be aware of what is really going on.

I prefer to stick with expressions that use the DateAdd and DateDiff functions because they work directly with the DateTime datatype so are never subject to the vagaries of regional date formats.

 

[James Dudden]

Yes, strings do work with the date functions...

Thanks for the reply and explanation.