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

  • 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

    Wednesday, November 8, 2017 10:24 AM

Answers

  • 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.

    \

    Wednesday, November 8, 2017 11:36 AM
  • 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)

    Wednesday, November 8, 2017 11:49 AM
  • 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.
    • Edited by Imb-hb Saturday, November 11, 2017 3:21 PM
    • Marked as answer by ForssPeterNova Sunday, November 12, 2017 6:27 AM
    Saturday, November 11, 2017 3:20 PM

All replies

  • Following...
    Wednesday, November 8, 2017 10:26 AM
  • 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.

    \

    Wednesday, November 8, 2017 11:36 AM
  • 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)

    Wednesday, November 8, 2017 11:49 AM
  • 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

    Wednesday, November 8, 2017 3:07 PM
  • 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 MSDNFSF@microsoft.com.

    Friday, November 10, 2017 5:58 AM
  • 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

    Friday, November 10, 2017 7:48 AM
  • 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

    Saturday, November 11, 2017 9:17 AM
  • 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.
    • Edited by Imb-hb Saturday, November 11, 2017 3:21 PM
    • Marked as answer by ForssPeterNova Sunday, November 12, 2017 6:27 AM
    Saturday, November 11, 2017 3:20 PM
  • Thanks Imb

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


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

    Sunday, November 12, 2017 6:29 AM
  • 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.

    Monday, November 13, 2017 3:34 PM
  • Once again, thanks a lot Imb!

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

    Tuesday, November 14, 2017 12:25 PM