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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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."
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
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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the problem, my database was corrupt. Thanks for your suggestions, everyone.
AW