Access Query too complex?

[weathermanfsu]

I have a query I am running that is taking form values and basically making sure that they are not null before including them in calculations to determine the amount of $$ a person should be reimbursed before travelling. I recently mad a couple of changes (removed a field) and now it works fine when I only have 2 days worth of reimbursement data, but when I go to 3 I get an error that it may be too complex and it can not be run. Can you all please look at this and give me a hand? Thanks!

 
SELECT TravelData.Name, TravelData.Purpose, TravelData.Depart_Time, TravelData.Return_Time, TravelData.Date, TravelData.Project, TravelData.From, TravelData.To, IIf(Nz([Mileage]),[Mileage],0) AS ExMileage, IIf(Nz([Lodging]),[Lodging],0) AS ExLodging, IIf(Nz([Perdiem]),[Perdiem],0) AS ExPerdiem, IIf(Nz([Bag_Tips]),[Bag_Tips],0) AS ExBag, IIf(Nz([Taxi]),[Taxi],0) AS ExTaxi, IIf(Nz([Parking]),[Parking],0) AS ExParking, IIf(Nz([Travel]),[Travel],0) AS ExTravel, IIf(Nz([Rental]),[Rental],0) AS ExRental, IIf(Nz([Gas]),[Gas],0) AS ExGas, IIf(Nz([Other1_Amt]),[Other1_Amt],0) AS ExOth1, IIf(Nz([Other2_Amt]),[Other2_Amt],0) AS ExOth2, CCur(RoundCC([Mileage]*0.445)) AS Auto_Expenses, CCur(IIf(Nz([Lodging]),[Lodging],0)+IIf(Nz([Perdiem]),[Perdiem],0)+IIf(Nz([Bag_Tips]),[Bag_Tips],0)+IIf(Nz([Taxi]),[Taxi],0)+IIf(Nz([Parking]),[Parking],0)+IIf(Nz([Travel]),[Travel],0)+IIf(Nz([Rental]),[Rental],0)+IIf(Nz([Gas]),[Gas],0)+IIf(Nz([Other1_Amt]),[Other1_Amt],0)+IIf(Nz([Other2_Amt]),[Other2_Amt],0)+IIf(Nz([Auto_Expenses]),[Auto_Expenses],0)) AS Total, CCur(IIf(Nz([Lodging]),[Lodging],0)+IIf(Nz([Perdiem]),[Perdiem],0)+IIf(Nz([Bag_Tips]),[Bag_Tips],0)+IIf(Nz([Taxi]),[Taxi],0)+IIf(Nz([Parking]),[Parking],0)+IIf(Nz([Travel]),[Travel],0)+IIf(Nz([Rental]),[Rental],0)+IIf(Nz([Gas]),[Gas],0)+IIf(Nz([Other1_Amt]),[Other1_Amt],0)+IIf(Nz([Other2_Amt]),[Other2_Amt],0)+IIf(Nz([Auto_Expenses]),[Auto_Expenses],0)) AS 5300TOT, TravelData.Comments, TravelData.Other1, TravelData.Other2, TravelData.Lodging, TravelData.Perdiem, TravelData.Bag_Tips, TravelData.Taxi, TravelData.Parking, TravelData.Travel, TravelData.Rental, TravelData.Gas, TravelData.Other1_Amt, TravelData.Other2_Amt, IIf(Nz([Auto_Expenses]),[Auto_Expenses],0) AS ExAuto
FROM TravelData 
WHERE (((TravelData.Name)=[forms]![frmMainMenu]![txtName]))
GROUP BY TravelData.Name, TravelData.Purpose, TravelData.Depart_Time, TravelData.Return_Time, TravelData.Date, TravelData.Project, TravelData.From, TravelData.To, IIf(Nz([Mileage]),[Mileage],0), IIf(Nz([Lodging]),[Lodging],0), IIf(Nz([Perdiem]),[Perdiem],0), IIf(Nz([Bag_Tips]),[Bag_Tips],0), IIf(Nz([Taxi]),[Taxi],0), IIf(Nz([Parking]),[Parking],0), IIf(Nz([Travel]),[Travel],0), IIf(Nz([Rental]),[Rental],0), IIf(Nz([Gas]),[Gas],0), IIf(Nz([Other1_Amt]),[Other1_Amt],0), IIf(Nz([Other2_Amt]),[Other2_Amt],0), CCur(RoundCC([Mileage]*0.445)), CCur(IIf(Nz([Lodging]),[Lodging],0)+IIf(Nz([Perdiem]),[Perdiem],0)+IIf(Nz([Bag_Tips]),[Bag_Tips],0)+IIf(Nz([Taxi]),[Taxi],0)+IIf(Nz([Parking]),[Parking],0)+IIf(Nz([Travel]),[Travel],0)+IIf(Nz([Rental]),[Rental],0)+IIf(Nz([Gas]),[Gas],0)+IIf(Nz([Other1_Amt]),[Other1_Amt],0)+IIf(Nz([Other2_Amt]),[Other2_Amt],0)+IIf(Nz([Auto_Expenses]),[Auto_Expenses],0)), CCur(IIf(Nz([Lodging]),[Lodging],0)+IIf(Nz([Perdiem]),[Perdiem],0)+IIf(Nz([Bag_Tips]),[Bag_Tips],0)+IIf(Nz([Taxi]),[Taxi],0)+IIf(Nz([Parking]),[Parking],0)+IIf(Nz([Travel]),[Travel],0)+IIf(Nz([Rental]),[Rental],0)+IIf(Nz([Gas]),[Gas],0)+IIf(Nz([Other1_Amt]),[Other1_Amt],0)+IIf(Nz([Other2_Amt]),[Other2_Amt],0)+IIf(Nz([Auto_Expenses]),[Auto_Expenses],0)), TravelData.Comments, TravelData.Other1, TravelData.Other2, TravelData.Lodging, TravelData.Perdiem, TravelData.Bag_Tips, TravelData.Taxi, TravelData.Parking, TravelData.Travel, TravelData.Rental, TravelData.Gas, TravelData.Other1_Amt, TravelData.Other2_Amt, IIf(Nz([Auto_Expenses]),[Auto_Expenses],0)
ORDER BY TravelData.Date;
 

NOTE: RoundCC is a function I wrote to round my currency calculations based on mileage. Even when I told Access it was a currency field it still kept more than 2 digits. It also did not round correctly (44.555 rounded to 44.55 instead of 44.56). so roundCC calls that function.

[PEB]

There is a lot of IIF!!! But maybe they are necessery!!!

So I think that it's better that in your Where clause you don't have as parameter a Form parameter, its's better to place there a String!

Also it's very important to make firstly an ordinary query without groub by clause!!! If it is working You have a chance!

Then You can create an agregate query using the ready results!
If the second query gives an error, so you can save the result of the first query in a temporary table and then do an agregate query on a table! :)