0

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.

Improve this question
4
  • 1
    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 Commented Nov 14, 2017 at 14:37
  • Please show the values and types of startDate and endDate. Commented Nov 14, 2017 at 15:00
  • 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. Commented Nov 15, 2017 at 9:01
  • 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. Commented Nov 16, 2017 at 19:16

3 Answers 3

Reset to default
2

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This worked, but I realy need explanation why. Since all my previous databases worked fine. A link for the explanation is just fine. Thanks everyone.
If the local date separator is not a dot, it will most likely work - except for dates where day is <= 12 and, thus, will be taken for the month if using the format dd-mm-yyyy.
So what I present as date value is one thing, and calculation is another.
Yes, the user interface is localised - so most places outside the US, date/time, currency, etc. will look different.
0

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

2 Comments

tried but no change. In my code with no format specified it says "Syntax error in querry expression '[Holiday] >=#01.02.2017# AND [Holiday] <=#07.02.2017', and in your expample it says '[Holiday] >=#02.01.2017# AND [Holiday] <=#02.07.2017'. So my guess is that it must be or a difference Access 2010 handles code from 2007 example, or some reference is missing, but I can't see logic in it.
Your problem is that you don't escape the / which then becomes the date separator which is localised. Your locale uses a dot which scrambles the SQL. See my answer.
0

  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.

Improve this answer

2 Comments

Answers: 1. all my previous access db's have local date/time format as dd/mm/yyyy and they work. Only this one doesn't. Since in db date format is not specified different then my local dd/mm/yyyy, I don't see this can be the issue? 2. When I mark strholidays with a mouse, a popup apperas saying "strHolidays = 'Holidays' ", which is the correct name of my table.
@Vedran in my experience, all date criteria IN VBA must be in US mode. Try with an ambiguous date, like #6/5/2017#. It will be understood as June 5.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.