Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-06-2019, 08:29 PM   #1
Vivirtruvian
Newly Registered User
 
Join Date: Jun 2017
Posts: 19
Thanks: 4
Thanked 1 Time in 1 Post
Vivirtruvian is on a distinguished road
Wildcard has me stumped

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.

Vivirtruvian is offline   Reply With Quote
Old 11-06-2019, 09:02 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Wildcard has me stumped

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)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-06-2019, 09:35 PM   #3
Vivirtruvian
Newly Registered User
 
Join Date: Jun 2017
Posts: 19
Thanks: 4
Thanked 1 Time in 1 Post
Vivirtruvian is on a distinguished road
Re: Wildcard has me stumped

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!

Vivirtruvian is offline   Reply With Quote
Old 11-06-2019, 10:03 PM   #4
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 288
Thanks: 470
Thanked 22 Times in 21 Posts
Tera is on a distinguished road
Re: Wildcard has me stumped

Quote:
Originally Posted by Vivirtruvian View Post
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 by Tera; 11-06-2019 at 10:56 PM.
Tera is offline   Reply With Quote
Old 11-06-2019, 10:39 PM   #5
Vivirtruvian
Newly Registered User
 
Join Date: Jun 2017
Posts: 19
Thanks: 4
Thanked 1 Time in 1 Post
Vivirtruvian is on a distinguished road
Re: Wildcard has me stumped

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 is offline   Reply With Quote
The Following User Says Thank You to Vivirtruvian For This Useful Post:
Tera (11-06-2019)
Old 11-06-2019, 10:57 PM   #6
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 288
Thanks: 470
Thanked 22 Times in 21 Posts
Tera is on a distinguished road
Re: Wildcard has me stumped

Quote:
Originally Posted by Vivirtruvian View Post
Hi Tera,

I will give that code a try. I have used that code but had "'*" closing the criteria.
Sorry, it was a typo. I corrected it.
Tera is offline   Reply With Quote
Old 11-06-2019, 11:17 PM   #7
Vivirtruvian
Newly Registered User
 
Join Date: Jun 2017
Posts: 19
Thanks: 4
Thanked 1 Time in 1 Post
Vivirtruvian is on a distinguished road
Re: Wildcard has me stumped

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.

Vivirtruvian is offline   Reply With Quote
Old 11-07-2019, 03:05 AM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,651
Thanks: 98
Thanked 1,499 Times in 1,414 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Wildcard has me stumped

Quote:
Originally Posted by Vivirtruvian View Post
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])
Galaxiom is offline   Reply With Quote
Old 11-07-2019, 07:10 AM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,540
Thanks: 92
Thanked 1,680 Times in 1,559 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Wildcard has me stumped

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
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-07-2019, 07:47 AM   #10
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,050
Thanks: 10
Thanked 208 Times in 197 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Wildcard has me stumped

Quote:
If your problem is always just leading zeros,
Apparently that is not the case:

Quote:
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.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Stumped--PLEASE HELP!?! omahatom Queries 2 11-08-2014 01:37 AM
Stumped on SQL jrmondonedo Reports 6 01-21-2014 11:36 PM
really stumped gbanks General 1 09-02-2003 09:03 AM
stumped and not sure where tcommis Queries 9 07-30-2003 06:12 AM
Stumped! JRB Tables 6 07-17-2002 07:00 AM




All times are GMT -8. The time now is 08:04 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World