CODEWITHSUNDEEP
vbams-access
Vedran
Vedran

Reputation: 41

MSACCESS 2010 VBA; Syntax Error in date in query expression

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.

Upvotes: 0

Views: 2329

Answers (3)

Gustav
Gustav

Reputation: 55806

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)

Upvotes: 2

iDevlop
iDevlop

Reputation: 25252

  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.

Upvotes: 0

ste-fu
ste-fu

Reputation: 7434

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") & "#"

Upvotes: 0

Related Questions