Escaping # chr(35) in an sql

[craigachan]

I have some fields that have # in the string representing number such as #1 and #2. I can't seem to find anywhere how to escape the # in an sql? Can someone help me?

 

[jdraw]

I don't know of a way to escape them. Can you remove them and put the data into a file you can process?

 

[MarkK]

Can't you just delimit them with double quotes like you would any other string character? What's your SQL?

SELECT "#" & Field1 As MyNumber 
FROM MyTable

 

[craigachan]

I have to process a lot of records. One of the fields may have a string such as:

field1 = "45 y/o #5 xray to be taken" or "35 y/o #23 Will bring referral"
field2 = "SOMETHING"

my sql: "INSERT INTO mytable (field1, field2) " & _
"VALUES ('" & field1 & "','" & field2 & "')"

Of course the # in field1 causes a runtime error. So I have to deal with this some how. The question is how do I do this. I have fixescape routines to fix quotes and double quotes, but do not know how to handle '#'.

thanks for any help.

 

[MarkK]

Use parameters in a querydef, like . . .

const SQL as string = _
   "INSERT INTO mytable " & _
      "( field1, field2 ) " & _
   "VALUES " & _
      "( prm0, prm1 )"

dim qdf as dao.querydef

set qdf = currentdb.createquerydef("", sql)
with qdf
   .parameters("prm0") = "45 y/o #5 xray to be taken "
   .parameters("prm1") = "SOMETHING ' "" !@#$%^&*" & chr(34)
   .execute dbFailOnError
   .close
end with
set qdf = nothing

See what's going on there? It's very fast and reliable and all your delimiters are handled by the query.

 

[Galaxiom]

The problem is probably caused by using DoCmd.RunSQL which is interpreted by Access before being sent to the database engine.

Use

CurrentDb.Execute mysql, dbFailOnError

 

[craigachan]

Galaxiom: I tried Currentdb.execute sqlM, dbfailonerror
but I got an runtime also on this using my original sqlM statement.

So I tried this code but it gets a runtime 3134 - syntax error...

dim rs as dao.recordset
Dim pDate As String, pPName As String, pRName As String
Dim pILight As String, pALight As String, pFLight As String, pDesc As String
Const sqlM = "INSERT INTO tempImplantManagement " & _
                       "( DOS, PName, RName, ImplantLight, AbutLight, FinalLight, Desc) " & _
                      "VALUES " & _
                         "( pDate, pPName, pRName, pILight, pALight, pFLight, pDesc )"
set rs = currentdb.openrecordset("anothertable")
strImplantLight = mydate1
strAbutLight = mydate2
strFinalLight = mydate3

Dim qdf As dao.QueryDef
Set qdf = CurrentDb.CreateQueryDef("", sqlM)
             With qdf
                    .Parameters("pDate") = rs!nDate
                    .Parameters("pPName") = rs!PName
                    .Parameters("pRName") = rs!RName
                    .Parameters("pILight") = strImplantLight
                    .Parameters("pALight") = strAbutLight
                    .Parameters("pFLight") = strFinalLight
                    .Parameters("pDesc") = rs!Desc
                    .Execute dbFailOnError
                    .Close
              End With
Set qdf = Nothing
set rs = nothing

 

[JANR]

A shot in the dark. Your fieldname "Desc" in your insert statment is a SQL reserved word. Put square brackets around it.

Const sqlM = "INSERT INTO tempImplantManagement " & _
                       "( DOS, PName, RName, ImplantLight, AbutLight, FinalLight, [COLOR="Red"][[/COLOR]Desc[COLOR="red"]][/COLOR]) " & _
                      "VALUES " & _
                         "( pDate, pPName, pRName, pILight, pALight, pFLight, pDesc )"

JanR

 

[arnelgp]

JANR is right, Desc is a reserved word, this is where the problem and not having # in your string. i tried adding record with # on it and it passed through without error.

 

[craigachan]

You were right about Desc. I've been working with this db for so long that It didn't catch my eye. the [Desc] took care of it.

I ran my original code with the [Desc] and it also worked so it took care of the '#' in the string.

So does this mean we still don't know how to take care of the # escape for those looking for a solution to this thread?

 

[Galaxiom]

So does this mean we still don't know how to take care of the # escape for those looking for a solution to this thread?

There is no need to escape those characters in a string. I just tested it and it works fine with both DoCmd.RunSQL and CurrentDb.Execute

 

[craigachan]

Thanks for all the insight, discussion and help.