IIf Date Query Issue

[AGG1992]

Once again I am stuck. Basically I have a query as followed:
Current: Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]<=30,[dbo_GD-AgedDebtors]![Outstanding],0))
I'm trying to show outstanding bills if the bill date is less than or equal to my date parameter. However, it keeps coming up "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)"

 

[namliam]

Try
Current: Sum(IIf(Date()-[dbo_GD-AgedDebtors]![BillDate]<=30,[dbo_GD-AgedDebtors]![Outstanding],0))

I have a sneaking suspicion you are not entering your date in MM/DD/YYYY

 

[AGG1992]

Thank you! The error issue is now solved, it now runs.However, it produces 0 for everyone, which should not be the case. So another issue begins, which I'll attempt to solve :banghead:

 

[Brianwarnock]

Shouldn't that be >=

if the bill date was 29 sept and you were running today the subtraction would return 31, I think , and wouldn't you want that?

Brian

 

[jdraw]

What exactly is the purpose of the Sum()? I can see , as Brian said, that you are seeking records where the BillDate is 30 or more Days "past". But I'm not sure what you are "summing".

 

[AGG1992]

The point of the sum is, this query is to produce the total amount of debtors for each person in specific debtor periods i.e. Current month, 1 month, 2 months etc. Thus, if I was running the query for 30th September, then the query would allow me to produce the total amount of debtors for person A if the date I enter (30th Sep) and the bill date are less than 30 days apart.

 

[Smart8]

You could Try count instead of sum

 

[AGG1992]

Thank you everyone for the replies. I have finally fixed it anyway!

 

[jdraw]

Sum is for adding (usually amounts/numbers...)
Normally you would use Count to see how many records qualify to the criteria.
But I'm not sure what you re trying to do. Perhaps, it's just me, but I do not understand

Thus, if I was running the query for 30th September, then the query would allow me to produce the total amount of debtors for person A if the date I enter (30th Sep) and the bill date are less than 30 days apart.

 

[Brianwarnock]

Sum is for adding (usually amounts/numbers...)
Normally you would use Count to see how many records qualify to the criteria.
But I'm not sure what you re trying to do. Perhaps, it's just me, but I do not understand

Nor do I. I know he has fixed it but would love to see the full SQL.

DOes he mean the total numbers of debtors for person A
Or
The total amount of debt owed to person A
?

Brian

 

[AGG1992]

For your own access amusement, here is the full sql view. It is the amount of debt(unpaid billed time) for each lawyer.

PARAMETERS Run_date DateTime;
SELECT dbo_DEPARTMT.DEPNAME, dbo_Staff.strStaffName, IIf(Left([dbo_GD-AgedDebtors]![BillNumber],2)="DL","DL",IIf(Left([dbo_GD-AgedDebtors]![BillNumber],2)="SO","HC","GD")) AS Source, Sum([dbo_GD-AgedDebtors].BillTotal) AS SumOfBillTotal, Sum([dbo_GD-AgedDebtors].Outstanding) AS SumOfOutstanding, Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]<=30,[dbo_GD-AgedDebtors]![Outstanding],0)) AS [Current], Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]>30,IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]<=60,[dbo_GD-AgedDebtors]![Outstanding],0),0)) AS [1 month], Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]>60,IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]<=90,[dbo_GD-AgedDebtors]![Outstanding],0),0)) AS [2 month], Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]>90,IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]<=180,[dbo_GD-AgedDebtors]![Outstanding],0),0)) AS [3-6 months], Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]>180,IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]<=365,[dbo_GD-AgedDebtors]![Outstanding],0),0)) AS [6-12 months], Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]>365,[dbo_GD-AgedDebtors]![Outstanding],0)) AS [Over 12 months]
FROM ([dbo_GD-AgedDebtors] LEFT JOIN dbrganisation ON [dbo_GD-AgedDebtors].strOrgCode = dbrganisation.strOrgCode) LEFT JOIN ((dbo_FeeEarner LEFT JOIN dbo_DEPARTMT ON dbo_FeeEarner.DEPNO = dbo_DEPARTMT.DEPNO) LEFT JOIN dbo_Staff ON dbo_FeeEarner.strStaffCode = dbo_Staff.strStaffCode) ON [dbo_GD-AgedDebtors].strExecResponsible = dbo_FeeEarner.strStaffCode
WHERE ((([dbo_GD-AgedDebtors].mCCNO)="1") AND (([dbo_GD-AgedDebtors].feCCNO)="1"))
GROUP BY dbo_DEPARTMT.DEPNAME, dbo_Staff.strStaffName, IIf(Left([dbo_GD-AgedDebtors]![BillNumber],2)="DL","DL",IIf(Left([dbo_GD-AgedDebtors]![BillNumber],2)="SO","HC","GD"))
HAVING (((IIf(Left([dbo_GD-AgedDebtors]![BillNumber],2)="DL","DL",IIf(Left([dbo_GD-AgedDebtors]![BillNumber],2)="SO","HC","GD")))="GD"))
ORDER BY dbo_DEPARTMT.DEPNAME, dbo_Staff.strStaffName;

 

[Brianwarnock]

Thank you that clears up what you are doing which makes sense now, but my old eyes cannot see what has changed in the original clause, however I won't trouble you anymore.

Brian

 

[AGG1992]

No worries. Thanks for the help anyway.