Access Subquery in select not working

[GPnew]

Simplifying my sql
Select a.accountcode as Test ,
(select b.accountname from b where b.accountcode = Test)
from a

I have found the parameter 'Test' (which is a number) is not recognised in the subquery. However I have found the following works in the subquery (select b.accountname from b where b.accountcode = a.accountcode)

Is there anyway I can pass the test parameter into the subquery. ?

Thanks

 

[dhookom]

Why not just use accountcode? it seems like such a simple solution.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Why don't you want to use the subquery that works? The word 'Test' is just an alias.

You are going to find out thet this:

[tt]Select a.accountcode as Test,
(select b.accountname from b where b.accountcode = a.accountcode)
from a
ORDER BY [blue]a.accountcode [/blue]
[/tt]
works, but this:
[tt]
Select a.accountcode as Test,
(select b.accountname from b where b.accountcode = a.accountcode)
from a
ORDER BY [blue]Test[/blue]
[/tt]
Does not.

What is your real SQL that you are trying to run?


Have fun.

---- Andy

 

[PHV]

Anyway, why not using a JOIN insead of a subquery ???

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GPnew]

Thanks for your imput.
My full query is pretty ugly .....has a couple of joins and some iif's etc (to which the alias is actually attached) ....but it works apart from the problem illustrated
I was hoping for an easy answer.... unfortunately it looks like I can't use the alias (i.e 'Test' in my posted example) as a parameter in a subquery. I will have to revisit the whole query
I assume the same regarding alias as parameters applies to a Dlookup

Thanks