Criteria from 2 fields to display one result

[TKM]

I need to retreive a Transaction Type (field named HLP_TEXT) based from the
following two fields.

1. TR_NO (has 4 digits such as 3112, 4009 or any other 4 digits combinations)
2. HLP_CODE (has 5 digits isuch as 30003, 40034 or any other 5 digit combos)

Notice that the last digit of the HLP_CODE is the first digit of the TR_NO?
This number represents a Transaction Type. I need to place this in my
criteria so I can find a Transaction Type for the given TR_NO. I really need
a place to start with this one so any help is appreciated!

 

[Marshall Barton]

I need to retreive a Transaction Type (field named HLP_TEXT) based from the
following two fields.

1. TR_NO (has 4 digits such as 3112, 4009 or any other 4 digits combinations)
2. HLP_CODE (has 5 digits isuch as 30003, 40034 or any other 5 digit combos)

Notice that the last digit of the HLP_CODE is the first digit of the TR_NO?
This number represents a Transaction Type. I need to place this in my
criteria so I can find a Transaction Type for the given TR_NO. I really need
a place to start with this one so any help is appreciated!

That's pretty vague. Are these Text fields or are they
numeric type? Are these fields in the same table or
different tables?

Maybe you can adapt this kind of condition to your needs:

Right(HLP_CODE, 1) = Left(TR_NO, 1)

 

[TKM]

All are text fields. They are in two seprate tables in one query.

 

[TKM]

My results should be provide all the fields in my HLP_TEXT field that match
the following criteria. If the first number in TR_NO field matches the last
digit in the HLP_CODE field then show all those records. All the fields are
text from two seprate tables in one query. hope this helps?

 

[Marshall Barton]

If you need the entire query:

You really should be more specific. Are you matching every
record in this table to every record in that table? Or are
the records in this table linked to the records in that
table by the usual one-many PK-FK arrangement?

Since i can't do the latter without more information, I just
give an example of the former:

SELECT [this table].*, [that table].*
FROM [this table], [that table]
WHERE Right([this table].HLP_CODE, 1) = Left([that
table].TR_NO, 1)