Solved Syntax error in date in query expression

[DeWaal]

Hi Experts

I really need some help please i am still pretty new to Access VBA.

I am trying to create a pop up reminder in my form called "FrmCustomerInfo" that has a Subform called "FrmCalloutDetailsSubform"

I am using the On Timer event with VBA and a Timer Interval of 5000

But i keep getting a "Syntax error in date in query expression"

Any assistance here will be much appreciated.

Please also let me know if any other info is required.

This is my code:
Private Sub Form_Timer()
Dim ID As Long
ID = Nz(DLookup("CustomerID", "TblCalloutDetails", "MajorServiceDate=" & Date & "#"), 0)
If ID <> 0 Then
DoCmd.OpenForm "FrmNotice"
End If

End Sub

 

[GPGeorge]

Date delimiters are used in pairs, one before the date variable and one after the date variable.

 

[DeWaal]

Hi George

Thanks a mill for your reply.

ill be honest i have no idea what you mean.

Is there something i need to change on the code?

 

[DeWaal]

So this what I'm getting

 

[DeWaal]

Got this solved.

My code looks like this now.

Private Sub Form_Timer()
Dim ID As Long
ID = Nz(DLookup("CalloutID", "TblCalloutDetails", "MajorServiceDate<=#" & Date & "#"), 0)
If ID <> 0 Then
DoCmd.OpenForm "FrmNotice"
End If

End Sub

 

[Gasman]

Use format to get it in mm/dd/yyyy or yyyy-mm-dd format.
I used to use this is some of my dbs as I am in the UK.

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

 

[GPGeorge]

Got this solved.

My code looks like this now.

You figured out what I meant.

 

[DeWaal]

Use format to get it in mm/dd/yyyy or yyyy-mm-dd format.
I used to use this is some of my dbs as I am in the UK.

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

Thank you for this Gasman

 

[DeWaal]

You figured out what I meant.

Hi George

Yes, thank you.

I'm getting the hang of things a bit around VBA and my access database.