select and where

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following in my criteria for a query

when this is run
Access is telling me that only one value can be returned
the relationship is that the Sub CoCode may have a many to one with CoCode
Can some please help -either amending this to acheive what I want or suggest
another way.


(SELECT [Sub CoCode] FROM [Org details-sub] WHERE [CoCd] = [Forms]![Legal
Entity Form-CONS]![CoCode])

I tries to use the list box tied to unbound text on the main form but the
coding gave me a problem - I already have questions out on the use of
code-but without any sucuss

Thanks
Danny
 
Hi,


Why are you using ( ) around the SELECT statement? What is the context
(where you use the statement) ?


A SINGLE field of a given record can only have ONE value.


..... SET MyFIeld = ( SELECT ... )


the innermost SELECT should return just ONE value, in this context, since
just one value can be stored in MyField.


Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel
It does not work without the ()


Is there a way I can set the criteira based on the record returned on a
query -
the query returns the Sub CoCode
[Sub CoCode] FROM [Org details-sub]

Thanks
Danny




Michel Walsh said:
Hi,


Why are you using ( ) around the SELECT statement? What is the context
(where you use the statement) ?


A SINGLE field of a given record can only have ONE value.


..... SET MyFIeld = ( SELECT ... )


the innermost SELECT should return just ONE value, in this context, since
just one value can be stored in MyField.


Hoping it may help,
Vanderghast, Access MVP


Danny said:
I have the following in my criteria for a query

when this is run
Access is telling me that only one value can be returned
the relationship is that the Sub CoCode may have a many to one with CoCode
Can some please help -either amending this to acheive what I want or
suggest
another way.


(SELECT [Sub CoCode] FROM [Org details-sub] WHERE [CoCd] = [Forms]![Legal
Entity Form-CONS]![CoCode])

I tries to use the list box tied to unbound text on the main form but the
coding gave me a problem - I already have questions out on the use of
code-but without any sucuss

Thanks
Danny
Click to expand...
Click to expand...
 
Hi,


If the context requires a ( ), then it may be that the context requires at
most one row. In those cases, a TOP 1, if the ORDER BY defines a unique
ordering, or an aggregate, like MIN, MAX, LAST, FIRST, ... can insure you
there is just one value returned by the sub-select query.


(SELECT TOP 1 whatever FROM somewhere WHERE condition ORDER BY primaryKey )

or

(SELECT MAX(whatever) FROM somewhere WHERE condition )




Hoping it may help,
Vanderghast, Access MVP


Danny said:
Hi Michel
It does not work without the ()


Is there a way I can set the criteira based on the record returned on a
query -
the query returns the Sub CoCode
[Sub CoCode] FROM [Org details-sub]

Thanks
Danny




Michel Walsh said:
Hi,


Why are you using ( ) around the SELECT statement? What is the context
(where you use the statement) ?


A SINGLE field of a given record can only have ONE value.


..... SET MyFIeld = ( SELECT ... )


the innermost SELECT should return just ONE value, in this context,
since
just one value can be stored in MyField.


Hoping it may help,
Vanderghast, Access MVP


Danny said:
I have the following in my criteria for a query

when this is run
Access is telling me that only one value can be returned
the relationship is that the Sub CoCode may have a many to one with
CoCode
Can some please help -either amending this to acheive what I want or
suggest
another way.


(SELECT [Sub CoCode] FROM [Org details-sub] WHERE [CoCd] =
[Forms]![Legal
Entity Form-CONS]![CoCode])

I tries to use the list box tied to unbound text on the main form but
the
coding gave me a problem - I already have questions out on the use of
code-but without any sucuss

Thanks
Danny
Click to expand...
Click to expand...
Click to expand...
 
I have the following in my criteria for a query

when this is run
Access is telling me that only one value can be returned
the relationship is that the Sub CoCode may have a many to one with CoCode
Can some please help -either amending this to acheive what I want or suggest
another way.


(SELECT [Sub CoCode] FROM [Org details-sub] WHERE [CoCd] = [Forms]![Legal
Entity Form-CONS]![CoCode])

I tries to use the list box tied to unbound text on the main form but the
coding gave me a problem - I already have questions out on the use of
code-but without any sucuss
Click to expand...

If you are expecting multiple values of [Sub CoCode] to be returned
from [Org Details-sub], you may be able to use a different syntax:
just precede this expression with the keyword IN:

IN (SELECT [Sub CoCode] <etc>)


John W. Vinson[MVP]
 
Hi Michel
tried both did not work - top 6 is not available in expresions

Danny


Michel Walsh said:
Hi,


If the context requires a ( ), then it may be that the context requires at
most one row. In those cases, a TOP 1, if the ORDER BY defines a unique
ordering, or an aggregate, like MIN, MAX, LAST, FIRST, ... can insure you
there is just one value returned by the sub-select query.


(SELECT TOP 1 whatever FROM somewhere WHERE condition ORDER BY primaryKey )

or

(SELECT MAX(whatever) FROM somewhere WHERE condition )




Hoping it may help,
Vanderghast, Access MVP


Danny said:
Hi Michel
It does not work without the ()


Is there a way I can set the criteira based on the record returned on a
query -
the query returns the Sub CoCode
[Sub CoCode] FROM [Org details-sub]

Thanks
Danny




Michel Walsh said:
Hi,


Why are you using ( ) around the SELECT statement? What is the context
(where you use the statement) ?


A SINGLE field of a given record can only have ONE value.


..... SET MyFIeld = ( SELECT ... )


the innermost SELECT should return just ONE value, in this context,
since
just one value can be stored in MyField.


Hoping it may help,
Vanderghast, Access MVP


I have the following in my criteria for a query

when this is run
Access is telling me that only one value can be returned
the relationship is that the Sub CoCode may have a many to one with
CoCode
Can some please help -either amending this to acheive what I want or
suggest
another way.


(SELECT [Sub CoCode] FROM [Org details-sub] WHERE [CoCd] =
[Forms]![Legal
Entity Form-CONS]![CoCode])

I tries to use the list box tied to unbound text on the main form but
the
coding gave me a problem - I already have questions out on the use of
code-but without any sucuss

Thanks
Danny
Click to expand...
Click to expand...
Click to expand...
 
Hi John

the IN does not work

some one must have the answer !

Danny


John Vinson said:
I have the following in my criteria for a query

when this is run
Access is telling me that only one value can be returned
the relationship is that the Sub CoCode may have a many to one with CoCode
Can some please help -either amending this to acheive what I want or suggest
another way.


(SELECT [Sub CoCode] FROM [Org details-sub] WHERE [CoCd] = [Forms]![Legal
Entity Form-CONS]![CoCode])

I tries to use the list box tied to unbound text on the main form but the
coding gave me a problem - I already have questions out on the use of
code-but without any sucuss
Click to expand...

If you are expecting multiple values of [Sub CoCode] to be returned
from [Org Details-sub], you may be able to use a different syntax:
just precede this expression with the keyword IN:

IN (SELECT [Sub CoCode] <etc>)


John W. Vinson[MVP]
Click to expand...
 
Hi John

the IN does not work

some one must have the answer !
Click to expand...

Please post the COMPLETE SQL of your query, describe where the values
you're using as criteria come from, and what exactly you're trying to
accomplish.

John W. Vinson[MVP] '
 
Back
Top