Wildcard Question

[BTilson]

I am working on an app that looks at data from our warehouse locaton database. The DB stores location information as a series of alphanumeric strings in the format of RACK_ID & BIN_ID.

For instance, TT0, HHH8, N2, etc. So far, so simple.

However, I need to create some reports that print specific racks. "Show me all bins in rack TT" This seems easy, but there is also a rack TTT, and the equivalent problem occurs with most of our locations. (Rack A, AA, AAA, AAAA, etc)

This means the simple approach of using "TT*" as the query expression invalid, becasue it will show me TT1 as well as TTT1.

I could hardcode in every location, but I think that's a pretty bad way to go about it, especially since it's very likely that we may rework our rack naming scheme at some point.

So, does anyone know what sort of wildcard setup I need to use to pull only TT records, but NOT TTT records?

Thanks in advance for any suggestions!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[Remou]

Is the Rack_Id always 2 characters? If so, have you considered Left?

WHERE Left(Loc,2)=var

 

[BTilson]

No, unfortunately. The rack ID is the letter portion (T, TT, TTT, etc) and the bin ID is the number section (usually 0 - 10)

So I can have T0 (Rack T, Bin Zero) and TTT10 (Rack TTT, Bin Ten) and every combination in between.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[PHV]

Perhaps this ?
Like 'TT#*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Perhaps left could still work:

SELECT Code
FROM tblC
WHERE Left(

,Len([Enter Code]))=[Enter Code]

 

[Remou]

Oops:

SELECT Left(

,Len([Enter Code])) AS C
FROM tblC
WHERE Left([AccountCode],Len([Enter Code]))=[Enter Code]

 

[BTilson]

PHV: That approach was actually the first thing I tried, but it always returns zero records. It's almost as if my Access doesn't even recognize the # as the 'numerical wildcard' for some reason.

Remou: Gave that a try, it doesn't seem to make any difference. I still get TT1 and TTT1 (etc) when I enter TT as my criteria.

Thank you both for the help thus far, however!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[Remou]

Is that my second post? The first one was wrong.

SELECT Left(

,Len([Enter Code])) AS C
FROM tblC
WHERE Left([Code],Len([Enter Code]))=[Enter Code]

 

[BTilson]

Remou, I used your second post, at least as best I can tell. Here is my SQL:

SELECT SYSADM_PART_LOCATION.LOCATION_ID, SYSADM_PART_LOCATION.PART_ID, SYSADM_PART_LOCATION.DESCRIPTION, SYSADM_PART_LOCATION.QTY, SYSADM_PART_LOCATION.WAREHOUSE_ID, SYSADM_PART.STOCK_UM, getMaterialDescriptor([PART_ID]) AS type, SYSADM_PART.COMMODITY_CODE, SYSADM_PART.PRODUCT_CODE, [b]Left([LOCATION_ID],Len([forms]![lookup]![text0].[value])) AS TEMP[/b]
FROM SYSADM_PART_LOCATION INNER JOIN SYSADM_PART ON SYSADM_PART_LOCATION.PART_ID = SYSADM_PART.ID
WHERE (((SYSADM_PART_LOCATION.QTY)>0) AND ((SYSADM_PART_LOCATION.WAREHOUSE_ID) Like [forms]![lookup]![text10].[value]) AND ((SYSADM_PART.PRODUCT_CODE) Like [forms]![lookup]![text14].[value]) AND [b]((Left([LOCATION_ID],Len([forms]![lookup]![text0].[value]))) Like [forms]![lookup]![text0].[value]));[/b]

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[Remou]

You could try equals, rather than like:


((Left([LOCATION_ID],Len([forms]![lookup]![text0].[value])))[red] = [/red][forms]![lookup]![text0].[value]));

 

[BTilson]

Tried that as well, no difference unfortunately.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[Remou]

That is a pity, I tested in Access 2000 before I posted. I will look into it a little more.

 

[BTilson]

I am using 2002, but the file I am working in is 2000 format. Could that make a difference?

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[BTilson]

I found the solution! As weird as it may be... One of the guys here suggested it, I didn't expect it to work, but it does.

Say I want just the TT locations, all I have to put in for the criteria (in my original query, not the modified version) is "TT_"

Brooks Tilson
Database Development
Tilson Machine, Inc.