Date Criteria Issue

[CDBPlattco]

Evening All,

I know this is an issue that has been run down to death, but I can't seem to get the syntax right and I need some help.

I have a report within a database that I need to filter so it only shows the overdue items. in the past I had used the following:

DoCmd.OpenReport "rpt_CAR_Open_Dept", acViewReport, , "[DueDate]<=" & Format(Date, "\#mm/dd/yyyy\#"), acWindowNormal

Our European office requested that I change the date format on "DueDate" to the "Medium Date" format so that it reads "dd-mmm-yy". However, the change in format of the data now causes problems on the call out of the "[DueDate]" section of the report.

How do I update the OpenReport line to make sure I'm comparing apples to apples in the critiera?

 

[pbaldy]

Since you're using the Date function I think you can just do:

"[DueDate]<=Date()"

 

[CDBPlattco]

Alas, this is the problem I started with when I first built the database. Having spent some time on this forum I came up with the syntax that I used in the original post. However, this only works when the [DueDate] part is in US date format. When it was shifted to European format it became a problem and no matter how I re-write it, it doesn't want to work.

 

[pbaldy]

I wouldn't expect you to need to worry about format unless you're concatenating a value into the string. Did you try what I posted? I guess you can try Allen's method if mine doesn't work.

http://allenbrowne.com/ser-36.html

 

[arnelgp]

if the [DueDate] field is of type Date/Time, then your code will work no matter what Format you put on the table's Property.