Is there a way to do a Date Difference with a Date Serial?

[anoble1]

I have a table that I am using the run a query. The date format is yyyymmdd. I can't get the DateAdd function to work. I am doing a

=DateAdd("d",-100,Now())

and comes back blank with records from 3 years ago till today.
My question - Is there a better way to do this? If you have a date that is in yyyymmdd how do you tell the query you want to only see records that are older than 100 days from today?

[isladogs]

One method is to use a filter in your query:

WHERE [YourDateField]<Date()-100

[NeoPa]

If you want to do the same thing, but without relying on how dates are held and manipulated in memory, then the following also works :

WHERE ([YourDateField]<DateAdd('d',-100,Date()))

Remember, calculating the value that the dates need to be compared with is always better than calculating the difference based on the field value as the former calculation need only be done once whereas the latter need be done for each record. This can have further reach than simply slowing down each record.

It also helps to understand the difference between the Date() & Now() functions and which values they return. Once understood they can be used appropriately - as illustrated by IslaDogs' example.

PS. Don't get me wrong. It is 100% reliable to work with days as integer values when using dates. This isn't going to change. However, for purists - and the potential for porting the code to other less defined systems - using DateAdd() is advisable.