Build a Date in a query

[mikemaki]

How can I build a date in a query that is used for filtering purposes? The data is in the records in two fields - Month and Year. In our query we have an expression that builds a date - DateRange: [Month]/1/[Year] in order to qualify the record with the criteria Between FromDate and ThruDate. DateRange as it is shown here returns a date of 12/30/1899 for all records. DateRange: #[Month]/1/[Year]# is invalid syntax. DateRange: [Month] & "/" & 1 & "/" & [Year] is not recognized as a date.

 

[raskew]

Hi -

Lookup the DateSerial() function. Here's an example from the debug window of how you might use it:

mymonth = 10
myyear = 2005
myyear = dateserial(myyear, mymonth, 1)
? myyear
10/1/05

HTH - Bob

 

[mikemaki]

Thanks, Pat, that worked. And shhhh, I'm not the one that named the fields.