Query help needed

J

JB_96RS

I have set up the relationship between two tables in my query. The
field is a Lot ID and it is in this format.... "2-11000". Sometimes
there is "E" at the end of this. Question is: How do I make the query
think that the two fields are the same even if one contains an "E"?


Thanks,

JB
 
K

Kevin Sprinkel

One way is to calculate a new field in another column that
strips off the E, if it exists, then do the selection on
this field:

=IIf(Right([LotID],1="E",Left([LotID],len([LotID]-1),
[LotID])

HTH
Kevin Sprinkel
 
J

John Vinson

I have set up the relationship between two tables in my query. The
field is a Lot ID and it is in this format.... "2-11000". Sometimes
there is "E" at the end of this. Question is: How do I make the query
think that the two fields are the same even if one contains an "E"?
Click to expand...

Well... they're NOT the same. The text string "2-11000" is NOT equal
to the text string "2-11000E"! If you have a relationship on this
field, Access will have put these two *different* values into the
relevant table indexes, and will not consider them a match.

First suggestion: obey First Normal Form by making your fields
"atomic". If 2-11000 has its own meaning, and E has *its* own meaning,
store them in separate fields and concatenate them for display.

Second (probably less desirable for purity but simpler to maintain
your present structure) - don't join the fields in the query at all;
instead put a criterion on the first table's LotID of

=Left([secondtable].[LotID], 7)

to trim off the E before comparing.
 
Top