• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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

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?
Scott Palmer
Scott Palmer
2 Solutions
Rey Obrero (Capricorn1)Commented:
you have to use "Like" instead of Between

Where [FieldName] Like "*" & [Forms]![frmView]![txtPROCF] And [FieldName] Like "*" & [Forms]![frmView]![txtPROCT]
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)
Scott PalmerData AnalystAuthor Commented:
Got  it to work with some manipulation.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now