?
Solved

Adding wildcard to criteria in a query when requesting data from form

Posted on 2011-09-19
3
Medium Priority
?
292 Views
Last Modified: 2012-05-12
I have a query that is getting its criteria from a form.
Between [Forms]![frmView]![txtPROCF] And [Forms]![frmView]![txtPROCT] Or Is Null
I need to be able to add a wilcard to the beginning of txtPROCF and PROCT in the query.  Can this be done and if yes what is the syntax?
0
Comment
Question by:Scott Palmer
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 36562138
you have to use "Like" instead of Between

Where [FieldName] Like "*" & [Forms]![frmView]![txtPROCF] And [FieldName] Like "*" & [Forms]![frmView]![txtPROCT]
0
 
LVL 11

Assisted Solution

by:datAdrenaline
datAdrenaline earned 1000 total points
ID: 36562531
You can not use a wildcard in a BETWEEN .. AND .. operator.  Also, how you can look for BETWEEN when the query process will not know what the beginning of the text will be?  Remember BETWEEN will use an alphanumeric sorting algorithm on text fields, so you have to give it the starting letters.  However, just as cap is aluding to, you need to use a LIKE operator, but you can use that in conjuction with your BETWEEN ...

WHERE ((someTextField Between [Forms]![frmView]![txtPROCF] And [Forms]![frmView]![txtPROCT])
      And (someTextField LIKE "[a-c]*"))
      Or (someTextField Is Null)

The above criteria above will return all the records with Null in someTextField or (someTextField starts with a, b, or c AND is between your parameters)
0
 

Author Closing Comment

by:Scott Palmer
ID: 36563725
Got  it to work with some manipulation.  Thanks.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

839 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