Error: Subqueries cannot be used in the expression

[webcats]

Hi all,

I've tried searching for this problem (this site and Google), but I haven't found an answer yet.

I have a query that was working fine until today.

In the Field area of the QBE grid I have:

Total Current: Sum([earned_current]+[unearned_current])

The query is grouped and I have the Total line set to Expression.


The error I'm getting is: Subqueries cannot be used in the expression ([earned_current]+[unearned_current]).

Does anyone know why I'm suddenly getting this error? It's driving me to drink!

Thanks.

 

[PHV]

Can you please post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[webcats]

Hi PH,

Here is the SQL:

SELECT Sum([earned_current]+[unearned_current]) AS [Total Current]
FROM tbl308_PreviousMonth_FS INNER JOIN tlkpTranslate_Descr_Fresno ON tbl308_PreviousMonth_FS.Serv_Code = tlkpTranslate_Descr_Fresno.Service_Code;

Thanks!

 

[PHV]

Nothing wrong I can see.
Have you tried to compact/repair the DB ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[webcats]

Exactly!

That's why it's driving me to drink...

I've compacted/repaired, I've created a blank database and imported all the objects into it, I've checked all my References, I've re-compiled all the code (I figured it couldn't hurt), I rebooted my computer (twice) - still the same error.

What's even stranger is that I have another database that uses the exact same query except for another area and it's working fine!

Maybe I'll import the other query that's working and just change the table names...

Thanks anyway!

 

[PHV]

Are tbl308_PreviousMonth_FS and tlkpTranslate_Descr_Fresno both real tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[webcats]

Yes, they're real tables.

I got it to work. I imported the other query (from the other area that does work), and changed the table names to match the Fresno area and now it works.

Very strange. Can a query be corrupted?

Thanks!

 

[PHV]

Can a query be corrupted?
Sure, absolutely ANYTHING may be corrupted in Access ;-)

 

[JonFer]

Qualify the fields in the Sum() with their table name and see if you still get the error.


John

http://www.interautomateconsulting.com