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
|
|||
|
|||
DLookUP
I need to run a DlookUp to search for records and then when it finds
them I want to exclude these records from a rowsource. How would I do this? I've got this so far NZ(DLookUp("MenuCatID","MenuInfo",[MenuID]=Forms!MenuMaker!MenuID),0) This gives me the records that I want to Exclude. I'm running a list Box with SQl behind it Can I use a DLookUp within an SQL Statement? Any help appreciated. Thanks DS |
#2
|
|||
|
|||
DLookUP
The way I would do this is to build a query: qry1 SELECT MenuCatID FROM MenuInfo WHERE MenuID=Forms!MenuMaker!MenuID This will give me a 'view' of my data with the matching MenuId's Then qry 2 (actually the easy way is to build a new (unmatched query) using the wizard, but it should give something like the following) SELECT MenuInfo.* FROM qry1 RIGHT JOIN MenuInfo ON qry1.[MenuCatID] = MenuInfo.MenuCatID WHERE qr1.MenuCatID = Null; Now you have a set of rows in MenuInfo not included in qry1; Ed Warren "DS" wrote in message ... I need to run a DlookUp to search for records and then when it finds them I want to exclude these records from a rowsource. How would I do this? I've got this so far NZ(DLookUp("MenuCatID","MenuInfo",[MenuID]=Forms!MenuMaker!MenuID),0) This gives me the records that I want to Exclude. I'm running a list Box with SQl behind it Can I use a DLookUp within an SQL Statement? Any help appreciated. Thanks DS |
#3
|
|||
|
|||
DLookUP
Ed Warren wrote:
The way I would do this is to build a query: qry1 SELECT MenuCatID FROM MenuInfo WHERE MenuID=Forms!MenuMaker!MenuID This will give me a 'view' of my data with the matching MenuId's Then qry 2 (actually the easy way is to build a new (unmatched query) using the wizard, but it should give something like the following) SELECT MenuInfo.* FROM qry1 RIGHT JOIN MenuInfo ON qry1.[MenuCatID] = MenuInfo.MenuCatID WHERE qr1.MenuCatID = Null; Now you have a set of rows in MenuInfo not included in qry1; Ed Warren "DS" wrote in message ... I need to run a DlookUp to search for records and then when it finds them I want to exclude these records from a rowsource. How would I do this? I've got this so far NZ(DLookUp("MenuCatID","MenuInfo",[MenuID]=Forms!MenuMaker!MenuID),0) This gives me the records that I want to Exclude. I'm running a list Box with SQl behind it Can I use a DLookUp within an SQL Statement? Any help appreciated. Thanks DS Ok Ed, That worked great! Only One Question is there a way of doing this without storing a Query? Heres the first Query's SQL SELECT MenuInfo.MenuID, MenuInfo.MenuCatID FROM MenuInfo WHERE (((MenuInfo.MenuID)=[Forms]![MenuMaker]![MenuID])); Heres the Second Querys SQL SELECT Query1.MenuID, MenuCats.MenuCatID, MenuCats.MenuCat FROM Query1 RIGHT JOIN MenuCats ON Query1.MenuCatID = MenuCats.MenuCatID WHERE (((Query1.MenuID) Is Null)) ORDER BY MenuCats.MenuCat; Once again Thank You, This kept me going almost al night. DS |
#4
|
|||
|
|||
DLookUP
Why not save the query?
For me it's easier to do maintenance on queries than code. I'm sure there is a way in SQL that one can 'cascade' the queries into one query and/or you can put it in vba code as a 'temp' querydef. Sorry but you have to be very specific about what you really want to do, and someone with that expertise (not me ) will have to answer. Ed Warren. "DS" wrote in message ... Ed Warren wrote: The way I would do this is to build a query: qry1 SELECT MenuCatID FROM MenuInfo WHERE MenuID=Forms!MenuMaker!MenuID This will give me a 'view' of my data with the matching MenuId's Then qry 2 (actually the easy way is to build a new (unmatched query) using the wizard, but it should give something like the following) SELECT MenuInfo.* FROM qry1 RIGHT JOIN MenuInfo ON qry1.[MenuCatID] = MenuInfo.MenuCatID WHERE qr1.MenuCatID = Null; Now you have a set of rows in MenuInfo not included in qry1; Ed Warren "DS" wrote in message ... I need to run a DlookUp to search for records and then when it finds them I want to exclude these records from a rowsource. How would I do this? I've got this so far NZ(DLookUp("MenuCatID","MenuInfo",[MenuID]=Forms!MenuMaker!MenuID),0) This gives me the records that I want to Exclude. I'm running a list Box with SQl behind it Can I use a DLookUp within an SQL Statement? Any help appreciated. Thanks DS Ok Ed, That worked great! Only One Question is there a way of doing this without storing a Query? Heres the first Query's SQL SELECT MenuInfo.MenuID, MenuInfo.MenuCatID FROM MenuInfo WHERE (((MenuInfo.MenuID)=[Forms]![MenuMaker]![MenuID])); Heres the Second Querys SQL SELECT Query1.MenuID, MenuCats.MenuCatID, MenuCats.MenuCat FROM Query1 RIGHT JOIN MenuCats ON Query1.MenuCatID = MenuCats.MenuCatID WHERE (((Query1.MenuID) Is Null)) ORDER BY MenuCats.MenuCat; Once again Thank You, This kept me going almost al night. DS |
#5
|
|||
|
|||
DLookUP
Ed Warren wrote:
Why not save the query? For me it's easier to do maintenance on queries than code. I'm sure there is a way in SQL that one can 'cascade' the queries into one query and/or you can put it in vba code as a 'temp' querydef. Sorry but you have to be very specific about what you really want to do, and someone with that expertise (not me ) will have to answer. Ed Warren. "DS" wrote in message ... Ed Warren wrote: The way I would do this is to build a query: qry1 SELECT MenuCatID FROM MenuInfo WHERE MenuID=Forms!MenuMaker!MenuID This will give me a 'view' of my data with the matching MenuId's Then qry 2 (actually the easy way is to build a new (unmatched query) using the wizard, but it should give something like the following) SELECT MenuInfo.* FROM qry1 RIGHT JOIN MenuInfo ON qry1.[MenuCatID] = MenuInfo.MenuCatID WHERE qr1.MenuCatID = Null; Now you have a set of rows in MenuInfo not included in qry1; Ed Warren "DS" wrote in message ... I need to run a DlookUp to search for records and then when it finds them I want to exclude these records from a rowsource. How would I do this? I've got this so far NZ(DLookUp("MenuCatID","MenuInfo",[MenuID]=Forms!MenuMaker!MenuID),0) This gives me the records that I want to Exclude. I'm running a list Box with SQl behind it Can I use a DLookUp within an SQL Statement? Any help appreciated. Thanks DS Ok Ed, That worked great! Only One Question is there a way of doing this without storing a Query? Heres the first Query's SQL SELECT MenuInfo.MenuID, MenuInfo.MenuCatID FROM MenuInfo WHERE (((MenuInfo.MenuID)=[Forms]![MenuMaker]![MenuID])); Heres the Second Querys SQL SELECT Query1.MenuID, MenuCats.MenuCatID, MenuCats.MenuCat FROM Query1 RIGHT JOIN MenuCats ON Query1.MenuCatID = MenuCats.MenuCatID WHERE (((Query1.MenuID) Is Null)) ORDER BY MenuCats.MenuCat; Once again Thank You, This kept me going almost al night. DS I'm trying to go Queryless for one reason or another! Once agai Thank you for your help, it is much appreciated. DS |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I establish two criterias in DLookup function | AndyEduardo | Running & Setting Up Queries | 2 | November 30th, 2005 07:20 PM |
dlookup or not dlookup... | lula | General Discussion | 5 | November 24th, 2005 07:24 AM |
dlookup documentation? | Fredrated | New Users | 1 | May 19th, 2005 11:10 PM |
DLookup and Conversion in Report | Tom | Setting Up & Running Reports | 1 | April 2nd, 2005 05:21 AM |
HELP! I'm really struggling to understand DLookup! | Tony Williams | General Discussion | 0 | February 17th, 2005 01:28 PM |