Default Value=Now(), datediff not giving resutls 1

[JDRoss]

I have a datetimestamp field of type Date/Time in a table, which is updated with the default value, Now() from the system clock when a record is created. I want to show in query and report when a record was created or entered.

I have tried the following datediff statement to show entries that have been made in the last 90 days, but I get no results.

DateDiff("d",Date(),[table1]![DateTimeStamp)>=90

Can you help?

John

 

[BobStubbs]

Try:

Date()-[table1]![DateTimeStamp] >= 90

Subtracting two dates in VBA gives an integer result, which is the number of days between them.


Bob Stubbs

 

[BobJacksonNCI]

John,

This should fix you up:

[table1]![DateTimeStamp] >= (date() - 90) & " " & #00:00#

& " " & #00:00# ensures you get all entries that are exactly 90 days old by setting the time of day.



HTH,
Bob

 

[BobJacksonNCI]

John,

"upon further review..."

DateDiff(interval,date1,date2) calculates FROM date1 to date2. If date1 is the newer date, you get a negative number as a result - which will always fail the >= 90 comparison.

DateDiff("d",[table1]![DateTimeStamp],date())>=90

WILL work.

OR:

int(DateDiff("d",Date(),[table1]![DateTimeStamp))>=90

If you have entries exactly 90 days ago, I don't know whether the time portion might keep some records from being returned.



HTH,
Bob

 

[JDRoss]

Thanks Bob & Bob

None of those suggestions seem to work for me, though I now have a better understanding of what datediff is doing. The datediff function works well on other date fields I have, but not on the default value=Now() one.

I am wondering if I set the default=date() rather than Now(), will it make the difference in calculating?

John

 

[JDRoss]

>=(Date()-90) & " " & #00:00:00#

Thanks Bob, I tried this again and it works perfectly. Thank you so much.

Regards

John