# in field causes error

R

RD

Hi again.

Problem:
I've exported a bunch of huge tables from an Oracle db that I'm restricted to
using a particular interface for. This front end exports null values as a
string: #EMPTY
I have to export the tables as txt files because many of them run 300k to over
500k records. Then I import them into Access 2K making all the fields text data
type (any other data type causes import errors).
Now I've got a boatload of tables with boatloads of fields containing "#EMPTY".

This code produces a syntax error:
'Fields are numbered from 0 so we start at 1 to avoid the ID field
For i = 1 To rs.Fields.Count - 1
sField = rs.Fields(i).Name
sSql = "UPDATE " & strTable & " SET [" & strTable & "]. _
[" & sField & "] = Null "
sSql = sSql & "WHERE ((([" & strTable & "]. _
[" & sField & "])='#EMPTY'));"
db.Execute sSql, dbFailOnError
Next i

Question:
I *know* it's that damn hash mark! I need to do this programatically because it
will be automated once I work out the kinks. How do I get around the hash mark?

Thanks,
RD
 
D

Dirk Goldgar

RD said:
Hi again.

Problem:
I've exported a bunch of huge tables from an Oracle db that I'm
restricted to using a particular interface for. This front end
exports null values as a string: #EMPTY
I have to export the tables as txt files because many of them run
300k to over 500k records. Then I import them into Access 2K making
all the fields text data type (any other data type causes import
errors).
Now I've got a boatload of tables with boatloads of fields containing
"#EMPTY".

This code produces a syntax error:
'Fields are numbered from 0 so we start at 1 to avoid the ID field
For i = 1 To rs.Fields.Count - 1
sField = rs.Fields(i).Name
sSql = "UPDATE " & strTable & " SET [" & strTable & "]. _
[" & sField & "] = Null "
sSql = sSql & "WHERE ((([" & strTable & "]. _
[" & sField & "])='#EMPTY'));"
db.Execute sSql, dbFailOnError
Next i

Question:
I *know* it's that damn hash mark! I need to do this programatically
because it will be automated once I work out the kinks. How do I get
around the hash mark?
Click to expand...

I don't think it's the hash mark at all. You have broken your
statements, for continuation on new lines, in inaappropriate places.
Try this:

sSQL = "UPDATE " & strTable & " SET [" & strTable & "].[" & _
sField & "] = Null "
sSQL = sSQL & "WHERE ((([" & strTable & "].[" & _
sField & "])='#EMPTY'));"
 
A

Alex Dybenko

Hmm, just tried to make a sample query with #EMPTY - works fine
if you paste SQL in a new query - will it work?
 
R

RD

Thanks for your responses, Dirk and Alex. I ended up fixing this one myself.
It was a matter of using enough "quote" marks. """" :)

I have a new issue posted under a different header.

Regards,
RD
 
Top