Multiple criteria from textbox

  • Thread starter Thread starter Jeff Hunt
  • Start date Start date
J

Jeff Hunt

I’m trying to filter a query based on a text box from a form, but I would
like to be able to allow multiple options. The query that I have is as
follows:

SELECT tblRequests.FinalApprovalDate, tblRequests.Location,
tblRequests.Description
FROM tblRequests
WHERE (((tblRequests.Location) In ([forms]![frmEmails].[txtFilterLocation])));

This works when I put in a single location code. But when I try to put in
two of them, it never returns anything. I’ve tried a ton of variations with
double and single quotes, commas, OR statements, etc, but can’t get it to
work. Is there an easy way to do this?
 
I don't believe the In() clause can 'parse' the contents of that textbox. I
suspect it is trying to match the entire contents.

How are you expecting your users to use that textbox? How are you
anticipating how they might "get creative" and use it in ways you haven't
handled in your code?

Are you saying that you want to give users a way to search on (potentially)
multiple keywords? Or multiple (pre-existing) locations?

If the latter, what about the idea of creating a multi-select listbox filled
with the possible locations. Then your code could check for records in
tblRequests where the [Location] is selected in the listbox. I believe you
could do that by using a sub-SELECT statement against the recordset of
selected items...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If your text box hold data like:

"New York, London, Paris"


you can try:

WHERE ("," & forms!frmEmails!txtFIlterLocation & ",") LIKE "*[, ]" &
Location & "[, ]*"




Vanderghast, Access MVP
 
My query had some other criteria that took some reworking, but this ended up
working for me. Thanks!

Michel Walsh said:
If your text box hold data like:

"New York, London, Paris"


you can try:

WHERE ("," & forms!frmEmails!txtFIlterLocation & ",") LIKE "*[, ]" &
Location & "[, ]*"




Vanderghast, Access MVP


Jeff Hunt said:
I'm trying to filter a query based on a text box from a form, but I would
like to be able to allow multiple options. The query that I have is as
follows:

SELECT tblRequests.FinalApprovalDate, tblRequests.Location,
tblRequests.Description
FROM tblRequests
WHERE (((tblRequests.Location) In
([forms]![frmEmails].[txtFilterLocation])));

This works when I put in a single location code. But when I try to put in
two of them, it never returns anything. I've tried a ton of variations
with
double and single quotes, commas, OR statements, etc, but can't get it to
work. Is there an easy way to do this?
Click to expand...
Click to expand...
 
Slick!

Jeff Boyce

Michel Walsh said:
If your text box hold data like:

"New York, London, Paris"


you can try:

WHERE ("," & forms!frmEmails!txtFIlterLocation & ",") LIKE "*[, ]" &
Location & "[, ]*"




Vanderghast, Access MVP


Jeff Hunt said:
I'm trying to filter a query based on a text box from a form, but I would
like to be able to allow multiple options. The query that I have is as
follows:

SELECT tblRequests.FinalApprovalDate, tblRequests.Location,
tblRequests.Description
FROM tblRequests
WHERE (((tblRequests.Location) In
([forms]![frmEmails].[txtFilterLocation])));

This works when I put in a single location code. But when I try to put
in
two of them, it never returns anything. I've tried a ton of variations
with
double and single quotes, commas, OR statements, etc, but can't get it to
work. Is there an easy way to do this?
Click to expand...
Click to expand...
 
Back
Top