INSERT INTO Query Problem 1

[rookery]

Anyone figure whats wrong with the following code:

Set dbs = CurrentDb
Set MyRs = dbs.OpenRecordset("tblMailMerge", dbOpenDynaset)
Do Until MyRs.EOF
With MyRs
.MoveFirst
intCust = ![CustID]
dbs.Execute "INSERT INTO ClientNotes (CustID, Date, NoteText) VALUES (10, 26/07/2002, 'MailMerge Letter Sent!'"
.MoveNext
End With
Loop

I'm getting a "Syntax Error in INSERT INTO query" error, yet the table and field names are all correct.
Ideas?

 

[AlexMidd]

Is your post an exact copy of the SQL? If so, there is a ) missing from the end of the query. Have fun! )

Alex Middleton

 

[sdimov]

Also #26/07/2002# maybe?

Svet

 

[rookery]

Thanks for your replies people. Alex, my mistake, it wasn't an exact post - I must have missed the last bracket off during the COPY. I tried your suggestion Svet, but no joy.
If at first you dont succeed....

 

[flaniganmj]

Is CustID a text field?

flan

 

[rookery]

Yes Flan it is. Should it read:

VALUES (" & 10 & ", 26/07/2002, 'MailMerge Letter Sent!'&quot

 

[rookery]

All this sun must be going to my head! My apologies - I've just double checked and it is an AutoNumber field.

 

[rookery]

Blimey I really have got sunstroke! Believe me this time when I say it is a Number field.

 

[beniez]

Hi Rookery

You can't have a field called Date. At least, you can, but it won't work in SQL. There's a reason for this. I've just forgotten it.

Changing your fieldname, and your code works fine.

HTH

Ben

 

[rookery]

You're a diamond Ben. Thanks alot. Just out of interest though, my code seems to get stuck in a never-ending loop. I've sorted out a workaround but would be interested to know why this is happening for future ref.

Cheers again.