Query using multiple values from one text box
in [Queries]
|
From: shm135 on 14 May 2010 12:00 On May 14, 10:11 am, Tom van Stiphout <tom7744.no.s...(a)cox.net> wrote: > On Fri, 14 May 2010 06:14:37 -0700 (PDT), shm135 <shmou...(a)gmail.com> > wrote: > > I'm guessing you have a query: > select * from myTable where StateName = Forms!myForm!myTextbox > You later use this query as the recordsource for some form or report. > > The reason your current solution does not work is because there is no > StateName with a value of Alaska OR Alabama, regardless of how you put > the doublequotes. > The solution is to use an IN clause: > select * from myTable where StateName in ('Alaska', 'Alabama') > (note that I use single-quotes; will come in handy below) > I have noticed in the past that this does not work in a query: > select * from myTable where StateName in (Forms!myForm!myTextbox) > > The workaround is to forget about the query and assign the > concatenated string to the recordsource property directly: > private sub form_open > if isnull(Forms!myForm!myTextbox) then > msgbox "Yo! Gimme some state(s)" > else > me.recordsource = "select * from myTable where StateName in (" & > Forms!myForm!myTextbox & ")" > end sub > Now if you put 'Alaska', 'Alabama' in the textbox it should work. > > -Tom. > Microsoft Access MVP > > > > >Hi, > > >I have a form called Test. On this form, I have a textbox where I can > >type query criteria. I would like to be able to type multiple criteria > >into this textbox and use this textbox to filter my query results. > > >For example, on my Test form textbox, I'd like to be able to type > >("Alaska" OR "Alabama") and would like to then filter my query based > >on what I type into this textbox. > > >Currently, when I type one thing into the textbox "Alaska," the query > >properly filters to all results that equal "Alaska." However, when I > >type another piece of critiera "AND Alabama" or "OR Alabama", the > >query returns no results. > > >How can I accomplish this? > > >Thanks- Hide quoted text - > > - Show quoted text - Thank you all for your responses! I think I may have accidentally marked one of your responses as SPAM- for that, I apologize! You're right Tom. This is what I am doing now and it is not working: select * from myTable where StateName in (Forms!myForm!myTextbox) To further elaborate- I have a multiselect listbox, whose AFTERUPDATE event transfers my selections to a textbox, with the word OR in between. Then, I am setting the query criteria equal to the textbox. If I make one selection, it works. More than one selection and it does not work. In the end, I want to use my selections for an UPDATE query. How can I go about this using the recordsource method you describe above. Essentially, I want to first pull out all of the records whose statename is equal to my form textbox. Then, I want to be able to update the Point of Contact field for multiple states at one time. How do I do this? Thanks!
From: shm135 on 20 May 2010 17:02 On May 14, 12:00 pm, shm135 <shmou...(a)gmail.com> wrote: > On May 14, 10:11 am, Tom van Stiphout <tom7744.no.s...(a)cox.net> wrote: > > > > > > > On Fri, 14 May 2010 06:14:37 -0700 (PDT), shm135 <shmou...(a)gmail.com> > > wrote: > > > I'm guessing you have a query: > > select * from myTable where StateName = Forms!myForm!myTextbox > > You later use this query as the recordsource for some form or report. > > > The reason your current solution does not work is because there is no > > StateName with a value of Alaska OR Alabama, regardless of how you put > > the doublequotes. > > The solution is to use an IN clause: > > select * from myTable where StateName in ('Alaska', 'Alabama') > > (note that I use single-quotes; will come in handy below) > > I have noticed in the past that this does not work in a query: > > select * from myTable where StateName in (Forms!myForm!myTextbox) > > > The workaround is to forget about the query and assign the > > concatenated string to the recordsource property directly: > > private sub form_open > > if isnull(Forms!myForm!myTextbox) then > > msgbox "Yo! Gimme some state(s)" > > else > > me.recordsource = "select * from myTable where StateName in (" & > > Forms!myForm!myTextbox & ")" > > end sub > > Now if you put 'Alaska', 'Alabama' in the textbox it should work. > > > -Tom. > > Microsoft Access MVP > > > >Hi, > > > >I have a form called Test. On this form, I have a textbox where I can > > >type query criteria. I would like to be able to type multiple criteria > > >into this textbox and use this textbox to filter my query results. > > > >For example, on my Test form textbox, I'd like to be able to type > > >("Alaska" OR "Alabama") and would like to then filter my query based > > >on what I type into this textbox. > > > >Currently, when I type one thing into the textbox "Alaska," the query > > >properly filters to all results that equal "Alaska." However, when I > > >type another piece of critiera "AND Alabama" or "OR Alabama", the > > >query returns no results. > > > >How can I accomplish this? > > > >Thanks- Hide quoted text - > > > - Show quoted text - > > Thank you all for your responses! I think I may have accidentally > marked one of your responses as SPAM- for that, I apologize! > > You're right Tom. This is what I am doing now and it is not working: > select * from myTable where StateName in (Forms!myForm!myTextbox) > > To further elaborate- I have a multiselect listbox, whose AFTERUPDATE > event transfers my selections to a textbox, with the word OR in > between. Then, I am setting the query criteria equal to the textbox. > If I make one selection, it works. More than one selection and it does > not work. In the end, I want to use my selections for an UPDATE query. > How can I go about this using the recordsource method you describe > above. > > Essentially, I want to first pull out all of the records whose > statename is equal to my form textbox. Then, I want to be able to > update the Point of Contact field for multiple states at one time. How > do I do this? > > Thanks!- Hide quoted text - > > - Show quoted text - Does anyone have any input to help? Thanks for your time.
From: PieterLinden via AccessMonster.com on 20 May 2010 17:39 shm135 wrote: >> > I'm guessing you have a query: >> > select * from myTable where StateName = Forms!myForm!myTextbox >[quoted text clipped - 66 lines] >> >> - Show quoted text - > >Does anyone have any input to help? Thanks for your time. What did you need help with? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: shm135 on 21 May 2010 16:26 On May 20, 5:39 pm, "PieterLinden via AccessMonster.com" <u49887(a)uwe> wrote: > shm135 wrote: > >> > I'm guessing you have a query: > >> > select * from myTable where StateName = Forms!myForm!myTextbox > >[quoted text clipped - 66 lines] > > >> - Show quoted text - > > >Does anyone have any input to help? Thanks for your time. > > What did you need help with? > > -- > Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1 have a multiselect listbox, whose AFTERUPDATE event transfers my selections to a textbox, with the word OR in between. Then, I am setting the query criteria equal to the textbox. If I make one selection, it works. More than one selection and it does not work. In the end, I want to use my selections for an UPDATE query. How can I go about this using the recordsource method you describe above. Essentially, I want to first pull out all of the records whose statename is equal to my form textbox. Then, I want to be able to update the Point of Contact field for multiple states at one time. How do I do this?
First
|
Prev
|
Pages: 1 2 Prev: SQL subsets Next: What does this query error message mean: Cannot open database ". |