Access query using datediff

Question

I have a query expression field like this..

Days: CInt (Date()-CDate ([somedates]))

The data in the Days field looks like this..

2

11

15

I want to use this to query records but I get a type mismatch anytime I try to add any criteria.

< 7

< "7"

Like "2"

Please expand on this. What does the criteria represent? A range of days? A maximum date? Where do you enter this criteria? What does the SQL look like both before the criteria is added and after? — Johnny Bones
What's the format of 'somedate'? If you are supplying a date there like #1/1/2016# then the error is caused by the brackets around the value. — Wayne G. Dunn
The criteria is the number of days that has passed. So I enter this as > [Number of Days] in the exception. Date () is used to get the days since the data in the [somedate field] which is a date like 01/01/2016 12:15:45 — Road King

Johnny Bones Johnny Bones · Accepted Answer · 2016-01-15T20:40:30

I'm not 100% sure what you mean, but I just created this query loosely based on what you've provided. Note that the database contains a table called tblHoliday which contains a field called HolidayDate.

SELECT tblHoliday.HolidayDate, CInt([HolidayDate]-CDate(#5/5/2012#)) AS Days
FROM tblHoliday
WHERE (((CInt([HolidayDate]-CDate(#5/5/2012#)))>5));

I put in the Criteria box just below the Days field, ">5" (without the quotes). The above code is what the SQL equivalent is.

Try pasting this into the SQL window, substituting your date field and table name, and see if it works. If so, look at the design view of the query and change what you want to change.

Access query using datediff

1 Answers