Solved IIF with multi-criteria - Trouble

[Ashfaque]

Hi,

I have an report called Invoice in which I have place a textbox in page footer which contains following IIF statement.

=IIf([Page]=1,"Continued Next Page..." & [Page]+1,IIf([Page]+1<[Pages],"Continued Next Page..." & [Page]+1,IIf([Page]=[Pages]-1,"Continued Next Page..." & [Page]+1," Last Page.." & [Pages])))

The purpose of the above conditional statement is to display blank line at bottom of report if the invoice data is appearing on one single page. And if there are more than one page of a invoice, it should say on first page bottom "Continued Next Page...2" and then "Continued Next Page...3" and on last page the text "Last Page.." Should appear.

I tried managing it but it is working with invoice that has multiple pages but for single page it displaying "Continued Next Page...2" while there is no second page..

Can someone please help?
Thanks,
Ashfaque

 

[Gasman]

You would need to test pagecount?
On the first page, it is always going to be 1. ?

 

[plog]

You're logic is too complex for this to be inline, it needs to go into a function. You've got 4 cases that you need to account for, trying to cram all of it into a deeply nested iif statement is just too much for a human to achieve.

So, build a function and break out the logic into as many steps as you need:

Function get_PageFooter(Page As Int, TotalPAges As Int)
    ' compiles what to show for page footer based on current page report is on and total pages of report

 Dim ret As String
    ' return value will hold what to display in footer

  ret = ""
  ' default is nothing--if it is just one page 
 
 iif(TotalPages>1) then
  ' multiple pages will determine what to show
  
  ... logic here



 End If   

get_PageFooter = ret

End

Above is just sample code, may not even be VBA, but it demonstrates what you need to do to achieve your logic.

 

[Ashfaque]

I know I made it complex. I need it to be short and clear. If only 1 page report then nothing to print at bottom. If pages more than 1 then start printing on first page "Continued Next Page...2" and then "Continued Next Page...3" and at last page "Last page..". Max 3 page report will be there. I need this using IIF function in text box...

Any other idea?

 

[Gasman]

Yes, post #2

 

[Steve R.]

Any other idea?

Use the MS Access case statement. Complex IF statements are a pain.

MS Access: Case Statement

This MSAccess tutorial explains how to use the Access Case statement with syntax and examples. The Microsoft Access Case statement can only be used in VBA code. It has the functionality of an IF-THEN-ELSE statement.

www.techonthenet.com

 

[Ashfaque]

Thanks Steve,

Below is working...

=IIf([Pages]=1 And [Page]=1,"",IIf([Page]=1,"Continued Next Page... " & [Page]+1,IIf([Page]<[Pages],"Continued Next Page... " & [Page]+1,"Last Page.. " & [Pages])))

Thanks all