Solved

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

Posted on 2011-09-19
3
283 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 119

Accepted Solution

by:
Rey Obrero 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now