By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,903 Members | 2,075 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,903 IT Pros & Developers. It's quick & easy.

Criteria with wild cards AND variables

P: n/a
This is what I'm trying to do:

In a form, a user enters a value (e.g. John) and clicks on submit.

Submit opens up a query with one of its values (e.g. Name) set to the value
of the control that the user just typed "John" into.

Thus far, this works fine, and it pulls up all the records where Name =
John.

But I want it to pull up all records where Name = John, John Smith, John
Jones, John Jacobs, Father John, etc. -- in other words, if John is in any
part of the control, I want it to come back.

If I put Like "*John*" in the criteria, it works fine.

But if I put Like "[Forms]![Submit Form]![Name]*" it returns no records
(because it looks for names with the value "[Forms]!...")

If I put Like [Forms]![Submit Form]![Name]* or Like [Forms]![Submit
Form]![Name]"*" it returns with a syntax error.

Obviously I won't always be looking for John, so I would like to do this
with a variable.

Any idea how to do what I want to do? Thanks!
ph******@removethispart.hotmail.com
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Try something like

Like "*" & Left$([forms]![Submit Form]![Name],Len([forms]![Submit
Form]![Name] & "*"

The Len(....) part defines how many letters you want to search on (in the
case of John you will look for at least 4 letters)

HTH

Phil
"John Smith" <so*****@microsoft.com> wrote in message
news:c7**********@daisy.noc.ucla.edu...
This is what I'm trying to do:

In a form, a user enters a value (e.g. John) and clicks on submit.

Submit opens up a query with one of its values (e.g. Name) set to the value of the control that the user just typed "John" into.

Thus far, this works fine, and it pulls up all the records where Name =
John.

But I want it to pull up all records where Name = John, John Smith, John
Jones, John Jacobs, Father John, etc. -- in other words, if John is in any
part of the control, I want it to come back.

If I put Like "*John*" in the criteria, it works fine.

But if I put Like "[Forms]![Submit Form]![Name]*" it returns no records
(because it looks for names with the value "[Forms]!...")

If I put Like [Forms]![Submit Form]![Name]* or Like [Forms]![Submit
Form]![Name]"*" it returns with a syntax error.

Obviously I won't always be looking for John, so I would like to do this
with a variable.

Any idea how to do what I want to do? Thanks!
ph******@removethispart.hotmail.com

Nov 12 '05 #2

P: n/a
Thanks! I'm not sure why I would use Left or Len, so instead I just did

Like "*" & [Forms]![Submit Form]![Name] & "*"

Which worked like a charm. Thanks for the help!

John

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message
news:40***********************@mercury.nildram.net ...
Try something like

Like "*" & Left$([forms]![Submit Form]![Name],Len([forms]![Submit
Form]![Name] & "*"

The Len(....) part defines how many letters you want to search on (in the
case of John you will look for at least 4 letters)

HTH

Phil

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.