Error in rpt with no Data and IIF ? 2

[saustin]

Hi,
Have an A2k rpt based upon a query. The report has 3 header items which correspond to three query selections. The normal report and query should not produce any detail data (this is an exception report). Instead of producing the selection criteria in the header section am getting an "error" message. Is there any way around this ?
Used the =IIf(UCase([Please Enter User ID])>"",UCase([Please Enter User ID]),"&quot tip but to no avail. Really just want to show you when the report was run and of course the selection criteria.

Many thanks for any ideas, Steve.

 

[danvlas]

Suppose [Please Enter User ID] is a parameter in the underlying query, you could set the textbox to:

=UCase([Please Enter User ID])

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[saustin]

Hi Daniel,
Many thanks for the reply. That was the exact line in the text box that was producing the error
UCase([Please Enter User ID]) when there is no data. Put the IIF in there to work around this. Steve.

 

[GoDawgs]

Try making it <> instead of >...so this:

=IIf([Please Enter User ID]<>&quot;&quot;,UCase([Please Enter User ID]),&quot;&quot

Hope that helps.

Kevin

 

[saustin]

Thanks Kevin,
Both =IIf([Please Enter User ID]<>&quot;&quot;,UCase([Please Enter User ID]),&quot;&quot
and =IIf(UCase([Please Enter User ID]) is not null ,UCase([Please Enter User ID]),&quot;&quot
produced no difference, still shows an error.
Did a little debugging and the value is definitely Null. Perhaps I'm missing the understanding of what the report actually sees when the query has no data.
Steve.

 

[GoDawgs]

ah...try this:

=IIf(nz([Please Enter User ID])<>&quot;&quot;,UCase([Please Enter User ID]),&quot;&quot

sorry...should have thought of that last time...

Kevin

 

[CosmoKramer]

Set the control source of the text box in the report header to:

=UCase([Please Enter User ID])

In the OnFormat event of the report header place code like this to suppress the text box if the report has no data:

If Me.Report.HasData Then
   Me.txtEnterUserId.Visible = True
Else
   Me.txtEnterUserId.Visible = False
End If

Hoc nomen meum verum non est.

 

[GoDawgs]

CosmoKramer's right...somehow I missed the part about this happening when the report has no data.

 

[saustin]

Sorry Kevin, but no difference in the result (error still shows). Steve.

 

[saustin]

Hi CosmoKramer,
Brilliant solution getting rid of the error, but.... still want you to see the original query parameter you selected on the report (even if there is no data). For example, if you are querying all records entered by SYSADM from 1-1-2004 to 2-1-2004 we want to show (sarbannes oxley stuff) that there were none entered so the detail section will be empty but the header (with a time stamp) should show the User ID and data range selected which will suffice for reporting purposes. Steve.

 

[CosmoKramer]

Steve,

Query parameters are tough to deal with. Could you run the report through a command button on a form?? If you can, add text boxes to the form, one representing each parameter. Then, in your report header set the control source for those text boxes to their respective form text boxes.

Hoc nomen meum verum non est.

 

[saustin]

Agreed ! Am making the changes now. Will test and report back. Steve.

 

[CosmoKramer]

OK...One thing I forgot to add, an advantage if you can go this route is you don't have to deal with any HasData logic. The entries will appear in the header whether or not there is data....

Hoc nomen meum verum non est.

 

[saustin]

Many thanks CosmoKramer and Kevin,
Works like a charm, created a text box and two dtpicker controls for the dates. Modified the query and the report to use these and voila... happy Tuesday. Best Regards, Steve.