Run-time error 3075 syntax error in date in query expression

Hi All,

I have the following code which it is in subform object event to check date overlapping:

If DCount("*", "Course Details", "InstructorID=" & Forms![Course Details]![Course Details Subform].Form.InstructorID & " AND Start<= #" & Me.End & "# AND End <=#" & Me.Start & "# and ID <>" & Me.id) > 0 Then

   MsgBox "Overlapping course for this instructor"

  Cancel = True

End If

 

When the field "Start" updated, I get the following error msg:

Run-time error 3075 syntax error in date in query expression 'InstructorID=1 AND Start<=## AND End <=#11/20/2011# and ID<>5'

 

 

any help please?

 
Question Info

Last updated July 19, 2018 Views 3,513 Applies to:
Answer
The following article includes guidance on the use of the Hijri calendar in Access:

http://office.microsoft.com/en-us/access-help/about-using-different-calendars-HP005257922.aspx

Try using the CDate function rather than a date literal:

     strCriteria = _
         "InstructorID = " & Me.InstructorID & _
         " And Start <= " & CDate(Me.txtEnd) & _
         " And End >= " & CDate(Me.txtStart) & _
         " And ID <> " & Me.id

As you are concatenating the value into a string expression, you may need to convert the return value of the CDate function to a number, however:

     strCriteria = _
         "InstructorID = " & Me.InstructorID & _
         " And Start <= " & Clng(CDate(Me.txtEnd)) & _
         " And End >= " & Clng(CDate(Me.txtStart)) & _
         " And ID <>" & Me.id

The date/time data type is implemented as 64 bit floating point number staring at 30 December 1899 00:00:00, with the integer part representing the days and the fractional part the time of day.  As you are concerned solely with dates here using the CLng function to return a long integer number should give you a value which is directly comparable with the underlying numeric values in the Start and End columns.

Another possibility would be to use the following function of mine:

Public Function WithinDateRange(dtmParamRangeStart As Date, _
                       dtmParamRangeEnd As Date, _
                       dtmDataRangeStart As Date, _
                       dtmDataRangeEnd As Date) As Boolean
                       
' Accepts: date at which parameter range starts
'          date at which parameter range ends
'          date at which data range starts
'          date at which data range ends
' Returns: True if data range intersects with parameter range
'          False if whole of data range outside parameter range
 
   WithinDateRange = _
       dtmDataRangeStart <= dtmParamRangeEnd And _
       dtmDataRangeEnd >= dtmParamRangeStart
       
End Function

This just wraps up the same logic as in your expression in a function, so you'd call it like this:

     strCriteria = _
         "InstructorID = " & Me.InstructorID & _
         " And WithinDateRange(" & CDate(Me.txtStart) & _
         "," & CDate(Me.txtEnd) &",[Start],[End])" & _
         " And ID <>" & Me.id

As the function's arguments are all declared as date data type it should correctly interpret the values passed into it.

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Assuming you are still using the form's BeforeUpdate even procedure the following lines:

    Me.Undo

after the 'Cancel = True' line.

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.