problem with sums in query

[Manaz]

I have a query which returns records in 5 different fault categories; mechanical, service, electrical, damage and retrofit. The values the query return are based on another query and are a sum of the amount of money spent in each category.

I have put a sum fuction in the 'field' box like this; total: ([Mechanical]+[Service]+[Electrical]+[Damage]+[Retrofit])

I also have a criteria set up where every time the query runs a specific id has to be entered.

The problem is when i run the query with the function shown above i have to enter the id to run the query then it asks me to enter the id again for the 'Retrofit', it then runs the query but adds the value i have entered in the criteria for retrofit to the totals of the other fault categories!!!

If i remove the retrofit part of the sum funtion it works fine

Please help this is very frustrating

Thanks

 

[ajetrumpet]

I have put a sum fuction in the 'field' box like this; total: ([Mechanical]+[Service]+[Electrical]+[Damage]+[Retrofit])

This should work fine. You are aware that surrounding single words in [ ] is read by Access as field names???

The problem is when i run the query with the function shown above i have to enter the id to run the query then it asks me to enter the id again for the 'Retrofit',

What does the popup ASK for?? Does it say ID, or does it say RETROFIT??? If it says "ID", you might have to post the statement so people can look at it. If it says "Retrofit", most likely there is no field in the underlying (source) query called "Retrofit", because Access is telling you that the word is not recognized / cannot be referenced.

 

[Manaz]

thanks for the reply but i have just solved it.

I feel like an idiot but i was missing the space between the 'retro' and 'fit'