Dynamically fill a text box on a report (1 Viewer)

[KelMcc]

OK, I call a report, RSR, from form FISReports.

On the form, you can choose the date range (not optional), a specific individual ("BA", optional), and the status (optoinal) of the projects you want to report on.

The button that runs the report has the following code to filters the optional choices:

Private Sub RunRepSRnon_Click()
On Error GoTo Err_RunRepSRnon_Click

Dim stDocName As String, stDocWhere As String

stDocName = "RSR"

If Not IsNull(Forms![FISReports].BA) And Not IsNull(Forms![FISReports].Status) Then
stDocWhere = "([Status].TOpCl) = '" & Me.Status & "' And[BA] = '" & Me.BA & "'"
ElseIf Not IsNull(Forms![FISReports].BA) Then
stDocWhere = "[BA] = '" & Me.BA & "'"
ElseIf Not IsNull(Forms![FISReports].Status) Then
stDocWhere = "([Status].TOpCl) = '" & Me.Status & "'"

End If

DoCmd.OpenReport stDocName, acPreview, , stDocWhere

Exit_RunRepSRnon_Click:
Exit Sub

Err_RunRepSRnon_Click:
MsgBox Err.Description
Resume Exit_RunRepSRnon_Click

The Report itself then uses a query, QSRReport, to pull in the actual data. Here's the query:

SELECT Status.TOpCl, SR.BA, SRComment.Comment, SRComment.Date, SR.Product, SR.Releasenum, SR.ProjectSR, SR.SRNum, Status.TOpCl, SR.SRTitle, SR.SRDescription, SR.SRType, SR.CurStat, SR.Requestor, SR.DepRequesting, SR.DtSubmitted, SR.DtResolved, SR.RqStdCompDt
FROM Status INNER JOIN (SR INNER JOIN SRComment ON SR.SRNum = SRComment.SRNumb) ON Status.Status = SR.CurStat
WHERE (((SRComment.Date) Between [Forms]![FISReports]![From Date] And [Forms]![FISReports]![To Date]) AND ((SR.SRType)<>"Project" Or (SR.SRType) Is Null));

The problem I'm up against now is I need to specify on the report what status its for: Open, Closed, or All. Now, if the report is open or closed, specifically, I can populate the text field I'm using as a title w/ "Open" or "Closed" based on one of the fields that's being pulled in. No problem.

The remaining issue is that I can't figure out how to get it to populate "All". The code selects "All" based on the user NOT populating the status field. So, on the report end, there's nothing for me to key on to say label the report "All."

I hope this is enough to get going on. I'm sure there's some obvious way to do this, I just haven't had my "duh" moment yet.

If I can provide more info, please say so.

Thanks for whatever help anyone can provide.

 

[jtvcs]

Don't know if this helps but...I usually assign dynamic titles to textboxes on a report with something like this...Control Source property = "Applies to: "&[Forms]![frmQueryResponses]![WhichUsers]. HTH or sends your mind into adapting it to your situation. JT

 

[KelMcc]

OK, I put this in the control source field for the textbox on the report:

Applies to: &[Forms]![FISReports]![Status]

and I get the error message: "Syntax error in query expression"

FYI, from what you suggested, jtvcs, I put the name of the form supplying the dynamic variable in the 2nd bracket and the name of the combo box that supplies the dynamic variable in the 3rd bracket.

Was I supposed to put this somewhere else? Or is it just a matter of me not putting it in quite correctly?

Not that it matters, but I'm using Access XP.

 

[jtvcs]

You must start with = "Applies to:" &[Forms]![FISReports]![Status] --- you missed the equal sign and the necessary quotes. Give it a try. HTH JT

 

[KelMcc]

Hey! Nice! Thanks.

Now, if you want to help me w/ a slight complication.

One of the options the user has is to leave the field blank. How do I make it so that a blank field on the form will cause the word "All" to appear in the report text?

Thanks again.

 

[Fizzio]

in the controlsource of the control, try

=iif(isnull([YourField],"All",[YourField])

 

[KelMcc]

=iif(isnull([YourField],"All",[YourField])

OK, tried this:
=IIf(isnull([Forms]![FISReports]![Status],"All",[Forms]![FISReports]![Status])

got a "wrong number or arguments" error

So, I thought it was missing a parenthesis.

=IIf(isnull([Forms]![FISReports]![Status],"All",[Forms]![FISReports]![Status]))

No, same error.

Ok, maybe this?:

=IIf(isnull[Forms]![FISReports]![Status],"All",[Forms]![FISReports]![Status])

Got invalid syntax error.

 

[KelMcc]

Oops, OK, upon closer inspection, do you mean to put that code into the control source of combo box on the form? The one that the report uses?

If so, then disregard my previous post, I'll try this. However, I'm concerned this will pass on a value "All" to the query, which will make it not work.

 

[Fizzio]

sorry, try this in the control on the report (brackets missing!!!)

=iif(isnull([YourField]),"All",[YourField])

 

[KelMcc]

Huzzah!

Again, I say, Huzzah!

Thank you both for your help.

Problem Solved.