NEED SQL HELP

[rjohnstone1]

I have a button on my form to open a report
with some criteria (combo box, dates)
I am trying to build a sql statement so the
report will be filtered between two dates
and having criteria of my combo box. I know the dates work but the Cost Centre Name does not. Here is what i have. Any help would be appreciated.
Thanks Ryan J

DoCmd.OpenReport Report_Name, Report_Type, , "[WoDate] Between #" & Format(Me.StartDate, "mm\/dd\/yyyy") & "# And #" & Format(Me.EndDate, "mm\/dd\/yyyy") & "# And [WoCostCentreName] = [FrmWhatDates].forms![CostCenterCombo]"

 

[Pat Hartman]

I'm confused by why you have both forward and backward slashes in the format statement but that's beside the point. The point is - you are forcing Access to do an alphanumeric compare rather than a numeric compare. Alpha compares work character by character from left to right. That means that 01/15/2000 would fall between 01/01/1999 and 01/31/1999. Do the compare in your head as if you didn't "know" you were looking at a date to see what I mean. 01/15/2000 is greater than 01/01/1999 and less than 01/31/1999 because 31 is greater than 15.

There are two solutions to the problem.
1. Change the format statements to yyyy/mm/dd so that the fields will be compared in the correct order.
2. Don't use format statements at all. That way Access will do a numeric compare (since it internally stores dates as a serial number) and the problem will not occur.