Using Right(X,5) Command in a query

[Guest]

Howdy from Oklahoma!

I am relatively new to ACCESS and just getting intoduced to VB.

I have a query that is pulling a Lot# from a table and the lot# has the
Purchase Order as the last 5 or 6 characters of the Lot #.
So, an example of a lot # would be 77070-17892. Where 17892 is the Puchase
Order #.
Now, sometimes the Lot# will have a "T" in it and in that case the Purchase
Order # will be 6 digits instead of 5 as in above.
Here is this ones example of this type of Lot# 77071-T16743. Where T16743 is
the Purhase Order # for this record.

Now I have an ACCESS query field that looks like this "PO:
Right(RTrim([lotno]),5)" and it works fine for pulling the Puchase Order#
form the Lot# in the first example, BUT in the second example I need it to
give me the last 6 digits.

And I am thinking these would be a way to build some type of "IF" statement
to where if the Lot # has a "T" in then pull the last 6 digits and if it does
not have a "T" then just pull the last 5 digits of the Lot #.

I need this Purchase order # to compare it to another field elsewhere in the
query so I need both the 5 digit and 6 digit forms of the Purchase Order.

Any help would be GREATLY GREATLY appreciated!!!
And as always, THANKS IN ADVANCE!!!!

Chip

 

[Tom Ellison]

Dear Chip:

When the Lot# has a T in it, do you mean anywhere, or specifically in the
6th position from the end? I'll assume the latter.

Now, a test for this would be LEFT(RIGHT([Lot#], 6), 1) = "T". When this is
true, put a T in front of the last 5 characters:

IIf(LEFT(RIGHT([Lot#], 6), 1) = "T", "T", "") & RIGHT([Lot#], 5)

Does this make sense? Does it work?

Tom Ellison