Query criteria for first two digits of a 10 digit field (1 Viewer)

[hardhitter06]

Hi,

I have a field in every record called UNSPSC which is a 10 digit code.

I want to be able to search by UNSPSC with only the first two digits of my searched number to retreive matching UNSPSCs.
(I have a method to my madness for doing it this way)

I.E. I search the UNSPSC 0123456789...I want to return all records with the UNSPSC of "01" [first two digits].

I know within my UNSPSC field criteria in my query...I need something along the lines of a Like "*" This returns all fields but i can't find anything online that shows me how to limit that to the first two digits.

Also I will be using a form text field to enter the number i want to search by which will be within the criteria as well:

[forms].[frmSplitOrderingSearch].[text4] I need to beef this up to only take the first two digits of the 10 digit number i searched by to return all matching records.

I know this is a mess but thanks

 

[Beetle]

Create a calculated field in a query that returns only the first two digits of your field like;

ShortUN:Left([UNSPSC], 2)

Include any other fields that you need returned in this query, put your Like criteria in the ShortUN column and use this query to return your results.

 

[hardhitter06]

@ Beetle, don't quite understand what you mean but I'm not sure if we are on the same page either so let me try to explain this another way.

I have a form that has an unbound Text47 that will be used to enter in a UNSPSC number to search by. Behind the search button is this code:

     Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSplitOrderingSearchAllUNSPSC"
    strIdNumber = Me.Text47
 
If Len(strIdNumber) <> 8 Then
    MsgBox "UNSPSC must be 8 characters, re-enter.", vbOKOnly, "Error"
    Else
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

Behind the form "frmSplitOrderingSearchAllUNSPSC" is a query. This query at this moment has this code :

 [forms].[frmSplitOrderingSearch].[text47]

within the field UNSPSC.

Right now I can enter in a UNSPSC number and get all records that have that number for that field.

There's two things that I'm missing to accomplish what I want.

I want to be able to search that 8 digit number like i do now, but only use the first 2 characters (almost as a wildcard if you will) to return all records that match those first two digits.

For example I have the UNSPSC number 12345678. I want the user to still enter all 8 digits but behind the scenes the search chops off the last 6 digits so it bascially searches 12###### to return all records that have a UNSPSC with "12" in the beginning.

So the two things I need help with:

1. Making that search behind the scene chop the UNSPSC number down to two digits to use that as the search

2. Adjust something in my query where the new number from No. 1 will return all records with the beginning two digits

I know this seems like a lot and you might say well why don't you use have the user enter the first two digits of the UNSPSC number to start with and search for all matching records but we are trying to avoid work arounds and this is the best way to do it.

Even if i went with this latest option, I still need some type of criteria in the query that will look at all record's UNSPSC number and to return all that match the first two digits of the searched number.

I hope i explained this better - thanks for your help so far

 

[hardhitter06]

@ Beetle, Your response finally clicked and I did what you said and that works...it answers my No. 2 question.

I'm assuming my next step is to add/edit some code in my search command button to truncate the last 6 digits of my typed UNSPSC number..any ideas?

 

[Beetle]

You could place a hidden text box on your form that uses the Left function like in the previous example to return only the first two digits of whatever is entered in the first text box;

=Left([YourFirstTextBox], 2)

Then reference this hidden text box in the criteria row of the calculated ShortUN field in the query.

 

[hardhitter06]

Beetle, you are a very smart man! thank you so much