VB & SQL & Dates

[RosemaryJB]

I am having trouble with dates in SQL within VB.

I want to add a Call when a 'Making a Difference' record is created but not each time it is looked at or has minor updates so I am checking for existing records.

In a Query, the following works:
SELECT Calls.Notes, Calls.ContactID, Calls.CallDate
FROM Calls
WHERE (((Calls.Notes) Like "First MaDf Call*") AND ((Calls.ContactID)=[Forms]![Contacts]![ContactID]) AND ((Calls.CallDate)=#8/3/2010#))

I am matching on 03/08/2010 but I notice the SQL changes the date into US format - could this be significant?

This is my code:

To avoid the dreaded 3061 error, I have put the parameters in fields but still can't get a match on dates (the text & ID number matches work when I drop the date bit).

Dim Response, SQL As String
Dim rs As DAO.Recordset
Dim db As Database
Dim Txt As String
Dim ID, NoRec As Integer
Dim Dte As Date

Txt = """First MaDf Call:*"""
ID = [Forms]![Contacts]![ContactID]
Dte = [Forms]![Frm Making a Difference]![Date Added]
SQL = "SELECT * FROM Calls WHERE Calls.ContactID = " & ID & _
" And Calls.Notes Like " & Txt & _
" And Calls.CallDate = #" & Dte & "#"
'MsgBox SQL
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)
If rs.EOF = False Then
rs.MoveLast
NoRec = rs.RecordCount
End If
If NoRec > 0 Then
Txt = "ONE Call was added automatically for Making a Difference when this Request was first created. " & _
vbCrLf & "Any further Calls, if appropriate, must be added manually. "
Response = MsgBox(Txt, 0, "Making a Difference")
GoTo Clse
End If

'Add Call etc

 

[pbaldy]

Add this line right before the "Set rs..." line:

Debug.Print SQL

which will print the finished SQL out to the Immediate window in the VBA editor. That will let you examine it. If you don't spot the problem, post it here.

 

[RosemaryJB]

Thank-you for your help.
I had been using MsgBox SQL to display the query but thanks for letting me know about Debug. I came back to programming in my 60s for a charity having not programmed for decades so am self-taught in VB.
Query:
SELECT * FROM Calls WHERE Calls.ContactID = 68 And Calls.Notes Like "First MaDf Call:*" And Calls.CallDate = #03/08/2010#
I pasted it into SQL & it didn't work because Access transposed day & month.
I have now hard coded the date as MM/DD/YYYY (US style) and it seems to work so I now have to re-arrange the date (boohoo!) and we're away, I hope.

 

[namliam]

Yes access requires either ISO or US date formats....

 

[pbaldy]

More info and workarounds:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

http://www.mvps.org/access/datetime/date0005.htm

I've used the second in apps destined for the UK and other places where they use weird date formats.