The vertical bar character in Acces SQL 1

[vladk]

Hi,

I am working with Access from VB6. Some bright head set up the field, which values contain doubled "pipe" characters. I need to locate the record based on this field and update this field. I do it from VB front-end.

The example of the valies is:
A||04||WBG||111111||38610||3

I need to replace the last character "3" with character "1". The field name is "key".

The direct approach does not work, causing error 3075

Invalid use of vertical bars in query expression 'A||04||WBG||111111||38610||1'.

I tried approach from here but it did not work for me:

http://support.microsoft.com/?scid=http://www.support.microsoft.com/kb/178070/en-us/

The initial (failed) query looks like this:

strSQL = "UPDATE Inputs SET Key =" & pstrNewKey & "," & _
" AI_quote_issue = " & ISSUE & "," & " AI_polversion = 1," & _
" DateTimeStamp = " & pstrDateTimeStamp & _
"WHERE key like " & pstrSelectedKey & _
"AND AI_quote_issue <> " & ISSUE & ";"

If anybody can help me make it work, it will be great!!!!

vladk

 

[PHV]

strSQL = "UPDATE Inputs SET Key =[!]'[/!]" & pstrNewKey & "[!]'[/!]," & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vladk]

PHV,

Thank you very much. As always, your answer shots right in the center. I actually ended up with this string, which appears to work for me:

strSQL = "UPDATE Inputs SET Key =" & """" & pstrNewKey & """" & "," & _
" AI_quote_issue = " & """" & ISSUE & """" & "," & " AI_polversion = 1," & _
" DateTimeStamp = " & """" & pstrDateTimeStamp & """" & _
" WHERE key = " & """" & pstrSelectedKey & """" & _
" AND AI_quote_issue <> " & """" & ISSUE & """" & ";"

Thank you!

vladk