SQL SubQuery

[DS]

I tried to build an SQL statement from 2 Queries. I keep getting a
Syntax Error. Any help appreciated!!
Thanks
DS

SELECT MenuCats.MenuCatID, MenuCats.MenuCat, MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=(SELECT MenuInfo.MenuID, MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat

Query 1
SELECT MenuInfo.MenuID, MenuInfo.MenuCatID
FROM MenuInfo
WHERE (((MenuInfo.MenuID)=[Forms]![MenuMaker]![MenuID]));

Query 2
SELECT PreSectionQ.MenuID, MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM PreSectionQ RIGHT JOIN MenuCats ON PreSectionQ.MenuCatID =
MenuCats.MenuCatID
WHERE (((PreSectionQ.MenuID) Is Null) AND ((MenuCats.Active)=-1))
ORDER BY MenuCats.MenuCat;

 

[Duane Hookom]

First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;

 

[DS]

First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;

This seems like it should be right but it's not returning any
records...No Error codes but also no records.
Thanks
DS

 

[Duane Hookom]

What have you tried to do in troubleshooting? Have you created a query of
just your subquery to see what value/records are returned?

--
Duane Hookom
MS Access MVP
--

First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;

This seems like it should be right but it's not returning any records...No
Error codes but also no records.
Thanks
DS

 

[Randy Harris]

What have you tried to do in troubleshooting? Have you created a query of
just your subquery to see what value/records are returned?

--
Duane Hookom
MS Access MVP
--

First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;

This seems like it should be right but it's not returning any records...No
Error codes but also no records.
Thanks
DS

Duane, is this not one of those cases where he should be using IN in the
WHERE clause rather than =?

And MenuCats.MenuCatID In
(Select ...

Randy

 

[John Spencer]

I suspect that your criteria in the subquery is causing the problem.


WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID])

IsNull is going to return -1 or Zero

So SQL will see
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=0)

Or

(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=-1)

My guess is that neither of those is a valid value for MenuInfo.MenuId. Are you
trying to use the NZ function?

First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;

This seems like it should be right but it's not returning any
records...No Error codes but also no records.
Thanks
DS

 

[Duane Hookom]

Good catch.

--
Duane Hookom
MS Access MVP
--

I suspect that your criteria in the subquery is causing the problem.


WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID])

IsNull is going to return -1 or Zero

So SQL will see
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=0)

Or

(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=-1)

My guess is that neither of those is a valid value for MenuInfo.MenuId.
Are you
trying to use the NZ function?

First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;

This seems like it should be right but it's not returning any
records...No Error codes but also no records.
Thanks
DS

 

[DS]

I suspect that your criteria in the subquery is causing the problem.


WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID])

IsNull is going to return -1 or Zero

So SQL will see
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=0)

Or

(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=-1)

My guess is that neither of those is a valid value for MenuInfo.MenuId. Are you
trying to use the NZ function?

Duane Hookom wrote:

First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;

This seems like it should be right but it's not returning any
records...No Error codes but also no records.
Thanks
DS

The problem is that I need the records where MenuID is Null...you are
right the MenuID field will be 1 or Greater but never 0 or -1.
Thanks
DS

 

[DS]

What have you tried to do in troubleshooting? Have you created a query of
just your subquery to see what value/records are returned?

--
Duane Hookom
MS Access MVP
--

Duane Hookom wrote:


First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;


This seems like it should be right but it's not returning any

records...No

Error codes but also no records.
Thanks
DS

Duane, is this not one of those cases where he should be using IN in the
WHERE clause rather than =?

And MenuCats.MenuCatID In
(Select ...

Randy

Whenever I create a Query with Query1 and then place that into Query2 I
do get records returned.
Thanks
DS

 

[John Spencer]

Perhaps the following criteria will work for you

WHERE MenuInfo.MenuID=[Forms]![MenuMaker]![MenuID] OR
([Forms]![MenuMaker]![MenuID] IS NULL and MenuInfo.MenuID Is Null)

I am guessing that you want to return matches to
[Forms]![MenuMaker]![MenuID] and when [Forms]![MenuMaker]![MenuID] is null
then you want records where menuId is Null.

I suspect that your criteria in the subquery is causing the problem.


WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID])

IsNull is going to return -1 or Zero

So SQL will see
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=0)

Or

(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=-1)

My guess is that neither of those is a valid value for MenuInfo.MenuId.
Are you
trying to use the NZ function?

Duane Hookom wrote:


First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuCats
WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;


This seems like it should be right but it's not returning any
records...No Error codes but also no records.
Thanks
DS

The problem is that I need the records where MenuID is Null...you are
right the MenuID field will be 1 or Greater but never 0 or -1.
Thanks
DS

 

[DS]

Perhaps the following criteria will work for you

WHERE MenuInfo.MenuID=[Forms]![MenuMaker]![MenuID] OR
([Forms]![MenuMaker]![MenuID] IS NULL and MenuInfo.MenuID Is Null)

I am guessing that you want to return matches to
[Forms]![MenuMaker]![MenuID] and when [Forms]![MenuMaker]![MenuID] is null
then you want records where menuId is Null.

I suspect that your criteria in the subquery is causing the problem.


WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID])

IsNull is going to return -1 or Zero

So SQL will see
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=0)

Or

(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=-1)

My guess is that neither of those is a valid value for MenuInfo.MenuId.
Are you
trying to use the NZ function?

DS wrote:


Duane Hookom wrote:



First quick glance suggests remove one of the returned fields from the
subquery:
SELECT MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active

FROM MenuCats

WHERE MenuCats.Active=-1
And MenuCats.MenuCatID=
(SELECT MenuInfo.MenuCatID
FROM MenuInfo
WHERE MenuInfo.MenuID=IsNull([Forms]![MenuMaker]![MenuID]))
ORDER BY MenuCats.MenuCat;


This seems like it should be right but it's not returning any
records...No Error codes but also no records.
Thanks
DS

The problem is that I need the records where MenuID is Null...you are
right the MenuID field will be 1 or Greater but never 0 or -1.
Thanks
DS

Thanks John, thats right! In the meantime I also came up with this,
without a subQuery.

SELECT MenuInfo.MenuID, MenuCats.MenuCatID, MenuCats.MenuCat,
MenuCats.Active
FROM MenuInfo RIGHT JOIN MenuCats ON MenuInfo.MenuCatID=MenuCats.MenuCatID
WHERE (((MenuInfo.MenuID) Is Null) And ((MenuCats.Active)=-1)) Or
(((MenuInfo.MenuID) Not Like Forms!MenuMaker!MenuID))
ORDER BY MenuCats.MenuCat;

DS