locked
date with time as VBA recordset criteria RRS feed

  • Question

  • Hello!

    I keep ketting a syntax error in date message, what should be different? I do need time in the criteria. do I need to format the date in some specific way?

    error message:

    Syntax error in date in query expression:

    ’(((Month([recordset1].[date]))=10) and ((year([recordset1].[date]))=2014) and ((Recordset2.dk)=1)) or

    (((Month([recordset1].[date]))<10) and ((year([recordset1].[date]))<2014) and ((Recordset2.dk)=1) and ((recordset1.insertdate)>#17.11.2014 14:00:00 #)’.

    syntax:

    WHERE

     (

     ((Month([recordset1].[rs1date])) = " & Forms!MonthYear![xkuu] & ")

     And

    ((Year([recordset1].[rs1date])) = " & Forms!MonthYear![xaasta] & ")

     And

    ((Recordset2.dk) = 1)

    )

    Or

    (

    ((Month([recordset1].[rs1date])) < " & Forms!MonthYear![crmonth] & ")

    And

     ((Year([recordset1].[rs1date])) < " & Forms!MonthYear![cryear] & ")

     And

     ((Recordset2.dk) = 1)

    And

    ((recordset1.insdate) > #" & [Forms]![another form]![dekdate] & "#)

    )

    Thank you in advance,

    Wednesday, December 10, 2014 11:21 AM

Answers

  • >>
    ((recordset1.insertdate)>#17.11.2014 14:00:00 #)

    and

    ((recordset1.insdate) > #" & [Forms]![another form]![dekdate] & "#)

    <<

    Literal DateTime value MUST be in the US format "mm/dd/yyyy HH:nn:ss" or the ISO format "yyyy-mm-dd HH:nn:ss" (my preferred method since there is no confusion) and enclosed in hashes(#) regardless of your Date/Time Regional Settings.  Also there should not be any space character before the second/ending hash character.

    It looks like you are constructing the dynamic SQL in code and in this case, it is much harder since you have to watch out for matching double-quotes.  For example, I don't see a matching double-quote for the last double quote in the second quoteed expression???

    If you need to construct SQL in code, you should use a String Variable strSQL to construct the String and use the Immediate/Debug window to see the value strSQL after the construction to see the result to check for syntax errors (the database engine only processes this result, not the construction code).  when you want to ask the forums, please post the construction code as well as the result of the construction.

    It also looks to me that you use [Recordset1] and [Recordset2] as your Table names or Query names??? "Recordset" is the name of an important object used in code so using "Recordset" as part of your Table name or Query can get very confusing for potential respondents.

          


    Van Dinh

    • Marked as answer by helioflor Thursday, December 11, 2014 9:21 PM
    Wednesday, December 10, 2014 9:29 PM
  • I think you're getting hung up with all the parentheses.  Your resulting SQL where clause should (could) look something like this:

    WHERE (Month([recordset1].[date])=10 and year([recordset1].[date])=2014 and Recordset2.dk=1) or
    (Month([recordset1].[date])<10 and year([recordset1].[date])<2014 and Recordset2.dk=1 and recordset1.insertdate>#17.11.2014 14:00:00 #)

    • Marked as answer by helioflor Thursday, December 11, 2014 9:21 PM
    Wednesday, December 10, 2014 9:39 PM

All replies

  • >>
    ((recordset1.insertdate)>#17.11.2014 14:00:00 #)

    and

    ((recordset1.insdate) > #" & [Forms]![another form]![dekdate] & "#)

    <<

    Literal DateTime value MUST be in the US format "mm/dd/yyyy HH:nn:ss" or the ISO format "yyyy-mm-dd HH:nn:ss" (my preferred method since there is no confusion) and enclosed in hashes(#) regardless of your Date/Time Regional Settings.  Also there should not be any space character before the second/ending hash character.

    It looks like you are constructing the dynamic SQL in code and in this case, it is much harder since you have to watch out for matching double-quotes.  For example, I don't see a matching double-quote for the last double quote in the second quoteed expression???

    If you need to construct SQL in code, you should use a String Variable strSQL to construct the String and use the Immediate/Debug window to see the value strSQL after the construction to see the result to check for syntax errors (the database engine only processes this result, not the construction code).  when you want to ask the forums, please post the construction code as well as the result of the construction.

    It also looks to me that you use [Recordset1] and [Recordset2] as your Table names or Query names??? "Recordset" is the name of an important object used in code so using "Recordset" as part of your Table name or Query can get very confusing for potential respondents.

          


    Van Dinh

    • Marked as answer by helioflor Thursday, December 11, 2014 9:21 PM
    Wednesday, December 10, 2014 9:29 PM
  • I think you're getting hung up with all the parentheses.  Your resulting SQL where clause should (could) look something like this:

    WHERE (Month([recordset1].[date])=10 and year([recordset1].[date])=2014 and Recordset2.dk=1) or
    (Month([recordset1].[date])<10 and year([recordset1].[date])<2014 and Recordset2.dk=1 and recordset1.insertdate>#17.11.2014 14:00:00 #)

    • Marked as answer by helioflor Thursday, December 11, 2014 9:21 PM
    Wednesday, December 10, 2014 9:39 PM
  • Dear Van Dinh and Bruce, thanks for the advice. turns out both problems were there: excessive parenthesis and also dotted date. thank you so much !

    Thursday, December 11, 2014 9:22 PM