Querying on multiple criteria from a text box

  • Thread starter Thread starter Krisa Warnock
  • Start date Start date
K

Krisa Warnock

Help. I have a form that I am using to query my table. I
have text boxes that have specific search criteria, but my
question is how do I search on multiple instances of that
criteria within the same text box? For instance, If I
have a text box that searches on test numbers - say I
enter 10 and it pulls all tests that contain 10, how can I
put in say 10 and 15, or 10; 15 and it pull tests with
either 10 or 15 in the name?? Huge problem - would be
great breakthrough if you could help!!
 
Dear Krisa:

There's a good way to do this, and an easy way to do it.

The good way is to write VBA code to create the query string and run that.
You might want to change the text box to be a multi-select list box of all
the possible values.

The easy way is to use the comma delimiting of your text box. In the query,
append a comma to the front and to the end of the string. Then search for
comma, number, comma in the string, like this:

WHERE INSTR(',' & CStr([NumberField]) & ',', ',' &
[Forms]![YourFormName]![YourTextBox] & ',') <> 0

For this to work, the user typing into the text box must be very careful.
He must not put spaces after the commas in the list, for example. But it
CAN work. However, the list box is better because the user need only see
and select the values he wants. This takes away the possibility he will
make a mistake entering a number, use a semicolon instead of a comma, and
all the other things users do all the time.
 
I entered your suggestion as so into my query box - it
says there is a syntax error. Can you help?

WHERE INSTR(',' & CStr([Table1.Event]) & ',', ',' & Forms!
-----Original Message-----
Dear Krisa:

There's a good way to do this, and an easy way to do it.

The good way is to write VBA code to create the query string and run that.
You might want to change the text box to be a multi- select list box of all
the possible values.

The easy way is to use the comma delimiting of your text box. In the query,
append a comma to the front and to the end of the string. Then search for
comma, number, comma in the string, like this:

WHERE INSTR(',' & CStr([NumberField]) & ',', ',' &
[Forms]![YourFormName]![YourTextBox] & ',') <> 0

For this to work, the user typing into the text box must be very careful.
He must not put spaces after the commas in the list, for example. But it
CAN work. However, the list box is better because the user need only see
and select the values he wants. This takes away the possibility he will
make a mistake entering a number, use a semicolon instead of a comma, and
all the other things users do all the time.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Help. I have a form that I am using to query my table. I
have text boxes that have specific search criteria, but my
question is how do I search on multiple instances of that
criteria within the same text box? For instance, If I
have a text box that searches on test numbers - say I
enter 10 and it pulls all tests that contain 10, how can I
put in say 10 and 15, or 10; 15 and it pull tests with
either 10 or 15 in the name?? Huge problem - would be
great breakthrough if you could help!!
Click to expand...


.
Click to expand...
 
This may depend on the context of where you are placing this. I'm not sure
what you mean by "query box".

If you're putting this into the Query Design Grid as a criteria, then omit
the word WHERE. Instead, do something like:

Field: CalculateMe: INSTR(',' & CStr([Table1.Event]) & ',', ',' &
Forms![Search ATF Database]![Event] & ',')
Criteria: <> 0
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Krisa WArnock said:
I entered your suggestion as so into my query box - it
says there is a syntax error. Can you help?

WHERE INSTR(',' & CStr([Table1.Event]) & ',', ',' & Forms!
-----Original Message-----
Dear Krisa:

There's a good way to do this, and an easy way to do it.

The good way is to write VBA code to create the query string and run that.
You might want to change the text box to be a multi- select list box of all
the possible values.

The easy way is to use the comma delimiting of your text box. In the query,
append a comma to the front and to the end of the string. Then search for
comma, number, comma in the string, like this:

WHERE INSTR(',' & CStr([NumberField]) & ',', ',' &
[Forms]![YourFormName]![YourTextBox] & ',') <> 0

For this to work, the user typing into the text box must be very careful.
He must not put spaces after the commas in the list, for example. But it
CAN work. However, the list box is better because the user need only see
and select the values he wants. This takes away the possibility he will
make a mistake entering a number, use a semicolon instead of a comma, and
all the other things users do all the time.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Help. I have a form that I am using to query my table. I
have text boxes that have specific search criteria, but my
question is how do I search on multiple instances of that
criteria within the same text box? For instance, If I
have a text box that searches on test numbers - say I
enter 10 and it pulls all tests that contain 10, how can I
put in say 10 and 15, or 10; 15 and it pull tests with
either 10 or 15 in the name?? Huge problem - would be
great breakthrough if you could help!!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top