IIf Date Query Issue (1 Viewer)

AGG1992

New member
Local time
Today, 19:32
Joined
Oct 24, 2014
Messages
7
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

The Mailman - AWF VIP
Local time
Today, 20:32
Joined
Aug 11, 2003
Messages
11,696
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

New member
Local time
Today, 19:32
Joined
Oct 24, 2014
Messages
7
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

Retired
Local time
Today, 19:32
Joined
Jun 2, 2003
Messages
12,701
Shouldn't that be >=

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

Brian
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:32
Joined
Jan 23, 2006
Messages
15,364
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

New member
Local time
Today, 19:32
Joined
Oct 24, 2014
Messages
7
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.
 

AGG1992

New member
Local time
Today, 19:32
Joined
Oct 24, 2014
Messages
7
Thank you everyone for the replies. I have finally fixed it anyway!:D
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:32
Joined
Jan 23, 2006
Messages
15,364
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

Retired
Local time
Today, 19:32
Joined
Jun 2, 2003
Messages
12,701
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

New member
Local time
Today, 19:32
Joined
Oct 24, 2014
Messages
7
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 dbo_Organisation ON [dbo_GD-AgedDebtors].strOrgCode = dbo_Organisation.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;
 
Last edited:

Brianwarnock

Retired
Local time
Today, 19:32
Joined
Jun 2, 2003
Messages
12,701
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
 

Users who are viewing this thread

Top Bottom