DLookUP

[DS]

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

 

[Ed Warren]

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]

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

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

 

[Ed Warren]

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.

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

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

 

[DS]

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.

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




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