Syntax error in query expression ‘[datum för brygg]<=#21.11.2017’

Question

Hi

Getting message ” Syntax error in query expression ‘[datum för brygg]<=#21.11.2017’

I get the error when I run my app on an English version of Ms Access 2016 32 bit on remote desktop with Windows Server 2012 R2 64 bit (English) On the remote desktop I have picked Germany as Format.

But when I run the same app on Swedish Access 2016 32 bit and Swedish Windows 10 64 bit. There is no error.

So I guess there is something with the date format. But I can’t understand what. I have tried to edit the Query to “German standard” but the error is still the same:

This is how in looks in the Swedish Access version. First a copy-paste from the Query Editor:

Löpande Sum: DSumma("[kg behov]";"[prodschema var skall malten användas]";"[datum för brygg]<=#" & Format([Datum för brygg];"åååå-mm-dd") & "#")

Then the same from the SQL editor:

DSum("[kg behov]","[prodschema var skall malten användas]","[datum för brygg]<=#" & Format([Datum för brygg],"yyyy/mm/dd") & "#") AS [Löpande Sum]

Swedish Settings in Windows:

I changed the ”German” app into this:

Löpande Sum: DSum("[kg behov]";"[prodschema var skall humlen användas]";"[datum för brygg]<=#" & Format([Datum för brygg];"dd.mm.yyyy") & "#")

Then the same from the SQL editor: DSum("[kg behov]","[prodschema var skall humlen användas]","[datum för brygg]<=#" & Format([Datum för brygg],"dd/mm/yyyy") & "#") AS [Löpande Sum]

But I get the same error:

Any ideas?

---

Best // Peter Forss Stockholm GMT &#43;1.00

Answer 1

Following...

Answer 2

Then the same from the SQL editor: DSum("[kg behov]","[prodschema var skall humlen användas]","[datum för brygg]<=#" & Format([Datum för brygg],"dd/mm/yyyy") & "#") AS [Löpande Sum]

Hi Peter,

You could try:

DSum("[kg behov]","[prodschema var skall humlen användas]","[datum för brygg]<=#" & Format([Datum för brygg],'dd/mm/yyyy') & "#") AS [Löpande Sum]

The scope of the quotes of the formatstrring is different from the scope of the parameterstrings. You can use single quotes to distinguish between the two.

Imb.

\

Answer 3

Regardless of the system language, you should use either US date format mm/dd/yyyy or ISO date format yyyy-mm-dd, but NOT dd/mm/yyyy or variants thereof. So in the SQL editor:

DSum("[kg behov]","[prodschema var skall humlen användas]","[datum för brygg]<=#" & Format([Datum för brygg],"mm/dd/yyyy") & "#") AS [Löpande Sum]

or

DSum("[kg behov]","[prodschema var skall humlen användas]","[datum för brygg]<=#" & Format([Datum för brygg],"yyyy-mm-dd") & "#") AS [Löpande Sum]

This should work whether the computer is set to Swedish, German, or any other locale.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 4

Thanks Imb and thanks Hans

Am busy today. Will apply your suggestions as soon as I can.

---

Best // Peter Forss Stockholm GMT &#43;1.00

Answer 5

Hi ForssPeterNove,

Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

Best Regards,

Terry

---

MSDN Community Support Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

Hi Terry

I am on a business trip. No time for trying what Imb and Hans proposed. But I will in a few days. Then I will Mark as answer.

---

Best // Peter Forss Stockholm GMT &#43;1.00

Answer 7

Hi

I would say Hans gave me the right alternatives. The ISO and the US.

I have tried it back and forth. On my laptop with Swedish settings and Swedish MS Access and on a terminal server with English MS Access and German settings.

Thank you all for helping!

---

Best // Peter Forss Stockholm GMT &#43;1.00

Answer 8

Löpande Sum: DSum("[kg behov]";"[prodschema var skall humlen användas]";"[datum för brygg]<=#" & Format([Datum för brygg];"dd.mm.yyyy") & "#")

Hi Peter,

I always use a simple function to do the date setting, like:

Löpande Sum: DSum("[kg behov]";"[prodschema var skall humlen användas]";"[datum för brygg]<= " & As_date([Datum för brygg]))

The function "As_date" converts the date to ISO-format, and surrounds with #'s: code is now easy to write and to read.

Function As_date(cur_datum As Variant) As String
  ' ISO-format:  yyyy-mm-dd
  If (IsNull(cur_datum)) Then
    As_date = "Null"
  Else
    As_date = "#" & Format(cur_datum, "yyyy-mm-dd") & "#"
  End If
End Function

Imb.

Answer 9

Thanks Imb

What a great "little" function. Thank you very much for sharing.

---

Best // Peter Forss Stockholm GMT &#43;1.00

Answer 10

What a great "little" function. Thank you very much for sharing.

Hi Peter,

The same I do for text-manipulation:

   sql_string =  "SELECT * FROM Person_tbl WHERE Lastname = " & As_text("Forss")

Function As_text(cur_text As Variant, Optional on_null As String) As String
  If (IsNull(cur_text)) Then
    As_text = "Null"
  Else
    As_text = "'" & Replace(cur_text, "'", "''") & "'"
  End If
End Function

This function also handles names that contain single quotes.

Imb.

Answer 11

Once again, thanks a lot Imb!

---

Best // Peter Forss Stockholm GMT &#43;1.00