is Nested IIF Function in access? (1 Viewer)

[patelnitesha]

Hi there,

can i use nested function in access form to get a value for list box. Here is the following code i am using.

sql = "select invoiceNumber as InvNo, iif(sum(value_os)=0,'**Paid**',iif( (type<2) and (t_date>#06/01/2008#),format(sum(value_os)+(sum(value_os)*fcharge),'£#,##0.00'),format(sum(value_os),'£#,##0.00'))) as Owed, BankSlip, paydate, iif(sum(value_os)>0,'Credit Note',iif(sum(value_os)<0,'Invoice','-----')) as Type from joint_ledger where owner_ref=" & ref_num & " and InvoiceDate>#" & listdate & "# and invoicenumber<>0 and (type<20 or type=21) group by invoicenumber,value_settled, paydate,bankslip order by invoicenumber desc" 'InvoiceDate desc,


if Nested IIF is possible then could you please help me out why this is not working..

Many thanks
Nitesha

 

[namliam]

Well lets first make it readable...

sql = ""
sql = sql & "select invoiceNumber as InvNo " 
sql = sql & ", iif(sum(value_os)=0 " 
sql = sql & "     ,'**Paid**' " 
sql = sql & "     ,iif( (type<2) and (t_date>#06/01/2008#) " 
sql = sql & "         ,format(sum(value_os)+(sum(value_os)*fcharge ),'£#,##0.00') " 
sql = sql & "         ,format(sum(value_os),'£#,##0.00')"
sql = sql & "         )"
sql = sql & "     ) as Owed " 
sql = sql & ", BankSlip " 
sql = sql & ", paydate "
sql = sql & ", iif(sum(value_os)>0 " 
sql = sql & "     ,'Credit Note' " 
sql = sql & "     ,iif(sum(value_os)<0 " 
sql = sql & "          ,'Invoice' " 
sql = sql & "          ,'-----' "
sql = sql & "          )" 
sql = sql & "     ) as Type  " 
sql = sql & "from joint_ledger  " 
sql = sql & "where owner_ref=" & ref_num 
sql = sql & " and InvoiceDate>#" & listdate & "#  " 
sql = sql & " and invoicenumber<>0 " 
sql = sql & " and (type<20 or type=21) " 
sql = sql & "group by invoicenumber " 
sql = sql & ", value_settled " 
sql = sql & ", paydate " 
sql = sql & ", bankslip " 
sql = sql & "order by invoicenumber desc"

OK, so now that the SQL is more readable.... *finaly*

I can see your problem, the IIF structure in itself is perfect, but you cannot mix the Sum and non-Sum stuff together like that.

Your easiest and safest bet is to make a subquery....

Select <your output fields here, including IIFs>
from ( select ... bla bla ... Sum(value_OS) as SumValueOS from ... etc...)

 

[Pat Hartman]

You probably don't want to hard-code selection criteria such as dates.

,iif( (type<2) and (t_date>#" & [Enter date as mm/dd/yyyy] & "#) "

I think (but don't beat me up if this doesn't work) that if you use quotes instead of pound signs, you can enter the dates in the European format of dd/mm/yyyy.

,iif( (type<2) and (t_date>""" & [Enter date as dd/mm/yyyy] & """) "

 

[namliam]

I think (but don't beat me up if this doesn't work) that if you use quotes instead of pound signs, you can enter the dates in the European format of dd/mm/yyyy.

,iif( (type<2) and (t_date>""" & [Enter date as dd/mm/yyyy] & """) "

Dont think so Pat, Access SQL just requires US format (unfortunatly)

 

[Pat Hartman]

Obviously, I don't have an issue with this because I use dates in the correct format. So I will defer to your battleground knowledge on this subject. One thing I did find is that if you use the medium date format - DD-MMM-YYYY, people on both sides of the pond can read the date. I did this for an application that was being used in the UK and in the US so as to avoid the ambituity.

 

[namliam]

Yes the DD-MMM-YYYY format wil work in most cases...

Except where i.e. in dutch it is MAA for Maart and in english it is MAR for March

Tho 99.999% of the dutch will understand MAR to be March the DB doesnt understand MAA to be MAR
This often times leads to problems with dutch systems exporting MAA instead of MAR and then people dont understand why other (international) systems dont understand that to be March

Just one of many sources of my dealy struggles.