"Date" in VBA Translates to "2010-06-22" Instead of Today's Date

[VariableZ]

Windows7 64bit


Access 2010 32bit

I have a very odd problem. When trying to open a report that worked wonderfully last week, there are suddenly no results. I discovered the reason was that somehow DATE is getting translated as 2010-06-22 instead of 2012-03-13, which is today's date. I tried "=Date()" in the control source of a control and that worked properly. I changed the date on my computer, and the report still translates Date as 2010-06-22. The date on the server that the database is held on is correct. I've moved the database to my local machine and the problem persists. The code is below.

DoCmd.OpenReport "rptLotData", acViewReport, , "([qryLotDataConglomerateWithTotals].[FormattedDate] Between #" & Date & "# And #" & DateAdd("m", -1, Date) & "#) And ([FirstOfModel] Like ""*" & [VINCodeReportSortByUnitField] & """" & " Or [FirstOfModel] Like """ & [VINCodeReportSortByUnitField] & " *"")"

I've gotten around it by making a control named TodayField with control source "=Date()" and changing Date to [TodayField] in the VBA, but that doesn't seem like it should be a necessity. Any ideas?

[pbaldy]

My guess is that you have a field in the table named "Date" and Access is confused as to which you want. If so, you're seeing why we don't recommend using Date as a field name. This would be a workaround:

DateAdd("m", -1, VBA.Date)

[alansidman]

You might want to keep this link handy. It has a listing of "Reserved Words." Avoid using these.

http://office.microsoft.com/en-us/ac...010030643.aspx

[VariableZ]

Sadly, I do have a field named 'Date', but I can't do anything about that since I didn't design the SQL database I have to pull my data from . I don't understand why it worked until this week, but it looks like suddenly Access decided that the field took precedence over the reserved name. Maybe changing to daylight savings time made it grouchy. It always makes me grouchy. Anyway, thanks for the VBA.Date. That worked.

- Zohar S.E.

[pbaldy]

Happy to help.