Multiple criteria from textbox

[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?

 

[Jeff Boyce]

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

 

[Michel Walsh]

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]

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

If your text box hold data like:

"New York, London, Paris"


you can try:

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




Vanderghast, Access MVP

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?

 

[Jeff Boyce]

Slick!

Jeff Boyce

If your text box hold data like:

"New York, London, Paris"


you can try:

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




Vanderghast, Access MVP

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?