Sub queries cannot be used in the expression?

[cathyb569]

i have created a report with various calculations adding total hours etc. My problem is that when i try to create a calculation at the bottom of the report summarizing the totals for these fields I get the error message "Subqueries cannot be used in the Expression (Sum([Hours Outstanding])). Im assuming it is because I am trying to summarize fields that already contain a calculation but I have no idea how to get around this.

Can anyone help

 

[SOS]

My problem is that when i try to create a calculation at the bottom of the report summarizing the totals for these fields I get the error message "Subqueries cannot be used in the Expression (Sum([Hours Outstanding])).

Where do you get Hours Outstanding? Is that a field in your query for the report or is it a calculated field on the report. If a calculated field then you would need to recreate the calculation thus:

=Sum(([Field1]*[Field2])/[Field3])

etc.

 

[cathyb569]

its a calculated field in a report and the calculation in enter is:

=[HRS]+[Additions]-[Hours Complete]
what I want to do is the summarize hours complete at the bottom
but when i add a sum calculation i get the error.

i tried to use your formula in the hours complete box but it
doesn't work???

 

[SOS]

It would be

=Sum([HRS]+[Additions]-[Hours Complete])

But you have to make sure that you do not have a text box named HRS or Additions or Hours Complete, because you use the field names and not the text box names. If you have any of those then you would need to rename the text boxes and then make sure that you use the field names and not the text box names.

Also, if you have any nulls that will cause an error so you may want to do:

=Sum(Nz([HRS],0)+Nz([Additions],0)-Nz([Hours Complete],0))

to handle any nulls.

 

[cathyb569]

Hi Again - still getting the same error, any other suggestions?

 

[SOS]

can you post the database?

 

[cathyb569]

sure

when you open the database, select customer registration, then view existing courses. the report/column in question is called hours outstanding, i just need to summarize everything

thanks for all you help

 

[cathyb569]

sorry but I think Im having the same problem with the "create Invoice" tab as well, I have been trying to summarize the cost fields that appear but keep getting an error?

 

[Scooterbug]

You might want to convert the db to 2000/2003 format. Might be able to get a bit more help that way.

 

[cathyb569]

is there no other option? I dont really want to have to do that?

 

[cathyb569]

is there no other option? i dont really want to convert?

 

[Scooterbug]

Well, you dont have to, but not everybody has Access 2007. It just limits the amount of ppl who are able to help you out.