Multiple Criteria 11-15-09

I have a query fed by a table where:
 "Set1" can be A, B, C, D or E.
"Set2" can be A, B, C, D or E.
"Set3" can be A, B, C, D or E.

I have a form with a list box listing A, B, C, D or E.

What I'm trying to do is open a form that lists all the records with A in 
Set1, Set2 or Set3....every record with A or B or C or D or E in any one of 
the sets...when A, B, C, D or E are selected in the list box.  

Say the user selects "B" in the list box, they then click a button which 
shows all the records with "B" in any of the sets.

Anyone have an idea how I can do that?

Thank you in advance for any help you can give me.

Gee
0
Utf
11/15/2009 2:27:01 AM
access.formscoding 7493 articles. 0 followers. Follow

7 Replies
760 Views

Similar Articles

[PageSpeed] 51

Gee wrote:

>I have a query fed by a table where:
> "Set1" can be A, B, C, D or E.
>"Set2" can be A, B, C, D or E.
>"Set3" can be A, B, C, D or E.
>
>I have a form with a list box listing A, B, C, D or E.
>
>What I'm trying to do is open a form that lists all the records with A in 
>Set1, Set2 or Set3....every record with A or B or C or D or E in any one of 
>the sets...when A, B, C, D or E are selected in the list box.  
>
>Say the user selects "B" in the list box, they then click a button which 
>shows all the records with "B" in any of the sets.


SELECT table.*
FROM table
WHERE Set1=Forms!theform.listbox
		OR Set2=Forms!theform.listbox
		OR Set3=Forms!theform.listbox

-- 
Marsh
MVP [MS Access]
0
Marshall
11/15/2009 2:51:51 AM
Thanks so much for answering, but could you be a bit more specific?  When I 
put it in my code it turns read and doesn't like the "OR".

"Marshall Barton" wrote:

> Gee wrote:
> 
> >I have a query fed by a table where:
> > "Set1" can be A, B, C, D or E.
> >"Set2" can be A, B, C, D or E.
> >"Set3" can be A, B, C, D or E.
> >
> >I have a form with a list box listing A, B, C, D or E.
> >
> >What I'm trying to do is open a form that lists all the records with A in 
> >Set1, Set2 or Set3....every record with A or B or C or D or E in any one of 
> >the sets...when A, B, C, D or E are selected in the list box.  
> >
> >Say the user selects "B" in the list box, they then click a button which 
> >shows all the records with "B" in any of the sets.
> 
> 
> SELECT table.*
> FROM table
> WHERE Set1=Forms!theform.listbox
> 		OR Set2=Forms!theform.listbox
> 		OR Set3=Forms!theform.listbox
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 
0
Utf
11/15/2009 3:36:01 AM
This is what I had been trying, but it includes only the ones with both...I 
can't figure out how to the the "OR" in there.

Private Sub Command64_Click()
On Error GoTo Err_Command64_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
 stDocName = "SearchByRequestor"
   
    stLinkCriteria = "[Set1]=" & "'" & Me![List60] & "'"    
    stLinkCriteria = "[Set2]=" & "'" & Me![List60] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command64_Click:
    Exit Sub

Err_Command64_Click:
    MsgBox Err.Description
    Resume Exit_Command64_Click
    
End Sub

"Gee" wrote:

> Thanks so much for answering, but could you be a bit more specific?  When I 
> put it in my code it turns read and doesn't like the "OR".
> 
> "Marshall Barton" wrote:
> 
> > Gee wrote:
> > 
> > >I have a query fed by a table where:
> > > "Set1" can be A, B, C, D or E.
> > >"Set2" can be A, B, C, D or E.
> > >"Set3" can be A, B, C, D or E.
> > >
> > >I have a form with a list box listing A, B, C, D or E.
> > >
> > >What I'm trying to do is open a form that lists all the records with A in 
> > >Set1, Set2 or Set3....every record with A or B or C or D or E in any one of 
> > >the sets...when A, B, C, D or E are selected in the list box.  
> > >
> > >Say the user selects "B" in the list box, they then click a button which 
> > >shows all the records with "B" in any of the sets.
> > 
> > 
> > SELECT table.*
> > FROM table
> > WHERE Set1=Forms!theform.listbox
> > 		OR Set2=Forms!theform.listbox
> > 		OR Set3=Forms!theform.listbox
> > 
> > -- 
> > Marsh
> > MVP [MS Access]
> > .
> > 
0
Utf
11/15/2009 3:40:01 AM
Gee wrote:
> I have a query fed by a table where:
> "Set1" can be A, B, C, D or E.
> "Set2" can be A, B, C, D or E.
> "Set3" can be A, B, C, D or E.
>
> I have a form with a list box listing A, B, C, D or E.
>
> What I'm trying to do is open a form that lists all the records with
> A in Set1, Set2 or Set3....every record with A or B or C or D or E in
> any one of the sets...when A, B, C, D or E are selected in the list
> box.
>
> Say the user selects "B" in the list box, they then click a button
> which shows all the records with "B" in any of the sets.
>
> Anyone have an idea how I can do that?
>
> Thank you in advance for any help you can give me.


It is almost certain that you have not normalized your table correctly 


0
Mike
11/15/2009 6:56:12 AM
    stLinkCriteria = "[Set1]=" & "'" & Me![List60] & "'" & _
        " OR [Set2]=" & "'" & Me![List60] & "'" & _
        " OR [Set3]=" & "'" & Me![List60] & "'"

-- 

        Ken Snell
http://www.accessmvp.com/KDSnell/


"Gee" <Gee@discussions.microsoft.com> wrote in message 
news:13622D7B-45CD-4D17-AFFD-99C7186346D6@microsoft.com...
> This is what I had been trying, but it includes only the ones with 
> both...I
> can't figure out how to the the "OR" in there.
>
> Private Sub Command64_Click()
> On Error GoTo Err_Command64_Click
>
>    Dim stDocName As String
>    Dim stLinkCriteria As String
> stDocName = "SearchByRequestor"
>
>    stLinkCriteria = "[Set1]=" & "'" & Me![List60] & "'"
>    stLinkCriteria = "[Set2]=" & "'" & Me![List60] & "'"
>    DoCmd.OpenForm stDocName, , , stLinkCriteria
>
> Exit_Command64_Click:
>    Exit Sub
>
> Err_Command64_Click:
>    MsgBox Err.Description
>    Resume Exit_Command64_Click
>
> End Sub
>
> "Gee" wrote:
>
>> Thanks so much for answering, but could you be a bit more specific?  When 
>> I
>> put it in my code it turns read and doesn't like the "OR".
>>
>> "Marshall Barton" wrote:
>>
>> > Gee wrote:
>> >
>> > >I have a query fed by a table where:
>> > > "Set1" can be A, B, C, D or E.
>> > >"Set2" can be A, B, C, D or E.
>> > >"Set3" can be A, B, C, D or E.
>> > >
>> > >I have a form with a list box listing A, B, C, D or E.
>> > >
>> > >What I'm trying to do is open a form that lists all the records with A 
>> > >in
>> > >Set1, Set2 or Set3....every record with A or B or C or D or E in any 
>> > >one of
>> > >the sets...when A, B, C, D or E are selected in the list box.
>> > >
>> > >Say the user selects "B" in the list box, they then click a button 
>> > >which
>> > >shows all the records with "B" in any of the sets.
>> >
>> >
>> > SELECT table.*
>> > FROM table
>> > WHERE Set1=Forms!theform.listbox
>> > OR Set2=Forms!theform.listbox
>> > OR Set3=Forms!theform.listbox
>> >
>> > -- 
>> > Marsh
>> > MVP [MS Access]
>> > .
>> > 


0
Ken
11/15/2009 3:02:57 PM
In VBA you would write it like:

    stLinkCriteria = "Set1='" & Me!List60 & _
				"' OR Set2='" & Me!List60 & _
				"' OR Set3='" & Me!List60 & "' "
	'	MsgBox stLinkCriteria
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Uncomment the MsgBox line to see that the constructed
criteria ends up looking like the SQL Where clause I posted
earlier.  Remove the MsgBox line when you are confident
about how all this works.

You should spend a minute giving your controls more
meaningful names that make you code easier to see what it is
doing.  List60 just doesn't convey anything about what the
list box is being used for.

I also want to underscore Mike's comment about your table
being un normalized,  This is an extremely important concept
in any database and you should take some time out to
understand and practice "Normalization" (Google it) so you
don't have to use kludgy code like the above.
-- 
Marsh
MVP [MS Access]


Gee wrote:
>This is what I had been trying, but it includes only the ones with both...I 
>can't figure out how to the the "OR" in there.
>
>Private Sub Command64_Click()
>On Error GoTo Err_Command64_Click
>
>    Dim stDocName As String
>    Dim stLinkCriteria As String
> stDocName = "SearchByRequestor"
>   
>    stLinkCriteria = "[Set1]=" & "'" & Me![List60] & "'"    
>    stLinkCriteria = "[Set2]=" & "'" & Me![List60] & "'"
>    DoCmd.OpenForm stDocName, , , stLinkCriteria
0
Marshall
11/15/2009 3:10:53 PM
Thank you so much!  It totally worked and I can go on with my life.
I'll look up the Normalization you guys suggested.
Thanks,
Gee

"Mike Painter" wrote:

> Gee wrote:
> > I have a query fed by a table where:
> > "Set1" can be A, B, C, D or E.
> > "Set2" can be A, B, C, D or E.
> > "Set3" can be A, B, C, D or E.
> >
> > I have a form with a list box listing A, B, C, D or E.
> >
> > What I'm trying to do is open a form that lists all the records with
> > A in Set1, Set2 or Set3....every record with A or B or C or D or E in
> > any one of the sets...when A, B, C, D or E are selected in the list
> > box.
> >
> > Say the user selects "B" in the list box, they then click a button
> > which shows all the records with "B" in any of the sets.
> >
> > Anyone have an idea how I can do that?
> >
> > Thank you in advance for any help you can give me.
> 
> 
> It is almost certain that you have not normalized your table correctly 
> 
> 
> .
> 
0
Utf
11/15/2009 8:36:01 PM
Reply:

Similar Artilces:

Cannot Open Multiple Attachments
I am using Outlook 2003. There are several .jpg files in an E-mail message that I would like to open AT THE SAME TIME. In older versions of MS Outlook, I could highlight the attachments, right click, and select OPEN. In MS Outlook 2003, I highlight the attachments, right click, and OPEN is NOT an option. How can I do this? Thanks in advance. ...

Problem sending multiple emails
I have no problem sending to 4 recipients using Microsoft Outlook 2000, however if I try to send to more then 4 I get the following error message. '452 4.5.3 too many recipients Could somebody please help with this problem as I need to send emails to a group of 30 people but at the moment I am having to send them bit by bit, my ISP provider is AOL and they say they cannot help. That's a server message...some mail server somewhere along your line is rejecting messages for having too many recipients. Outlook does not limit the number of recipients a message can have except for t...

Window Mail 12-09-09
How can I backup my Window Mails and upgrade to Window 7 and use them in Win 7? Thanks, Gary If all else fails, Get out that old pencil and paper, copy all the addresses and install them into Windows 7 "Gary" <gary.liu@star-light.com.hk> wrote in message news:86D89095-671A-4ECC-A850-989ABD0A3F18@microsoft.com... > How can I backup my Window Mails and upgrade to Window 7 and use them in > Win 7? > > Thanks, > Gary Upgrade to Windows Live Mail before you upgrade to Windows 7. http://download.live.com/wlmail=20 It will import all your ...

Double Booking Query 12-27-09
At present I am developing a database for room bookings at work. The point I am at is to put in a query to check for double bookings. So far this is fine, if a room does not overlap on the time, it says there isn't a clash, and if there is an overlap, it says there is an overlap. But, what I'd like is for the query to return an overlap if two bookings are made within 30 minutes of each other. Example, if a meeting in room 1 ends at 13:00 and another starts at 13:15 in the same room, an overlap would be returned. This is needed as sometimes the layouts of rooms need adjusting and ...

burning cd wind media player 11
I create original wav files. Most all burn just fine, but some say media player cant tell the length so won't burn on cd. I check the file, in music, properties, and the length is there, but when added to the library of player, the length is not on the list. 1. is there a way to edit in the length on the player. The only thing error icon suggests is to play the content, and maybe player will detect. I have done that. Since it is 36 minutes long, I cant do much of that. Any help will be appreciated. The file plays in media player when I click on it, just fine. I have ...

intel wireless disconnects every 15 minutes XP
i am using XP & have been using the same wireless internet connection at home in france for 12 months. there was a problem recently with activ synch (what is active sync????!!!) has this got something to do with the fact that my wifi disconnects from the internet every 15 minutes? mine is the only preferred wireless connection by the way so that is not the problem it is set to automatic by the way but i have to reconnect manually every 15 minutes nonetheless i have the same problem whether windows firewall is on or not is there a new setting with windows ? has activ sync got something...

Mac Stuff CDs, A to Z, updated 15/Jan/2006, and Win & Mac programs, 'WinMac', 'PC/MaC', 'Win-Mac', 'Multi', 'Multi-Platform', 'MultiFormat', 'MULTIOS', 'HYBRID'
Mac Stuff CDs, A to Z, updated 15/Jan/2006, and Win & Mac programs, 'WinMac', 'PC/MaC', 'Win-Mac', 'Multi', 'Multi-Platform', 'MultiFormat', 'MULTIOS', 'HYBRID' GARAGE SALES, buy, risk-free purchase, working, tested, fully functional, very cheap discounted price, low cost, quality OEM software, -------------------------------------------------------- Abvent Artlantis v4.5 Mac 1CD Abvent.PhotoCAD.v1.0.MacOSX ACD.Systems.Canvas.X.0.2.925.MacOSX Adobe.Acrobat.7.0.Pro.for.Mac.OS 1CD Adobe Acrobat 6.0.0 Professional Mac OSX 1CD ...

Multiple sounds on a slide
Hi I am trying to insert two different sounds on the same slide. They are both 'embeded' no 'inserted' into my presentation. Basically, one sound plays, until it is time for the next to start playing, which is when the second sound starts and the first just cuts off. I am usong PowerPoint 2007. Can anyone help? (I don't care if the two sounds start together or if there is a staggerred start) "SomeRandomPerson" <SomeRandomPerson@discussions.microsoft.com> wrote in message news:72364EBB-40E9-41F9-B40B-D3C2606CDD07@microsoft.com... > Hi >...

Network vista to xp 11-29-09
Not sure how to explain this but will try. I have two pc's with xp networked to my vista pc, they both appear in the network window and also my vista pc appears, if neither are xp pc's are on then nothing appears. I've just networked a XP netbook to my vista pc as well and that won't appear unless one of the other XP pc's is on? Now the funny thing is once it had appeared I made a shotcut for netbook and even though nothing appears in the network window I can access it via the shortcut. Any help would be much appreciated. Thanks Eddy ...

catch multiple exceptions
instead of try { // <operation> } catch( CMemoryException* e) { trace("%s", e->GetErrorMessage()); e->Delete(); closefile(); return FALSE; } catch( CDBException* e) { trace("%s", e->GetErrorMessage()); e->Delete(); closefile(); return FALSE; } catch(...) { return FALSE; } is there another way so I don't have to repeat the same block of error report and cleanup that is almost entirely similar to all the other catch blocks? check out either the CATCH_ALL macro if you want to catch all possible exceptions at on...

Multiple Joins in DataAdapters
I have a second question related to my previous post subject: Mapping Nested XML to Relational Tables The XML document which loads into the dataset must then be stored in relational tables and recalled later. As mentioned in my previous posting, the document is fairly complex and requires over 20 tables to store the dataset record. Is it necessary to create 20 DataAdapters to store and recall the document, or can it be done with one DataAdapter containing multiple joins? Each XML document represents one record in the dataset and records must be added, recalled, and sometimes deleted from the...

Matching data in multiple columns
Hopefully the experts can help on this one... In one worksheet("payout") in have riders names (Column B) and horse names (Column C). In another worksheet ("teams") I have riders names (Col B) and horse names(Col C) and in Col D I have times In yet another worksheet (team payout) I have riders name(Col B) horse name(col C0 and times in Col D. The problem I'm having is this: Let's say Bill ( a rider) is riding 2 horses (Wells and Fargo). Anything I've used to compare the values in Cols B and C will only capture the first occurance of Bill and place t...

EARN $ 1,434,892.8 BY E-GOLD (GUARANTEED!!) All you need is 3 People You know #11
EARN $ 1,434,892.8 BY E-GOLD (GUARANTEED!!) All you need is 3 People You know Hello every body , im be glad to introduce one of the best MLM for every body who wants to explod his/her earning with very simple steps and incredible situation! JUST IMAGINE...... If you achieve only 10% success you will still make $ 143,489.28 If you achieve only 1% success you will still make $ 14,348.928 But If we trust to each other and be serious, reaching to $ 1,434,892.8 is not seem so far,just three or four weeks!! The whole works that you should do : 1) You should open an e-gold account ,th...

Is there a macro for returning multiple values through a Vlookup?
Foe eg: Sheet2 contains: Project# Phase Sp# Details status Manager ... 1234 1-0110 N80 abc Test am 1234 1-0210 N97 def Incom bd 1234 2-0210 N45 lmn Test cf 1234 1-0110 N67 jkl Com er Sheet1 contains: Project# Phase SP# Status Manager 1234 1-0110 The above 2 values are inserted by the user: Now: On running the macro: the sheet 1 should be populated with all the records for phase 1-0110 Desired output: Project# Phase Sp# Status Manager 1234 1-0110 N80 Test am ...

Recovery Storage Groups and Brightstore 11
Has anyone done a mailbox recovery using RSG and Brightstore 11 as the backup software? A caveat, we are not backing up brick level. ...

Rollup multiple records into one record
Hi, Gang, I'm having a dillema that I probably should be able to figure out on my own, but I'm having issues. I have multiple records which are laid out such as this: Acct ProductName 123 AB1 123 BA1 123 CD1 I want to roll those multiple records into one record that might look something like this: Acct AB1 BA1 CD1 123 Yes Yes Yes For some reason, this is escaping me. Thanks! "kidkosmo" <kidkosmo2@yahoo.com> wrote in message news:a122a9d...

help #11
Protocol: SMTP, Port: 25, Secure(SSL): No, Error Number: 0x800CCC0F ...

multiple users
Exchange 5.5 Outlook 2000 We would like several users to be able to see the inbox of one mailbox. Is it possible to configure Outlook to read more than one mailbox ? Jerry, Please see if the following articles help: 195781 OL2000: (CW) How to Open Someone Else's Calendar or Other Folder http://support.microsoft.com/?id=195781 196996 OL2000: (CW) How to Manage Multiple Exchange Mailbox Accounts http://support.microsoft.com/?id=196996 Thanks, -- Greg Mansius [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights "Jerry Fortenberry&quo...

Outlook 2000 attaching multiple signatures
Has anyone see Outlook 2000 SP3 inserting addtional signatures when attaching documents? Thanks in advance for any advice. Nickie ...

Exchange 2003
Hello to everybody ! It's the first message I post to a MS NG...I'm a bit excited :-). I have a problem and I didn't find anything to resolve it. I have a Win SBS 2003 standard server, italian version, where it is loaded also SQL 2000 SP4, Zetafax 9.1, WSUS. It's an IBM X236, 2 Xeon processor 3.0Mhz, 2 GB ram, 5 HD of 73 Gb, configured in a RAID 5EE volume with 2 partition C and D (for the datas). The server is configured mainly as a file server and we're trying to start to use the exchange server for the internal mail and external mail. Until now we're using 5 ...

Office 2003 and Windows 7 12-27-09
Can anyone tell me if Office 2003 will work with Windows 7 64bit ? Thanks ...

Printing on 11 x 17
Trying to print with Pub 2003 on 11 x 17 to HP BI 2600. After I send job to print, the message "Tabloid size not loaded in tray Tray 2". All references to page size in program and print driver are set to 11 x 17. Please help. After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Dennis Coons <dennis.coons@webco-inc.com>... > Trying to print with Pub 2003 on 11 x 17 to HP BI 2600. > After I send job to print, the message "Tabloid size not > loaded in tray Tray 2". All references to page size in > program and print driver are se...

Restrict multiple distribution groups...
Does anyone know of a way to do a mass distribution group edit to restrict who can send to them? I've tried ADModify.net with no success as the restriction section is not included. BTW - we're running E2k3 Ent. TIA, This is something that is scriptable, but it isn't easy. Possibly http://www.joeware.net might have something that could help you with this. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "kage13" <kage13@discussions.microsoft.com> wrote in message news:42A23351-E46C-481A-AA97-7A4CF6C53E65@microsoft....

Using login ID as criteria in query
I am trying to restrict the data that different users can see by capturing the login ID and using it as criteria in a query, but I can't find anything on this topic. Can anyone tell me if this is possible and how to do it? Sure. You mean the Windows login? see http://www.mvps.org/access/api/api0008.htm Else, if you use a secured database, you can relay on the CurrentUser property. For database not specifically secured, the result is 'Admin' : ? Application.CurrentUser Admin so, under a secured network, the Windows login is probably more reliable, in such cases. Vandergha...

One cell takes multiple inputs
I was wondering if there is a simple way to make one cell takes multiple inputs, essentially turning a cell into a placeholder. Example: Sheet1!A2=Sheet1!A3+5 Sheet1!A3 will take arbitrary value from Sheet2!A3 or Sheet3!A3, and return the answer to Sheet2!A4 and Sheet3!A4, respectively. If Sheet2!A3=2, then Sheet2!A4=Sheet1!A2=7 If Sheet3!A3=4, then Sheet3!A4=Sheet1!A2=9 Thank You. csw -- csw78 ------------------------------------------------------------------------ csw78's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23008 View this threa...