Solved

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

Posted on 2011-09-19
3
285 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:ScootterP
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 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 250 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:ScootterP
ID: 36563725
Got  it to work with some manipulation.  Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access VBA Printing current Page as pdf 3 56
VBA modules import 4 57
IIF in access query 19 25
Calculated field not displaying as currency on form 4 20
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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