Link to home
Start Free TrialLog in
Avatar of jcreswell
jcreswell

asked on

Autonumber Wildcard

Is there a character that can be used as a wildcard when running a query on an AutoNumber field?  I can't seem to make it not throw an error with any of the normal characters.  Am I missing something - because I don't see any special note about it on Microsoft's site.
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

what are you trying to achieve?  What is the nature of the query that you are trying to execute, that you need a 'wildcard' for an Autonumber field?

AW
Avatar of jcreswell
jcreswell

ASKER

The autonumber prints quite large on a confirmation slip that is used as a parking pass.  I want to enable the users to, if they only catch a portion of the autonumber, or cannot remember the entire thing if looking it up later, they can enter what they do remember, with a wildcard to find the matches.

For example, if the confirmation slip reads 1234, but they only can remember 123x, then the user can type in 123* or whatever might work as a wildcard to pull up all the 123x registrations to find the one they are looking for.
if you already have a search function that looks through the autonumber values, then you can add code that includes the wildcard (*)

for example (using the variable Confirmation):

     ' If Confirmation
    If Nz(Me.Confirmation) <> "" Then
        strWhere = strWhere & " AND " & "tablename.fieldname Like '*" & Me.Confirmation & "*'"
    End If
     
you would replace "tablename.fieldname" with the actual table and field that contains the autonumber field to search
this should search the autonumber field and find a partial value

let me know if you need more detail

SOLUTION
Avatar of Rick_Rickards
Rick_Rickards
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Using either ? or # produces an error message in the query builder.  

For #, Access says "The expression you entered has an invalid date value."  

For ?, it says "The expression you entered contains invalid syntax.  You may have entered and operand without an operator."

For [], it says "The expression you entered contains invalid syntax.  You may have entered and operand without an operator."

For *, it says "The expression you entered contains invalid syntax.  You may have entered and operand without an operator."
if i understand your question, you are not looking for vba or sql code, you want the syntax to place in query builder under query design

for this, you can add a statement to the "criteria" row for whatever field you want to search in query design

the format of the statement would be:

Like [Enter Value] & "*" Or Like "*" & [Enter Value] & "*"

this will popup a message box stating "Enter Value" - enter a partial or full value (e.g. 12 would return 12, 123, 1234, etc).  the more you enter, the more restrictions will be placed on the results returned

let me know if you need more detail on this or if you continue to get syntax errors


I have not been able to get any of these wildcards to work when searching an autonumber field.  Each returns an error in both the query builder and in ASP VBScript.  I am able to replicate this problem always, on all computers.  I just create a table, make one field a keyfield which is an autonumber data type, and try to query that field with a wildcard.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I found the problem, my database was corrupt.  Thanks for your suggestions, everyone.