DROP LETTER

[ZigZagZak]

I have a query that has 3 fields of criteria. [Branch], [OrderNumber], and
[ItemNumber]. The Item number is numeric most of the time, but sometimes
there are items that get split into .... 1A or 1B. 1A would be for the same
item as just 1.

My question is. Is there a way to have a query only use the numeric portion
of the string for its criteria? like with the wild card doesn't work because
its then using 1A & "*"...Hopefully that makes since.....

Thanks in advance for help!


Zach

 

[Ben]

Zig,

Try this

Select *
from A.itemnumber inner join Left(b.itemnumber,7)

with the above join syntax, you basically force the join between table A and
B on the first seven characters of the item number.

HTH,
Ben

 

[raskew via AccessMonster.com]

If the numeric portion always precedes the alpha portion, you can use the Val
() function, e.g.

x = "1A"
? val(x)
1

Bob

I have a query that has 3 fields of criteria. [Branch], [OrderNumber], and
[ItemNumber]. The Item number is numeric most of the time, but sometimes
there are items that get split into .... 1A or 1B. 1A would be for the same
item as just 1.

My question is. Is there a way to have a query only use the numeric portion
of the string for its criteria? like with the wild card doesn't work because
its then using 1A & "*"...Hopefully that makes since.....

Thanks in advance for help!

Zach