question marks in values

G

Guest

I am having an issue with values having question marks in my table.

Example
Table ABC contains the values W45,W50,U23,U25,R0,Z23 in Field 1
Table CDF has the following records

Field1 Field2
W?? text1
Z2? text2
U?? text3
R0 text4

I want to have W45 and W50 return text1, U23 and U25 return text3, R0 return
text4, and Z23 return text2. I can't just join ABC.Field1 to CDF.Field1
because of the question marks. Any ideas what the SQL or query should look
like to accomplish this?

Any help would be greatly appreciated.

TYIA
 
G

Guest

Seems that your data isn't broken out far enough. "W" has meaning in one
table while "W45" and "W50" each have different meanings in another table.
Therefore you need to break up the data like "W" and "45" into two different
fields. Then you would have a foriegn key to link to the CDF table to get the
results that you wish.

You could combine the two fields back together to in forms or reports to see
"W45" again.
 
G

Guest

Thanks for the reply but I can't do it that way. The first table is data from
an outside source. I doesn't mater to me what the next two letters are I need
everything that begins with a W to get text1 regardless of what follows the
W. I don't know all the combinations of W that can be used. I just know the W
values have 3 characters. Technically I could change the W?? to W*.
Ultimately it means the same thing from my point of view. The outside values
come in one field. I can't split the field because some of the values need to
look at just the first character, some the first 2, some all 3. Some of the
values in the outside table only have 2 characters.
 
G

Guest

You probably can use a conversion table like --
Field1 Field2
W text1
Z2 text2
U text3
R0 text4

Then in your query add a column like --
RMKS: IIf([YourField] Like [Field1] & "*", [Field2], "Unknown")
 
J

John Nurick

Hi Tasha,

This is easy if you want to match records on the first character of
Field1, e.g.

SELECT ABC.Field1, CDF.Field2
FROM ABC INNER JOIN CDF
ON Left(CDF.Field1, 1) = Left(ABC.Field1, 1)
;

or

SELECT ABC.Field1, CDF.Field2
FROM ABC INNER JOIN CDF
ON CDF.Field1 LIKE Left(ABC.Field1, 1) & '*'
;

It gets more complicated if (as your sample suggests) sometimes you want
to match on the first character (e.g. W??) and sometimes on more than
one character (e.g. Z2? and R0). Maybe something like this will do the
job:

SELECT ABC.Field1, CDF.Field2
FROM ABC INNER JOIN CDF
ON ABC.Field1 LIKE
IIF(
InStr(CDF.Field1, '?')>0,
Left(CDF.Field1, InStr(CDF.Field1, '?') - 1),
CDF.Field1
) & '*'
;


to return
 
Top