Search Box Using Concatenate values?

[abublitz]

This has me pretty stumped and I've not really had much luck googling it.




I have two tables that are linked

tblPartNumberSeries - holds information pertaining to the number ranges for my parts
tblParts - holds the actual part number data

On my form, frmParts I want to place an unbound textbox/button to use as a search. The issue is, the value that the user will be searching for is a concatenate value that is not stored in either of the tables.

For example, I want to go to the record for part number 010176

010 = prefix that is stored in PartNumberPrefix in the tblPartNumberSeries
176 = the value stored in PartNumber in tblParts

On my form, I have an un-editable textbox, txtPartNumberDisplay that shows this part number to the user, for their benefit, but this value is not stored anywhere. This value is generated by using this code in the Control Source
=[cboPartNumberSeriesID].[Column](1) & [txtPartNumber] so it takes what the user selects in cboPartNumberSeriesID combobox (values from tblPartNumberSeries) and adds it to the value in txtPartNumber which is the field for the PartNumber in tblParts.

Also, while all my concatenate part numbers are all the same length (6 characters), my prefix are 3-4 characters in length.


Is this confusing? I can link my DB too.

[pbaldy]

How do you plan on searching? You can certainly use a concatenated value, like in a query:

WHERE Field1 & Field2 = "010176"