Criteria with wild cards AND variables

[John Smith]

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******@remove thispart.hotmai l.com

[Phil Stanton]

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*****@micros oft.com> wrote in message
news:c7******** **@daisy.noc.uc la.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******@remove thispart.hotmai l.com

[John Smith]

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********@sta ntonfamily.co.u k> wrote in message
news:40******** *************** @mercury.nildra m.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