IIF Statement

[Thumper]

I have a form that request the user to enter up to five
job numbers. The query is tied to a report. I am using
the following syntax to list the job names on the report,
however, only the first one seems to be working. (I.E
the IIF statement does not appear to be working). When I
execute the query manually, it does appear to be working
properly.

Any idea why?

=(IIf([jobno]=[Forms]![frmFPISelect]![txtjob1],[job
title]))
=(IIf([jobno]=[Forms]![frmFPISelect]![txtjob2],[job
title]))
=(IIf([jobno]=[Forms]![frmFPISelect]![txtjob3],[job
title]))
=(IIf([jobno]=[Forms]![frmFPISelect]![txtjob4],[job
title]))
=(IIf([jobno]=[Forms]![frmFPISelect]![txtjob5],[job
title]))

Example of Report (if 5 job numbers entered in form):

Job Title 1
Job Title 2
Job Title 3
Job Title 4
Job Title 5
..

 

[Pieter Wijnen]

Assuming You have 5 Labels (!) in The Reports PageHeader
I'd suggest you name them the same as the controls on the Form
and put the following code in the report's page_header_format Event
(using labels u can also put in the Report_Open Event - btw)

Sub PageHeader_Format(FormatCount As Integer, Cancel As Integer)
Dim Frm As Access.Form
Dim Ctl As Access.Control
Dim i As Integer

If FormatCount > 1 Then Exit sub 'Don't need to Repeat it
Set Frm = Access.Forms("frmFPISelect")
For i = 1 To 5
Set C = Frm.Controls("txtJob" & i)
Me.Controls(C.Name).Caption = C.Value
Next
End Sub

HTH

Pieter