#error when running Access query

[AGG1992]

Hello, I don't know if there is a thread already on this, apologies if there is. I'm running a query and I'm trying to order it based on a number of IIf statements. However, I'm running into #error for the final IIf statement. I can post the SQL view, but can't send the database due to confidential information. I'm very new to access and can't see my error. Perhaps a joining error. The part in bold is the issue.

SELECT IIf([dbo_Staff]![datDateLeft] Is Not Null,3,IIf([dbo_Staff]![strStaffCatCode]="6",2,IIf([Salaries]![Time Recording]="1",4,1))) AS [Order], dbo_DEPARTMT.DEPNAME, OutputSum.FeeEarnerCode, (Nz([dbo_FEBudgetHours]![Total1]*[SWD]![Mondays])+([dbo_FEBudgetHours]![Total2]*[SWD]![Tuesdays])+([dbo_FEBudgetHours]![Total3]*[SWD]![Wednesdays])+([dbo_FEBudgetHours]![Total4]*[SWD]![Thursdays])+([dbo_FEBudgetHours]![Total5]*[SWD]![Fridays])+([dbo_FEBudgetHours]![Total6]*[SWD]![Saturdays])+([dbo_FEBudgetHours]![Total7]*[SWD]![Sundays])) AS [Contracted Hours], (Nz([OutputSum]![SumOfTimeInMins]/60)-([OutputSum]![SumOfNon-Working Day Time]/60)) AS [Accounted Hours], (Nz([OutputSum]![SumOfTimeInMins]/60)-([OutputSum]![SumOfNon-Working Day Time]/60))-(([dbo_FEBudgetHours]![Total1]*[SWD]![Mondays])+([dbo_FEBudgetHours]![Total2]*[SWD]![Tuesdays])+([dbo_FEBudgetHours]![Total3]*[SWD]![Wednesdays])+([dbo_FEBudgetHours]![Total4]*[SWD]![Thursdays])+([dbo_FEBudgetHours]![Total5]*[SWD]![Fridays])+([dbo_FEBudgetHours]![Total6]*[SWD]![Saturdays])+([dbo_FEBudgetHours]![Total7]*[SWD]![Sundays])) AS [Over/(Under)], (Nz([OutputSum]![SumOfHoliday Time]/60)) AS [Holiday Hours], (Nz([OutputSum]![SumOfStudy Time]/60)) AS [Study Hours], (Nz([OutputSum]![SumOfSick&Maternity Time]/60)) AS [Sick&Maternity Hours], IIf([Accounted Hours]>[Contracted Hours],(([OutputSum]![SumOfTimeInMins]/60)-([OutputSum]![SumOfNon-Working Day Time]/60)-([OutputSum]![SumOfHoliday Time]/60)-([OutputSum]![SumOfSick&Maternity Time]/60)-([OutputSum]![SumOfStudy Time]/60)),(([dbo_FEBudgetHours]![Total1]*[SWD]![Mondays])+([dbo_FEBudgetHours]![Total2]*[SWD]![Tuesdays])+([dbo_FEBudgetHours]![Total3]*[SWD]![Wednesdays])+([dbo_FEBudgetHours]![Total4]*[SWD]![Thursdays])+([dbo_FEBudgetHours]![Total5]*[SWD]![Fridays])+([dbo_FEBudgetHours]![Total6]*[SWD]![Saturdays])+([dbo_FEBudgetHours]![Total7]*[SWD]![Sundays]))-([OutputSum]![SumOfHoliday Time]/60)-([OutputSum]![SumOfSick&Maternity Time]/60)-([OutputSum]![SumOfStudy Time]/60)) AS [Available Hours], (Nz([OutputSum]![SumOfChargeable Billable Time]/60)) AS [Billable Chargeable Hours], Val(Nz([OutputSum]![SumOfNon-Billable Time],0)/60) AS [Non-Billable Chargeable Hours], (([OutputSum]![SumOfNon-Chargeable Time]/60)-([OutputSum]![SumOfNon-Working Day Time]/60)) AS [Non-Chargeable Hours], (Nz([OutputSum]![SumOfMarketing Time]/60)) AS [Marketing Hours], (Nz([OutputSum]![SumOfAdmin Time]/60)) AS [Admin Hours], (Nz([OutputSum]![SumOfChargeable Billable Time]/60)/([Available Hours])) AS [Percentage Available], (Nz([OutputSum]![SumOfChargeable Billable Time]/60)/(([dbo_FEBudgetHours]![Total1]*[SWD]![Mondays])+([dbo_FEBudgetHours]![Total2]*[SWD]![Tuesdays])+([dbo_FEBudgetHours]![Total3]*[SWD]![Wednesdays])+([dbo_FEBudgetHours]![Total4]*[SWD]![Thursdays])+([dbo_FEBudgetHours]![Total5]*[SWD]![Fridays])+([dbo_FEBudgetHours]![Total6]*[SWD]![Saturdays])+([dbo_FEBudgetHours]![Total7]*[SWD]![Sundays]))) AS [Percentage Standard], (Nz([OutputSum]![SumOfNon Billable Value],0)) AS [Non-Billable Time], (Nz([OutputSum]![SumOfBillable Chargeable Value],0)) AS [Billable Time Charged], ([dbo_FeeEarnerRates]![numChgOutperHr]) AS [Nominal £/hr]
FROM (((((OutputSum RIGHT JOIN (dbo_FeeEarner LEFT JOIN dbo_DEPARTMT ON dbo_FeeEarner.DEPNO = dbo_DEPARTMT.DEPNO) ON OutputSum.FeeEarnerCode = dbo_FeeEarner.strStaffCode) LEFT JOIN dbo_FEBudgetHours ON dbo_FeeEarner.strStaffCode = dbo_FEBudgetHours.strStaffCode) LEFT JOIN dbo_Staff ON dbo_FeeEarner.strStaffCode = dbo_Staff.strStaffCode) LEFT JOIN dbo_FeeEarnerRates ON dbo_FeeEarner.strStaffCode = dbo_FeeEarnerRates.strStaffCode) LEFT JOIN Salaries ON dbo_FeeEarner.strStaffCode = Salaries.Ref) LEFT JOIN SWD ON dbo_FeeEarner.strStaffCode = SWD.strStaffCode
WHERE (((dbo_FeeEarnerRates.DateApply)>#9/25/2014#) AND (((((Left([FeeEarnerCode],3)))))<>"DL-"))
ORDER BY dbo_DEPARTMT.DEPNAME, OutputSum.FeeEarnerCode;

 

[namliam]

Try formatting your sql and IIFs... you may just catch your problem...

SELECT IIf([dbo_Staff]![datDateLeft] Is Not Null
          ,3
          ,IIf([dbo_Staff]![strStaffCatCode]="6"
              ,2
              ,IIf([Salaries]![Time Recording]="1"
                  ,4
                  ,1
                  )
              )
          ) AS [Order],

Then again you may not...

 

[JHB]

Try the below:

SELECT IIf(Not IsNull([dbo_Staff]![datDateLeft]),3, IIf([dbo_Staff]![strStaffCatCode]="6",2,IIf([Salaries]![Time Recording]="1",4,1))) AS [Order]...

About posting the database, make a copy of it and make some sample data.

 

[plog]

IIf([Accounted Hours]>[Contracted Hours],(([OutputSum]![SumOfTimeInMins]/60)-([OutputSum]![SumOfNon-Working Day Time]/60)-([OutputSum]![SumOfHoliday Time]/60)-([OutputSum]![SumOfSick&Maternity Time]/60)-([OutputSum]![SumOfStudy Time]/60)),(([dbo_FEBudgetHours]![Total1]*[SWD]![Mondays])+([dbo_FEBudgetHours]![Total2]*[SWD]![Tuesdays])+([dbo_FEBudgetHours]![Total3]*[SWD]![Wednesdays])+([dbo_FEBudgetHours]![Total4]*[SWD]![Thursdays])+([dbo_FEBudgetHours]![Total5]*[SWD]![Fridays])+([dbo_FEBudgetHours]![Total6]*[SWD]![Saturdays])+([dbo_FEBudgetHours]![Total7]*[SWD]![Sundays]))-([OutputSum]![SumOfHoliday Time]/60)-([OutputSum]![SumOfSick&Maternity Time]/60)-([OutputSum]![SumOfStudy Time]/60)) AS [Available Hours]

Here's meta-lesson #1: when you have a single calculated field that is that complex, you no longer get to jam it into the query. It's time to create a function inside a module, you pass that function all the information it needs, it performs the logic it needs and returns your value. When you do that, you can use as many lines as you want to write yoru code and insert notes with it so you can explain what's happening in each section.

As it is, no one is probably going to help you dig through that mess of code to identify specifically your problem. This code needs to be made easier to read adn thus easier to debug.

With that said, I noticed the first field in that IIf statement is [Account Hours]. That field doesn't exist in your underlying data (the FROM clause), but instead you are calculating it (in the SELECT clause). I don't think you can use that value in the same SELECT clause that its calculated in. My suggestion is to remove it from this query, save the query and make a new query using it as the source. Than you will be able to use all those calculated fields in new caluclated fields.