M
Michael Koenig
Howdy!
I've got a real stumper here that I'm HOPING (praying...) that you all can
assist me with.
I'm building a database using a combination of SQL Server 2005 and AS-400
data files for the backend, and Access 97 as the frontend/UI. (Yes, I know...
please don't tease...)
As is often the case when dealing with legacy data, someone in the past
decided to use Chr(35) ("#") as a part of their field-naming convention when
creating an AS-400 data file without thinking of the needs of others. As a
result, I've got two fields that have this character in the field name, and
they're causing me pain.
I'm trying to create an on-the-fly ODBC pass-through query to look up data
pertaining to a specific account number that a user enters into my GUI, which
is something that I've done a thousand times before. The fields that have
this character (named RLEXT# and RLNEW#) work fine when I create a static
pass-through query using the SQL view of the Query Design Grid, using the SQL
string below:
SELECT CLPACMST.ACCTNO, CLPREFAL.RLEXT#, CLPACMST.APPLCT, CLPACMST.COAPPL,
CLPCUSTD.APPLCT AS Custodian, CLPHDOC.CUSTRF FROM (CLPCUSTD RIGHT JOIN
(CLPACMST LEFT JOIN CLPHDOC ON CLPACMST.ACCTNO = CLPHDOC.ACCTNO) ON
CLPCUSTD.CSTSEQ = CLPHDOC.CSTSEQ) LEFT JOIN CLPREFAL ON CLPACMST.ACCTNO =
CLPREFAL.RLNEW# WHERE (((CLPACMST.ACCTNO)=88098682))
However, because Access VBA likes to have it ITS way, when I try to
re-create the same SQL string in VBA as a part of a DAO recordset, I get the
following error:
"Run-time Error '3075': Syntax error in date in query expression
'CLPREFAL.RLEXT#'."
Now, I know that this is due to Access viewing the # as the start of a Date
string, so I know WHY it is happening... but can anyone offer a suggestion on
how to get around the issue? I can't get the field names changed, and I have
already tried to enclose the RLEXT# in brackets to no avail, so I'm at a
loss. If need be, I can pull the data fields from my AS-400 and rename them
at runtime, but the file has over 800,000 records in it, so I'd REALLY rather
not go there. Any other suggestions?
Thank you in advance - you all are real life savers to a geek in need! (Geek
being my AFFECTIONATE term for myself, and not an offensive label to be
applied to others!)
I've got a real stumper here that I'm HOPING (praying...) that you all can
assist me with.
I'm building a database using a combination of SQL Server 2005 and AS-400
data files for the backend, and Access 97 as the frontend/UI. (Yes, I know...
please don't tease...)
As is often the case when dealing with legacy data, someone in the past
decided to use Chr(35) ("#") as a part of their field-naming convention when
creating an AS-400 data file without thinking of the needs of others. As a
result, I've got two fields that have this character in the field name, and
they're causing me pain.
I'm trying to create an on-the-fly ODBC pass-through query to look up data
pertaining to a specific account number that a user enters into my GUI, which
is something that I've done a thousand times before. The fields that have
this character (named RLEXT# and RLNEW#) work fine when I create a static
pass-through query using the SQL view of the Query Design Grid, using the SQL
string below:
SELECT CLPACMST.ACCTNO, CLPREFAL.RLEXT#, CLPACMST.APPLCT, CLPACMST.COAPPL,
CLPCUSTD.APPLCT AS Custodian, CLPHDOC.CUSTRF FROM (CLPCUSTD RIGHT JOIN
(CLPACMST LEFT JOIN CLPHDOC ON CLPACMST.ACCTNO = CLPHDOC.ACCTNO) ON
CLPCUSTD.CSTSEQ = CLPHDOC.CSTSEQ) LEFT JOIN CLPREFAL ON CLPACMST.ACCTNO =
CLPREFAL.RLNEW# WHERE (((CLPACMST.ACCTNO)=88098682))
However, because Access VBA likes to have it ITS way, when I try to
re-create the same SQL string in VBA as a part of a DAO recordset, I get the
following error:
"Run-time Error '3075': Syntax error in date in query expression
'CLPREFAL.RLEXT#'."
Now, I know that this is due to Access viewing the # as the start of a Date
string, so I know WHY it is happening... but can anyone offer a suggestion on
how to get around the issue? I can't get the field names changed, and I have
already tried to enclose the RLEXT# in brackets to no avail, so I'm at a
loss. If need be, I can pull the data fields from my AS-400 and rename them
at runtime, but the file has over 800,000 records in it, so I'd REALLY rather
not go there. Any other suggestions?
Thank you in advance - you all are real life savers to a geek in need! (Geek
being my AFFECTIONATE term for myself, and not an offensive label to be
applied to others!)