Is it allowable to use a union statement in an access subquery?

[Guest]

I am getting the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] This operation is not allowed in
subqueries.

I think the problem may be my use of the Union operator in the suquery, but
I can not find any documentation about the combination.

Any help would be appreciated.

 

[Douglas J. Steele]

What's the SQL of your complete query?

 

[Guest]

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID = (SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = " + SpeciesIDIn + " UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = " + SpeciesIDIn +
" ) ";

The query is written in JavaScript in an .asp document. If you need anything
else, let me know.

Cordially,

Mark Fisher

What's the SQL of your complete query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am getting the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] This operation is not allowed in
subqueries.

I think the problem may be my use of the Union operator in the suquery,
but
I can not find any documentation about the combination.

Any help would be appreciated.

 

[Douglas J. Steele]

You sure you've got a value for SpeciesIDIn? If not, you're going to have an
invalid SQL statement.

Also, that assumes that SpeciesID is numeric. If it's text, you'll need to
add quotes:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID = (SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn + "'
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" + SpeciesIDIn
+
"' ) ";

(In case it's not obvious, I've changed some of the " to " ' or ' " as
appropriate)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID = (SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = " + SpeciesIDIn + "
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = " + SpeciesIDIn
+
" ) ";

The query is written in JavaScript in an .asp document. If you need
anything
else, let me know.

Cordially,

Mark Fisher

What's the SQL of your complete query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am getting the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] This operation is not allowed
in
subqueries.

I think the problem may be my use of the Union operator in the suquery,
but
I can not find any documentation about the combination.

Any help would be appreciated.

 

[Guest]

Yes, I am sure that I have a value, and I am sure that it is numeric. I have
been building this query piece by piece, so that I have proven that I can get
data, and that it is in the right format. This if the first time I have tried
to use the union statement in a query, and the first time I have received
this error message. I am at a loss since I need the same type of
data(VoyageID) from multiple tables, and do not know what exactly to use to
get it. The UNION would work, but it does not seem to work in a subquery. Any
suggestions?

Cordially.

Mark Fisher

You sure you've got a value for SpeciesIDIn? If not, you're going to have an
invalid SQL statement.

Also, that assumes that SpeciesID is numeric. If it's text, you'll need to
add quotes:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID = (SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn + "'
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" + SpeciesIDIn
+
"' ) ";

(In case it's not obvious, I've changed some of the " to " ' or ' " as
appropriate)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID = (SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = " + SpeciesIDIn + "
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = " + SpeciesIDIn
+
" ) ";

The query is written in JavaScript in an .asp document. If you need
anything
else, let me know.

Cordially,

Mark Fisher

What's the SQL of your complete query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am getting the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] This operation is not allowed
in
subqueries.

I think the problem may be my use of the Union operator in the suquery,
but
I can not find any documentation about the combination.

Any help would be appreciated.

 

[Douglas J. Steele]

Just reread your code, and I think that it should be

WHERE VoyageID IN (SELECT ...

not

WHERE VoyageID = (SELECT ...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, I am sure that I have a value, and I am sure that it is numeric. I
have
been building this query piece by piece, so that I have proven that I can
get
data, and that it is in the right format. This if the first time I have
tried
to use the union statement in a query, and the first time I have received
this error message. I am at a loss since I need the same type of
data(VoyageID) from multiple tables, and do not know what exactly to use
to
get it. The UNION would work, but it does not seem to work in a subquery.
Any
suggestions?

Cordially.

Mark Fisher

You sure you've got a value for SpeciesIDIn? If not, you're going to have
an
invalid SQL statement.

Also, that assumes that SpeciesID is numeric. If it's text, you'll need
to
add quotes:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn + "'
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" +
SpeciesIDIn
+
"' ) ";

(In case it's not obvious, I've changed some of the " to " ' or ' " as
appropriate)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = " + SpeciesIDIn + "
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = " +
SpeciesIDIn
+
" ) ";

The query is written in JavaScript in an .asp document. If you need
anything
else, let me know.

Cordially,

Mark Fisher

:

What's the SQL of your complete query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am getting the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] This operation is not
allowed
in
subqueries.

I think the problem may be my use of the Union operator in the
suquery,
but
I can not find any documentation about the combination.

Any help would be appreciated.

 

[Guest]

Thanks, but I have already tried using 'IN' , and I get the same results.

Cordially,

Mark Fisher

Just reread your code, and I think that it should be

WHERE VoyageID IN (SELECT ...

not

WHERE VoyageID = (SELECT ...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, I am sure that I have a value, and I am sure that it is numeric. I
have
been building this query piece by piece, so that I have proven that I can
get
data, and that it is in the right format. This if the first time I have
tried
to use the union statement in a query, and the first time I have received
this error message. I am at a loss since I need the same type of
data(VoyageID) from multiple tables, and do not know what exactly to use
to
get it. The UNION would work, but it does not seem to work in a subquery.
Any
suggestions?

Cordially.

Mark Fisher

You sure you've got a value for SpeciesIDIn? If not, you're going to have
an
invalid SQL statement.

Also, that assumes that SpeciesID is numeric. If it's text, you'll need
to
add quotes:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn + "'
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" +
SpeciesIDIn
+
"' ) ";

(In case it's not obvious, I've changed some of the " to " ' or ' " as
appropriate)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = " + SpeciesIDIn + "
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = " +
SpeciesIDIn
+
" ) ";

The query is written in JavaScript in an .asp document. If you need
anything
else, let me know.

Cordially,

Mark Fisher

:

What's the SQL of your complete query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am getting the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] This operation is not
allowed
in
subqueries.

I think the problem may be my use of the Union operator in the
suquery,
but
I can not find any documentation about the combination.

Any help would be appreciated.

 

[Douglas J. Steele]

Son of a gun. From the error message I'm getting trying to do this directly
in Access, it would appear that you can't use a UNION statement in a
subquery.

Try:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID IN
(SELECT VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn +
"')
UNION
SELECT VDate FROM RVOVoyage WHERE VoyageID IN
(SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" + SpeciesIDIn
+ "' ) ";

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, but I have already tried using 'IN' , and I get the same results.

Cordially,

Mark Fisher

Just reread your code, and I think that it should be

WHERE VoyageID IN (SELECT ...

not

WHERE VoyageID = (SELECT ...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, I am sure that I have a value, and I am sure that it is numeric. I
have
been building this query piece by piece, so that I have proven that I
can
get
data, and that it is in the right format. This if the first time I have
tried
to use the union statement in a query, and the first time I have
received
this error message. I am at a loss since I need the same type of
data(VoyageID) from multiple tables, and do not know what exactly to
use
to
get it. The UNION would work, but it does not seem to work in a
subquery.
Any
suggestions?

Cordially.

Mark Fisher

:

You sure you've got a value for SpeciesIDIn? If not, you're going to
have
an
invalid SQL statement.

Also, that assumes that SpeciesID is numeric. If it's text, you'll
need
to
add quotes:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn +
"'
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" +
SpeciesIDIn
+
"' ) ";

(In case it's not obvious, I've changed some of the " to " ' or ' " as
appropriate)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = " + SpeciesIDIn +
"
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = " +
SpeciesIDIn
+
" ) ";

The query is written in JavaScript in an .asp document. If you need
anything
else, let me know.

Cordially,

Mark Fisher

:

What's the SQL of your complete query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am getting the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] This operation is not
allowed
in
subqueries.

I think the problem may be my use of the Union operator in the
suquery,
but
I can not find any documentation about the combination.

Any help would be appreciated.

 

[Guest]

Thank you very much for all your help. This worked. Now on to the next
problem...

Cordially,

Mark Fisher

Son of a gun. From the error message I'm getting trying to do this directly
in Access, it would appear that you can't use a UNION statement in a
subquery.

Try:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID IN
(SELECT VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn +
"')
UNION
SELECT VDate FROM RVOVoyage WHERE VoyageID IN
(SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" + SpeciesIDIn
+ "' ) ";

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, but I have already tried using 'IN' , and I get the same results.

Cordially,

Mark Fisher

Just reread your code, and I think that it should be

WHERE VoyageID IN (SELECT ...

not

WHERE VoyageID = (SELECT ...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Yes, I am sure that I have a value, and I am sure that it is numeric. I
have
been building this query piece by piece, so that I have proven that I
can
get
data, and that it is in the right format. This if the first time I have
tried
to use the union statement in a query, and the first time I have
received
this error message. I am at a loss since I need the same type of
data(VoyageID) from multiple tables, and do not know what exactly to
use
to
get it. The UNION would work, but it does not seem to work in a
subquery.
Any
suggestions?

Cordially.

Mark Fisher

:

You sure you've got a value for SpeciesIDIn? If not, you're going to
have
an
invalid SQL statement.

Also, that assumes that SpeciesID is numeric. If it's text, you'll
need
to
add quotes:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn +
"'
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" +
SpeciesIDIn
+
"' ) ";

(In case it's not obvious, I've changed some of the " to " ' or ' " as
appropriate)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = " + SpeciesIDIn +
"
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = " +
SpeciesIDIn
+
" ) ";

The query is written in JavaScript in an .asp document. If you need
anything
else, let me know.

Cordially,

Mark Fisher

:

What's the SQL of your complete query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am getting the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] This operation is not
allowed
in
subqueries.

I think the problem may be my use of the Union operator in the
suquery,
but
I can not find any documentation about the combination.

Any help would be appreciated.

 

[Guest]

Hi Doug. I'm trying to use a Union query as the row source for a combo box.
However, this method doesn't allow me to specify which row of results is
selected as the value for my combo box (it just uses the first one it finds
no matter which one I pick). I'm wondering if this subquery method will
allow me to get around this?
--
Why are you asking me? I dont know what Im doing!

Jaybird

Son of a gun. From the error message I'm getting trying to do this directly
in Access, it would appear that you can't use a UNION statement in a
subquery.

Try:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID IN
(SELECT VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn +
"')
UNION
SELECT VDate FROM RVOVoyage WHERE VoyageID IN
(SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" + SpeciesIDIn
+ "' ) ";

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, but I have already tried using 'IN' , and I get the same results.

Cordially,

Mark Fisher

Just reread your code, and I think that it should be

WHERE VoyageID IN (SELECT ...

not

WHERE VoyageID = (SELECT ...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Yes, I am sure that I have a value, and I am sure that it is numeric. I
have
been building this query piece by piece, so that I have proven that I
can
get
data, and that it is in the right format. This if the first time I have
tried
to use the union statement in a query, and the first time I have
received
this error message. I am at a loss since I need the same type of
data(VoyageID) from multiple tables, and do not know what exactly to
use
to
get it. The UNION would work, but it does not seem to work in a
subquery.
Any
suggestions?

Cordially.

Mark Fisher

:

You sure you've got a value for SpeciesIDIn? If not, you're going to
have
an
invalid SQL statement.

Also, that assumes that SpeciesID is numeric. If it's text, you'll
need
to
add quotes:

var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = '" + SpeciesIDIn +
"'
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = '" +
SpeciesIDIn
+
"' ) ";

(In case it's not obvious, I've changed some of the " to " ' or ' " as
appropriate)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



var guestCommand = "Select VDate FROM RVOVoyage WHERE VoyageID =
(SELECT
VoyageID FROM BiodredgeSpecies WHERE SpeciesID = " + SpeciesIDIn +
"
UNION
SELECT VoyageID FROM PlanktonNetSpecies WHERE SpeciesID = " +
SpeciesIDIn
+
" ) ";

The query is written in JavaScript in an .asp document. If you need
anything
else, let me know.

Cordially,

Mark Fisher

:

What's the SQL of your complete query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am getting the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] This operation is not
allowed
in
subqueries.

I think the problem may be my use of the Union operator in the
suquery,
but
I can not find any documentation about the combination.

Any help would be appreciated.