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.

 

[Pat Hartman]

DateRange: CDate([Month] & "/" & 1 & "/" & [Year])

If that doesn't work, the problem is likely related to your choice of column names. It is poor practice (and leads to various problems with SQL and VBA) to use function names as column names. Both SQL and VBA have already taken Month and Year as function names.

 

[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.

 

[Pat Hartman]

You may not have named them, but if you're using them, you're the one who's going to have the problem.