?
Solved

Autonumber Wildcard

Posted on 2006-05-03
9
Medium Priority
?
506 Views
Last Modified: 2010-11-02
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.
0
Comment
Question by:jcreswell
9 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16602539
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
0
 
LVL 1

Author Comment

by:jcreswell
ID: 16602817
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.
0
 
LVL 3

Expert Comment

by:dtripp7
ID: 16603374
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

0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 16

Assisted Solution

by:Rick_Rickards
Rick_Rickards earned 600 total points
ID: 16603505
The * character can be used as a wild card to represent 1 or more characters.
The ? character can be used as a wild card to represent any single character.
The # character can be used as a wild card to represent any numnber.
The ! character can be used to identify something you don't want (a NOT operator)
The [] Brackets characters can be used to identify a range of something you want or if combined with the ! character a range of something you don't want...


Since you're dealing with an auto number field the ? and # character would serve the same purpose even though they technically do different things...

For example...

SELECT MyKey
FROM tblMyTable
WHERE MyKey Like "123*";

would return all numbers that started with 123 and ended with anything (including nothing) after 123.

SELECT MyKey
FROM tblMyTable
WHERE MyKey Like "*123";

would return all numbers that ended with 123 and started with anything (including nothing) before 123.

SELECT MyKey
FROM tblMyTable
WHERE MyKey Like "12*3";

would return all numbers starting with 12 followed by anything (including nothing) after the 12 and ending as long as the number ended with a 3.

SELECT MyKey
FROM tblMyTable
WHERE MyKey Like "12?3";

would return all 4 digit numbers where the first two numbers were 12, and the 4th number was a 3 but the 3rd number could be anything (although in this case it would have to be some number.

There are a great many ways you can spin wild cards too.  For example...


SELECT MyKey
FROM tblMyTable
WHERE MyKey Like "!1*";

would return all numbers that do not start with 1

SELECT MyKey
FROM tblMyTable
WHERE MyKey Like "![1-3]*";

would return all numbers that do NOT start with 1 thru 3 (includes 2)

SELECT MyKey
FROM tblMyTable
WHERE MyKey Like "[1-3]*";

would return all numbers that start with 1 thru 3...


SELECT MyKey
FROM tblMyTable
WHERE MyKey Like "6[7-9]##!5";

would return all numbers where the first digit is a 6, the second digit is a number falling in the range 7 thru 9 and requires 2 additional digits (numbers) that could be anything and then last but not least requires a 5th character (digit in this case) that is not 5 but can be anything else.

Well I could go on and on but hopefully this gives you the general idea and plenty to get started with.

Rick





0
 
LVL 1

Author Comment

by:jcreswell
ID: 16604836
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."
0
 
LVL 3

Expert Comment

by:dtripp7
ID: 16610023
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


0
 
LVL 1

Author Comment

by:jcreswell
ID: 16645786
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.
0
 
LVL 3

Accepted Solution

by:
dtripp7 earned 600 total points
ID: 16649694
Here is what I did to get the wildcard syntax to work.  

1.  I created a table called Table1 with two fiields:  ID (autonumber) Value (text)
2.  Added 20 values to Table1 which created autonumbers from 1 to 20
3.  Created a query called Query1 which includes Table1, all fields (ID, Value)
4.  Added the formula below to the criteria row of Query1 under the ID field (the autonumber field)

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

5. Saved and opened the query and entered a value in the "Enter Value" pop-up

If I enter 1 -  I get any autonumber field value that includes 1 (e.g. 1, 12, 13, etc), if I enter 2, I get
anything ID with a 2, etc

If this still does not work, let me know what error you receive

0
 
LVL 1

Author Comment

by:jcreswell
ID: 16720440
I found the problem, my database was corrupt.  Thanks for your suggestions, everyone.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question