Need help w/ Challenging Query

[Tom]

I need some help w/ creating an "error catching" query.

The 2 fields (Branch & Billet) are the 2 fields to be compared. In the
hierarcy, "Branch" is superior to "Billet".

There might be instances where billet code does not match the criteria of
the branch. Hence, I need to identify those billets.

Below are 20 sample records. The ones that have an "X" in error column must
be identified by the query.

But, first, let me explain what is a "good" or "bad" billet by showing some
examples:
- Record1: Both Branch and Billet are identical - that's ok
- Record2: "TC-5" of Branch are the first 4 characters of "TC-51" - that's
ok
- Record3: "TC-5" of Branch are the first 4 characters of "TC-5A" - that's
also ok
- Record5: "TC-50" of Branch are NOT the first 4 characters of "TC-511" -
that's wrong. If billet had been e.g. "TC-509", it would have
been a match.

Now, above have been based on a branch with 4 characters (TC-5) or 5
characters (TC-50). But there are also instances where I may have 6
characters e.g. "TC-510".

In such case, only the first 5 characters of the branch are determined for
the billet.
For instance, record # 9 shows "TC-510" and the matching billet of
"TC-516-VNC" is okay.

However, the next record (#10) is wrong. Here's billet "TC-530-A" does not
match branch "TC-520".


So, again, looking at the record set below, I need to end up w/ a query that
pulls
only records #5 and #10.

Does anyone have an idea as to how I could create such query?

Thanks,
Tom


*****************************************************


Branch Billet ERROR
====== ====== =====
1. TC-5 TC-5
2. TC-5 TC-51
3. TC-5 TC-5A
4. TC-50 TC-50
5. TC-50 TC-511 X
6. TC-510 TC-510
7. TC-510 TC-511
8. TC-510 TC-514-R
9. TC-510 TC-516-VNC
10. TC-520 TC-530-A X
11. TC-520 TC-521
12. TC-530 TC-538-R
13. TC-530 TC-530A
14. TC-530 TC-530B
15. TC-530 TC-530C
16. TC-530 TC-530D
17. TC-530 TC-530E
18. TC-530 TC-530F
19. TC-530 TC-530G
20. TC-530 TC-530H

 

[Jeremy Noland]

Use the SQL version of string Length (strlen?) and
utilize the Left function. E.G. :

where strlen = string Length of Branch

IIf ( Left([Branch], strlen-1) Like Left([Billet],
strlen), "OK", "Not OK")

Sorry if it is messy, but maybe this gets you going in
the right direction...

 

[Tom]

Jeremy,

thanks for sharing this w/ me... unfortunately, it did't produce the proper
results.

Tom

 

[Gary Walter]

Hi Tom,

try

SELECT Branch, Billet
FROM yourtable
WHERE
(((Switch(Len([Branch])=4,[Branch]<>Left([Billet],4),
Len([Branch])=5,[Branch]<>Left([Billet],5),
Len([Branch])=6,Left([Branch],5)<>Left([Billet],5),
True,False))=-1));

Please respond back if I have misunderstood.

Good luck,

Gary Walter

 

[Tom]

Gary:

Thank you so much for your feedback.

This works great!!! I'm totally thrilled!!!


Again, thanks.
Tom