type mismatch error

[kkshansid]

CurrentDb.Execute ("update emp set rmk=rmk &'1' WHERE ISDATE(LEFT(DOB,2) & " / " & MID(DOB,3,2)& " / " & MID(DOB,5,4))=0")

vs

update emp set rmk=rmk &'1' WHERE ISDATE(LEFT(DOB,2) & " / " & MID(DOB,3,2)& " / " & MID(DOB,5,4))=0

same query within double quote runs fine in query editor
while gives type mismatch error while running in vba sub.kindly guide

[SteffenBeck]

Hi kkshansid

I am a little in doubt. Is the field rmk a string? And are the purpose to add the character ‘1’ to the value in that field?
For instance, update the value ‘abc’ to ‘abc1’?

First of all, try to insert spaces:

CurrentDb.Execute ("update emp set rmk=rmk &*space*'1' WHERE ISDATE(LEFT(DOB,2) & " / " & MID(DOB,3,2)*space*& " / " & MID(DOB,5,4))=0")

Best regards
Stef

[zmbd]

It isn't running in VBA more than likely because you haven't properly escaped your quotes.

You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.

Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.

So to use your code:

'(the following is partially air-code)
DIM daoDB as DAO.Database
DIM strSQL as string
'
set daoDB = CurrentDB
'
'
strSQL = "update emp set rmk = rmk & '1' " & _
   "WHERE (ISDATE(LEFT(DOB,2)" & _
   " / " & Mid(dob, 3, 2) & _
   " / " & Mid(dob, 5, 4) & "=0)"
'
'now you can insert a debug print here for troubleshooting
' - press <ctrl><g> to open the immediate window
' - you can now cut and paste this information for review!
'
debug.print "Your criteria = " & strSQL
'
'now use the string in your code:
daoDB.Execute
 
if not daoDB is nothing then set daoDB = Nothing
 
 

I think you are going to find that your string isn't resolving the way you expect it to.
Also note that I set a variable to hold the pointer to the current database. If you are using multiple "currentdb" references within you're code, you are potentially tying up system resources that might also cause you other issues.

Your WHERE clause is also a bit clunky... why wouldn't your DOB field at the table level be set to a date/time type-cast?

You might also find the following information also very helpful: Quotes (') and Double-Quotes (") - Where and When to use them