vba code vs sql

[kkshansid]

Debug.Print ("update TABLE set REM=REM & ' DB'  WHERE  ISDATE(Left(DOB,2) & " / " & Mid(DOB,3,2) & " / " & Mid(DOB,5,4)) = 0")
CurrentDb.Execute ("update TABLE set REM=REM & ' DB'  WHERE  ISDATE(Left(DOB,2) & " / " & Mid(DOB,3,2) & " / " & Mid(DOB,5,4)) = 0")
 

same query running very successfully in sql mode while giving type mismatch error in vba code in fact debug.print is also giving type mismatch error

update TABLE set REM=REM & ' DB'  WHERE  ISDATE(Left(DOB,2) & " / " & Mid(DOB,3,2) & " / " & Mid(DOB,5,4)) = 0

this query is running successfully
Kindly help

[MikeTheBike]

Hi Try this

Debug.Print "update TABLE set REM=REM & ' DB'  WHERE  ISDATE(Left(DOB,2) & "" / "" & Mid(DOB,3,2) & "" / "" & Mid(DOB,5,4)) = 0"

You need to double up the quote marks when constructing a string containing/include quotes.


HTH

MTB

[NeoPa]

The more correct approach (when dealing with SQL strings specifically) is to use the correct SQL quotes as you have done in the first string (' DB').

Thus, it should be :

Dim strSQL As String
 
strSQL = "UPDATE [Table] " _
       & "SET    [REM]=[REM] & ' DB' " _
       & "WHERE  Not IsDate(Left([DOB],2)+'/'+Mid([DOB],3,2)+'/'+Mid([DOB],5,4))"
Debug.Print strSQL
Call CurrentDb().Execute(strSQL)

NB. Only use + for joining strings when none of the values is Null or when you want Null propagation. I use it here to keep the code shorter. "&" works perfectly well.

[NeoPa]

To make things perfectly clear - MtB's approach will work, as Jet SQL will happily work with double-quotes in place of the correct SQL quotes (').

The approach indicated will also work in various other scenarios where quotes of either type need to be entered into a string which is, itself, delineated by those same quotes.

So, important and useful to understand, even if not my personal choice as an answer to this specific question.

[zmbd]

Just some more food for thought: Quotes (') and Double-Quotes (") - Where and When to use them