If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple criteria from textbox
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? |
#2
|
|||
|
|||
Multiple criteria from textbox
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 "Jeff Hunt" wrote in message ... 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? |
#3
|
|||
|
|||
Multiple criteria from textbox
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" wrote in message ... 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? |
#4
|
|||
|
|||
Multiple criteria from textbox
My query had some other criteria that took some reworking, but this ended up
working for me. Thanks! "Michel Walsh" wrote: 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" wrote in message ... 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? |
#5
|
|||
|
|||
Multiple criteria from textbox
Slick!
Jeff Boyce "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|