Problems with New Year and Greater than or Equal Criteria in Query

[athyeh]

Hi Folks,

Hoping that this will be a simple fix, I have this criteria embedded in the Date Field of a query

>=Format([Forms]![Reporting]![FromAR],"mm/dd/yyyy") And <=Format([Forms]![Reporting]![ToAR],"mm/dd/yyyy")

It was working fine until the new year occured, and now when I try to pull a date range between that includes 12/31/2013-1/1/2014, the query fails to draw any results.



I'm assuming because the query doesn't register that 1/1/2014 is "Greater than" 12/31/2013? Can anybody help me troubleshoot?

Thanks!

[Bob Fitz]

Just a guess, but have you tried:
>=CDate(Format([Forms]![Reporting]![FromAR],"mm/dd/yyyy")) And <=CDate(Format([Forms]![Reporting]![ToAR],"mm/dd/yyyy"))

[athyeh]

Just a guess, but have you tried:
>=CDate(Format([Forms]![Reporting]![FromAR],"mm/dd/yyyy")) And <=CDate(Format([Forms]![Reporting]![ToAR],"mm/dd/yyyy"))

Just did, came back with "This expression is typed incorrectly...." error.

[Bob Fitz]

What is the Data Type of the field.
Also, second guess:
Between CDate(Format([Forms]![Reporting]![FromAR],"mm/dd/yyyy")) And CDate(Format([Forms]![Reporting]![ToAR],"mm/dd/yyyy"))

[June7]

Why do you need to use format function?

Format function results in a string value. The CDate function should convert back to a true date value but since it is still not working, I suspect something is wrong with the values input to textboxes and/or the dates stored in table.

[rpeare]

the format is changing your date to a string so you're trying to find something between two text values which is going to give you inconsistent results.

have you tried:

between [Forms]![Reporting]![FromAR] And [Forms]![Reporting]![ToAR]

as your criteria instead.