Report and IIf problem I think

[bacalaoyalcapurria]

Hello Folks,

I created this function,

Referenced_Document_Id: IIf([tblReference].[Referenced_Document_Id]," ","No References Found",[tblReference].[Referenced_Document_Id]

In other words, I'd like for access to add "No References Found" whenever a record from the tblStatutes has not references in the tblReferences when running a report.


Currently, the report looks like

Statutes.Document_Id Reference.Referenced_Document_Id
123 5656
00.236
01.237 236589

I'd like for the report to look like

Statutes.Document_Id Reference.Referenced_Document_Id
123 5656
00.236 No References Found
01.237 236589

Does the above function needs to be added to the query or to the report?
I added it to the query portion as an Expression Builder, and it's giving me an error.

Please, any suggestions or idea would be appreciated.

Thanks,

Rita

 

[belovedcej]

I would put it in the report. It will need an = before it. And you'll need to add a parenthese at the end. But you could put it in the query, too. I don't use the builder, so I'm not sure why you're getting an error, unless it's because you missed the closing parenthese.

I just go into the field name and put

MyNewFieldName: IIf(expression)

=IIf([tblReference].[Referenced_Document_Id]," ","No References Found",[tblReference].[Referenced_Document_Id])

 

[bacalaoyalcapurria]

I went to the report session under the Referenced_Document_Id control source and added

=IIf([tblReference].[Referenced_Document_Id]," ","No References Found",[tblReference].[Referenced_Document_Id])

I get the following error, "The expression you error has a function containing the wrong number of arguments"

 

[Remou]

I count four arguments, IIf only has three.
Do you mean:
[tt]=IIf(Trim([tblReference].[Referenced_Document_Id]) & "" = "","No References Found",[tblReference].[Referenced_Document_Id])[/tt]

 

[bacalaoyalcapurria]

This is getting a little more complicated than what I thought. I added the function to the query portion using the expression builder and it works fine when I run the query. But when I go to the form to view reports, it does not work anymore. Do I need to incorporate that function within the existing code and not within the query or report?

Here is the code that is within the cmdEnter button

Private Sub cmdEnter_Click()

Dim strWhere1 As String
Dim strReportName As String

strReportName = "rptmain"

'Check Requirements
If IsNull(Me.txtDate1) And Not IsNull(Me.txtDate2) Then
MsgBox "Enter a start date", vbExclamation
Exit Sub
ElseIf Not IsNull(Me.txtDate1) And IsNull(Me.txtDate2) Then
MsgBox "Enter an end date.", vbExclamation
Exit Sub
End If

'Date Check
If IsNull(Me.txtDate1) And IsNull(Me.txtDate2) Then
strWhere1 = ""
Else
'strWhere1 = "[tblStatutes.Last_Reviewed_Date] Between #" & Me.txtDate1 & "# And #" & Me.txtDate2 & "#"
strWhere1 = "[tblStatutes.Last_Reviewed_Date] Between #" & Me.txtDate1 & "# And #" & Me.txtDate2 & "#" & " OR " & "[tblStatutes.Effective_Date] Between #" & Me.txtDate1 & "# And #" & Me.txtDate2 & "#"



'strWhere1 = "tblStatutes.Last_Reviewed_Date Between #" & Format(Me!txtDate1, "yyyy-mm-dd") & "# And #" & Format(Me!txtDate2, "yyyy-mm-dd") & "#"
End If

'Debug.Print strWhere1

'Combo 1
If IsNull(Me.cboType) Then
strWhere1 = strWhere1
Else
If strWhere1 = "" Then
strWhere1 = "[tblStatutes].[Type] = '" & Me.cboType & "'"
Else
strWhere1 = strWhere1 & " AND [tblStatutes].[Type] = '" & Me.cboType & "'"
End If
End If

'Debug.Print strWhere1

'Combo 2
If IsNull(Me.cboFunction) Then
strWhere1 = strWhere1
Else
If strWhere1 = "" Then
strWhere1 = "[tblStatutes].[Function_Type] = '" & Me.cboFunction & "'"
Else
strWhere1 = strWhere1 & " AND [tblStatutes].[Function_Type] = '" & Me.cboFunction & "'"
End If
End If

'Combo 3
If IsNull(Me.cboExemption_Status) Then
strWhere1 = strWhere1
Else
If strWhere1 = "" Then
strWhere1 = "[tblStatutes].[Exemption_Status] = '" & Me.cboExemption_Status & "'"
Else
strWhere1 = strWhere1 & " AND [tblStatutes].[Exemption_Status] = '" & Me.cboExemption_Status & "'"
End If
End If

Debug.Print strWhere1

On Error Resume Next
DoCmd.OpenReport strReportName, acViewPreview, , strWhere1

Me.txtDate1 = Null
Me.txtDate2 = Null
Me.cboType = Null
Me.cboFunction = Null
Me.cboExemption_Status = Null

End Sub

 

[bacalaoyalcapurria]

Folks,

Don't worry. I figured it out.

Thanks for all your help and quick responses.

Rita

 

[Remou]

Can you post the SQL for your query?

 

[Remou]

Oops. Our posts crossed in the mail.