Switching from ADO to DAO brought up new error in SQL syntax

[Guest]

The following 2 debug prints are
examples of code that used to work when I was using ADO

Now, only the top one works - since I've switched to DAO
I'm thinking that there's some sort of escape code that is inadvertently
being read - even though I have the string contained between single quotes.

'=========Works=======
UPDATE [tbl_AIC_LOC] SET [FormatOverride] = 's
%AIData=$p(%AIData,"^",1,2),%AIData=$$USIIn^ASSIFM1(%AIData) 2' WHERE
([CustomizationRec] = '894505') AND ([Environment] = 'REGNHIM') AND
([AICIndx] = 1) AND (ActualDateTime = #2005-12-08#)

'======Does Not work
UPDATE [tbl_AIC_LOC] SET [FormatOverride] = 's
%AIDataI(587)=$$ApptDt^AYKPHSCH($p(%AIM("SCH","SCH"),"|",5))' WHERE
([CustomizationRec] = '894505') AND ([Environment] = 'REGNHIM') AND
([AICIndx] = 1) AND (ActualDateTime = #2005-12-08#)

I can provide the rest of the code, if necessary but I'm hoping these DEBUG
examples can shed some light on my hunch.

 

[David C. Holley]

Try copying and pasting the code in the Query Builder. You should get a
more specific error message. I'm not that familar with the nuances of
DAO when it comes to SQL statements so I can't see what's wrong. The
double quotes though stand out, but not knowing what's going on with the
new value for [FormatOverride], I don't know if that's the problem.

 

[david epsom dot com dot au]

The only thing in their that looks like an escape code
is the pipe symbol, which used to be an Access escape
symbol. Are you working in Access? Which version?
Which DAO version?

(david)

 

[Van T. Dinh]

David

The problem was indeed the pipe character. This has been resolved in a
thread in the Queries newsgroup.

According to the O.P., the pipe character is interpreted as bitwise OR by
JET 3.5 which he is using.

 

[Guest]

Thank you for the follow-up Van :~)

The pipe symbol is not an OR in JET SQL - it was an
escape symbol out to Access. (It allowed you to paste
unresolved form references into queries) Apparently an
Access 1 feature maintained for backward compatibility
through to Access 97/Jet 3.51 - I only knew about it
because of the complaints when it was broken by Access
2000/Jet 4.0

(david)

 

[Van T. Dinh]

Thanks, David

I only repeated what the O.P. wrote ... Since I don't have A97 anymore, I
couldn't test. In answering the O.P. question, I was suspicious of the pipe
character but when I tested in A2003, it was OK.

The O.P. was using JET 4.0 but somehow thought that JET 4.0 doesn't have
replication so he switched back to JET 3.5 and hence the problem.