Converting an Update Query to VBA

[robsmith]

Hi,



I am trying to build an update query into some VBA code. I want to delete all test after the comma but I keep getting a syntax error.

Can anyone see the problem?

CurrentDb.Execute "UPDATE Matches SET Matches.Toss = Left([Matches.Toss], InStr([Matches.Toss], ", ") - 1) WHERE Matches.Toss Like "*, *"", dbFailOnError

Thanks

[Minty]

Put you sql into a string an debug.print it - the error will be obvious;

Dim strSQL as String

strSQL = "UPDATE Matches SET Matches.Toss = Left([Matches.Toss], InStr([Matches.Toss], ", ") - 1) WHERE Matches.Toss Like "*, *""

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

Press ctrl+g to show the immediate window in the VBA editor it will display your string.

I suspect it should be something like

strSQL = "UPDATE Matches SET Matches.Toss = Left([Matches.Toss], InStr([Matches.Toss], ', ') - 1) WHERE Matches.Toss Like '*, *'"

[robsmith]

Thanks Minty