Wildcard has me stumped (1 Viewer)

Vivirtruvian

Registered User.
Local time
Today, 22:01
Joined
Jun 20, 2017
Messages
19
Hi all,

Shall keep this brief thanks to exhaustion:

Say I have a pallet ID 123456789

That ID goes into our system (this is how it is received as transmitted data from a customer)

However, the pallet itself has a barcode that scans as 00123456789

When I scan the barcode (into a form control, [forms]![frmSearch]![combo9]) I want my database to find pallet 123456789.

I have tried ID = Like "*" & [forms]![frmSearch]![combo9] but I get nothing. The query works if the form control and the pallet ID match identically, so there is nothing else confounding the query results.

I know in my loins what I am asking Access to do is the wrong way around... all I want it to do is find the in the database when the form control has some additional numbers around it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:01
Joined
Oct 29, 2018
Messages
21,358
Hi. Assuming ID=123456789 and the Form control has 00123456789 and also assuming ID and bar code are always all numbers, then you could try this.
Code:
ID=Val(FormControlHere)
 

Vivirtruvian

Registered User.
Local time
Today, 22:01
Joined
Jun 20, 2017
Messages
19
Hi theDBGuy,

Unfortunately this is not the case due to our wide customer base. Some customers use letters and characters in their IDs and the challenge all along has been coding the system robustly enough to retain customer data for ease of reporting rather than assigning additional IDs to stock that we receive.

I may have found a workaround using InStr()

Expr1: InStr([Forms]![frmSearch]![combo9],[tblProtein].[ID]) > 1

I was actually hoping there was a way to use InStr results to pass through a number to a Left() function to manipulate the query (something like Left([FormControl], InStrResult) but that is probably being too complicated!
 

deletedT

Guest
Local time
Today, 11:01
Joined
Feb 2, 2019
Messages
1,218
Hi theDBGuy,

Some customers use letters and characters in their IDs

If the id contains characters, then it's not a number.
You should use
Code:
Like "'*" & [forms]![frmSearch]![combo9] & "'"

Where do you use this? In a query? or in a Dcount(), DLookup(), etc?
 
Last edited:

Vivirtruvian

Registered User.
Local time
Today, 22:01
Joined
Jun 20, 2017
Messages
19
Hi Tera,

I will give that code a try. I have used that code but had "'*" closing the criteria.

It basically goes like this:

1. Barcode is scanned into an unbound text box which triggers an AfterUpdate event
2. A new form opens where the code that was scanned is placed into a hidden text box via OpenArgs
3. The query for the form looks at the text box value to bring up the rest of the data about the pallet

So it all works fine when whatever is scanned matches character for character with what ID is stored in the database, but has failed when redundant prefixes are present on the barcode.
 

Vivirtruvian

Registered User.
Local time
Today, 22:01
Joined
Jun 20, 2017
Messages
19
The more I think of it the more I think the Like criteria isn't going to work anyway. Logically, am I asking Access to find something in the database that contains what is in the text box, which includes the zeroes at the start. If it were the other way around (zeros contained in the data but not scanned in the barcode) it would make sense, as I would be saying to Access "go fetch me records that contain 123456789". Right now I am wording it to ask to fetch data that contains 00123456789, which of course none of it does.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:01
Joined
Jan 20, 2009
Messages
12,849
Logically, am I asking Access to find something in the database that contains what is in the text box, which includes the zeroes at the start.

What is wrong with what you already basically had ?

Code:
WHERE InStr([Forms]![frmSearch]![combo9], [ID])
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:01
Joined
Feb 28, 2001
Messages
27,001
If your problem is always just leading zeros, it should be possible to write a short loop to remove the leading zeros before you do the search. Something SIMILAR to this, you would have to play with it. But once this is finished, if your input method ALWAYS removes leading zeros when storing the codes, this might do your job.

Code:
Dim LeftCh as String, CurBarcode as String )
...
Do
    LeftCh = Left( CurBarcode, 1 )
    If LeftCh = "0" 
        Then 
            CurBarcode = Right( CurBarcode, Len( CurBarcode -1 ) )
        Else
            End Do
        End If
    Loop
 

Micron

AWF VIP
Local time
Today, 07:01
Joined
Oct 20, 2018
Messages
3,476
If your problem is always just leading zeros,
Apparently that is not the case:

Some customers use letters and characters in their IDs
If I understand the problem, there is a partial match between the pallet scan and the number you were sent. I suppose there's no appetite for coercing them send you the EXACT pallet number? In that case, I also wonder why you can't use the transmitted value as I think Galaxiom is saying. If they transmit 123456 and you've scanned 00123456 then no problem as you say. However, I don't see how that helps if they transmit 123456 and you scan 123-456. If that can happen, then I see the problem as being the process - which maybe it is anyway.
 

Users who are viewing this thread

Top Bottom