MSACCESS 2010 VBA; Syntax Error in date in query expression

0
votes

I am creating a simple database for tracking working hours. The idea is that:

  1. Every day you need to input only the days that employee did not work (startDate, endDate, absenceType),
  2. Then calculate working days for the whole month or selected period.

Calculation of working days should take into account weekends (saturday, sunday) and holidays from Holiday table.

I took a function sample from MSDN (Counting the Number of Working Days in Access 2007) and put it into a module in my MS Access 2010 db but each time I run a query I have this error.

Typically the same error appears attempting to run a query in another sample database from somewhere.

The problem is in the strWhere clause: 

strWhere = "[Holiday] >=#" & startDate & "# AND [Holiday] <=#" & endDate & "#"

' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", Domain:=strHolidays, Criteria:=strWhere)
Workdays = nWeekdays - nHolidays

The error msg from both databases is available in the link below

Runtime Error 3075 Syntax error in date in query expression

enter image description here

Any help is appreciated.

3
vbams-access
Where are you based? If you do not share a date format with the US then that is most likely the cause of the problem - ste-fu
Please show the values and types of startDate and endDate. - Parfait
EU/Croatia. My local date format is dd.mm.yyyy and it works in all of my previous Access databases. In this one I used a code as mentioned, but in it there is no other format specified. All inputs in tables are in local format. - Vedran
MS Access like all other Office applications align to your local CPU Regional Language settings which includes currency, date/time formats, and other items. Day first should not need to be re-formatted. - Parfait

3 Answers

2
votes

You must force a format on the string expressions for your dates. Get used to use the ISO sequence yyyy-mm-dd as it works everywhere:

strHolidays = "NameOfYourTable"
strWhere = "[Holiday] >= #" & Format(startDate, "yyyy\/mm\/dd") & "# AND [Holiday] <= #" & Format(endDate, "yyyy\/mm\/dd") & "#"

' Count the number of holidays.
nHolidays = DCount("*", strHolidays, strWhere)
0
votes

In the past I have had issues where VBA doesn't always respect the regional date settings. Try forcing it into US format before concatenating it

strWhere = "[Holiday] >=#" & Format(startDate, "MM/dd/yyyy") & "# AND [Holiday] <=#" & Format(endDate, "MM/dd/yyyy") & "#"
0
votes

  1. Make sure the date is in MM/DD/YYYY order in VBA. Always. I generally use:

    strWhere = "[Holiday] >= " & Format(startDate,"\#mm\/dd\/yyyy\#")

  2. the second argument of DCount is strHolidays. That does not look like the name of a table/query. This argument should be the name of a table/query.