Function Problem

[R. Choate]

My problem, I think, is with "Mid([CSPCL4],7,2) AS CSPCL" shown below, but I'm not sure. If I just pick out any field from either
one of my tables and run a simple SQL on that field, it works fine. However, I've got to join these 2 tables, and I've got to add
criteria to restrict the records in my recordset. The formula shown as part of this SQL, is intended to extract the 7th and 8th
characters from a string, and match those 2 characters to the 2-character string of the CPCHG field in the other table. Again, my
connection is good and I've narrowed the problem down to this SQL. This query is actually run against an AS400 table, but I believe
my formula is the problem. Please help.



mySQL = SELECT CPRD.CPDESC, CUST.CCYCLE, CUST.CCMPNY, Mid([CSPCL4],7,2) AS CSPCL
FROM CPRD INNER JOIN CUST ON CPRD.[CPCST#] = CUST.CCMPNY
WHERE (((CUST.CCYCLE)="RW" Or (CUST.CCYCLE)="RR" Or (CUST.CCYCLE)="RC" Or (CUST.CCYCLE)="TR" Or (CUST.CCYCLE)="WR" Or
(CUST.CCYCLE)="SB" Or (CUST.CCYCLE)="SC" Or (CUST.CCYCLE)="ZB" Or (CUST.CCYCLE)="1" Or (CUST.CCYCLE)="2" Or (CUST.CCYCLE)="10" Or
(CUST.CCYCLE)="11" Or (CUST.CCYCLE)="12" Or (CUST.CCYCLE)="13" Or (CUST.CCYCLE)="14" Or (CUST.CCYCLE)="15" Or (CUST.CCYCLE)="16")
AND ((CUST.CCMPNY)="1" Or (CUST.CCMPNY)="3" Or (CUST.CCMPNY)="4" Or (CUST.CCMPNY)="5" Or (CUST.CCMPNY)="6") AND
((Mid([CSPCL4],7,2))=[CPCHG]))

 

[Douglas J. Steele]

Unfortunately, you don't indicate what the problem you're encountering is!


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

My problem, I think, is with "Mid([CSPCL4],7,2) AS CSPCL" shown below,

but I'm not sure. If I just pick out any field from either

one of my tables and run a simple SQL on that field, it works fine.

However, I've got to join these 2 tables, and I've got to add

criteria to restrict the records in my recordset. The formula shown as

part of this SQL, is intended to extract the 7th and 8th

characters from a string, and match those 2 characters to the 2-character

string of the CPCHG field in the other table. Again, my

connection is good and I've narrowed the problem down to this SQL. This

query is actually run against an AS400 table, but I believe

my formula is the problem. Please help.



mySQL = SELECT CPRD.CPDESC, CUST.CCYCLE, CUST.CCMPNY, Mid([CSPCL4],7,2) AS CSPCL
FROM CPRD INNER JOIN CUST ON CPRD.[CPCST#] = CUST.CCMPNY
WHERE (((CUST.CCYCLE)="RW" Or (CUST.CCYCLE)="RR" Or (CUST.CCYCLE)="RC" Or

(CUST.CCYCLE)="TR" Or (CUST.CCYCLE)="WR" Or

(CUST.CCYCLE)="SB" Or (CUST.CCYCLE)="SC" Or (CUST.CCYCLE)="ZB" Or

(CUST.CCYCLE)="1" Or (CUST.CCYCLE)="2" Or (CUST.CCYCLE)="10" Or

(CUST.CCYCLE)="11" Or (CUST.CCYCLE)="12" Or (CUST.CCYCLE)="13" Or

(CUST.CCYCLE)="14" Or (CUST.CCYCLE)="15" Or (CUST.CCYCLE)="16")

AND ((CUST.CCMPNY)="1" Or (CUST.CCMPNY)="3" Or (CUST.CCMPNY)="4" Or

(CUST.CCMPNY)="5" Or (CUST.CCMPNY)="6") AND

((Mid([CSPCL4],7,2))=[CPCHG]))

 

[R. Choate]

Thanks for responding, Douglas. The reason I didn't was just that I'm querying against an AS400 and getting an AS400 error CWBWB0036
from the server, and I thought that would scare people off or just have them telling me not to post AS400 problems on an Access
site. The problem is definitely my SQL, I know that. If I write it for just one field from either table, it works fine. I just think
I shouldn't have used the formula Mid([CSPCL4],7,2) AS CSPCL, and I don't know how to re-write it. This is basically an Excel
formula, which I think will work in Access, but definitely not on AS400.
--
RMC,CPA


Unfortunately, you don't indicate what the problem you're encountering is!


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

My problem, I think, is with "Mid([CSPCL4],7,2) AS CSPCL" shown below,

but I'm not sure. If I just pick out any field from either

one of my tables and run a simple SQL on that field, it works fine.

However, I've got to join these 2 tables, and I've got to add

criteria to restrict the records in my recordset. The formula shown as

part of this SQL, is intended to extract the 7th and 8th

characters from a string, and match those 2 characters to the 2-character

string of the CPCHG field in the other table. Again, my

connection is good and I've narrowed the problem down to this SQL. This

query is actually run against an AS400 table, but I believe

my formula is the problem. Please help.



mySQL = SELECT CPRD.CPDESC, CUST.CCYCLE, CUST.CCMPNY, Mid([CSPCL4],7,2) AS CSPCL
FROM CPRD INNER JOIN CUST ON CPRD.[CPCST#] = CUST.CCMPNY
WHERE (((CUST.CCYCLE)="RW" Or (CUST.CCYCLE)="RR" Or (CUST.CCYCLE)="RC" Or

(CUST.CCYCLE)="TR" Or (CUST.CCYCLE)="WR" Or

(CUST.CCYCLE)="SB" Or (CUST.CCYCLE)="SC" Or (CUST.CCYCLE)="ZB" Or

(CUST.CCYCLE)="1" Or (CUST.CCYCLE)="2" Or (CUST.CCYCLE)="10" Or

(CUST.CCYCLE)="11" Or (CUST.CCYCLE)="12" Or (CUST.CCYCLE)="13" Or

(CUST.CCYCLE)="14" Or (CUST.CCYCLE)="15" Or (CUST.CCYCLE)="16")

AND ((CUST.CCMPNY)="1" Or (CUST.CCMPNY)="3" Or (CUST.CCMPNY)="4" Or

(CUST.CCMPNY)="5" Or (CUST.CCMPNY)="6") AND

((Mid([CSPCL4],7,2))=[CPCHG]))

 

[Tom Ellison]

If you are using a pass-through query to the AS400, then you must
follow AS400 rules, and probably your question is for AS400 experts.

If you are only linked to the AS400 tables, then your queries are
entirely run by Jet, (assuming you're not running an ADP or other MSDE
based server engine) and you can use the MID() function just as with
any other Jet query.

If your connection to the AS400 is working well, which you can test by
just performing "SELECT * FROM <table>" for each table, then you
probably would not be seeing AS400 errors unless you are using
pass-through. So, it's beginning to look as though you really may be
doing a pass-through.

If it is a pass-through, then you are running the query on the AS400
and your question really is an AS400 question.

Now for the query. I think your logic could be reduced to:

SELECT CPRD.CPDESC, CUST.CCYCLE, CUST.CCMPNY,
Mid(CSPCL4, 7, 2) AS CSPCL
FROM CPRD
INNER JOIN CUST ON CPRD.[CPCST#] = CUST.CCMPNY
WHERE CUST.CCYCLE IN
("RW", "RR", "RC", "TR", "WR", "SB", "SC", "ZB", "1",
"2", "10", "11", "12", "13", "14", "15", "16")
AND CUST.CCMPNY IN ("1", "3", "4", "5", "6")
AND MID(CSPCL4, 7, 2) = CPCHG

Now, looking this over, I don't see anything obviously wrong.
However, since you're getting an error, it does occur to me to wonder
whether CUST.CCMPNY might not be text, but numeric, since all the
values for which you're filtering it are numeric. Just a possibility.

Also, CUST>CCYCLE: is it a variable length string, or fixed length.
If it is fixed, you might need to make all the choices 2 characters
long. As it is you have some that are one character long, and some
that are 2. This is not necessarily wrong, but it might be wrong.

Finally, make sure CSPCL4 is always at least 8 characters long, so the
MID function doesn't error.

Anyway, without knowing what the error means, this could be tough to
track down. Reducing the length of the SQL as I have done (if I
didn't destroy its meaning by misreading all the parentheses) could
really make it easier to debug. All those extra parens added by Jet
can be a source of problems!

Thanks for responding, Douglas. The reason I didn't was just that I'm querying against an AS400 and getting an AS400 error CWBWB0036
from the server, and I thought that would scare people off or just have them telling me not to post AS400 problems on an Access
site. The problem is definitely my SQL, I know that. If I write it for just one field from either table, it works fine. I just think
I shouldn't have used the formula Mid([CSPCL4],7,2) AS CSPCL, and I don't know how to re-write it. This is basically an Excel
formula, which I think will work in Access, but definitely not on AS400.
--
RMC,CPA


Unfortunately, you don't indicate what the problem you're encountering is!

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts