Show alternative text if form is null

[Hoving]

When a user enters a start and end date on a form (into txtStartDate and txtEndDate), a report opens and an unbound text box shows the parameter used.

The control source for the text box is:

="Date Range: " & [txtStart] & " to " & [txtEnd]

where txtStart is a hidden control on the report set to:

=Forms.frmnuReportSelect.txtStartDate

and txt End is a hidden control on the report set to:

=Forms.frmnuReportSelect.txtEndDate

I would like to have alternative text show if the txtStartDate and txtEndDate controls on the form are null (i.e., the user didn't specificy a date range). The text could say something like:

"Date Range: All dates."

The current syntax I've tried results in:

"Date Range: to"

Current syntax:

=IIf(IsNull(Forms![frmnuReportSelect!txtStartDate]),"Date Range: All Dates","Date Range: " & [txtStart] & " to " & [txtEnd])

How chould I edit this, or would it be better to make two text boxes and set their visible property according to the values passed from the form?

Thanks! - Kurt

 

[KenReay]

Hi

=Nz(Forms.frmnuReportSelect.txtStartDate,"WHATEVERYOUWANTINHERE&quot

should do the trick



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Hoving]

> =Nz(Forms.frmnuReportSelect.txtStartDate,"WHATEVERYOUWANTINHERE&quot

> should do the trick

That does produce a message if txtStartDate is null, but how do I integrate that into the larger syntax statement?

In other words . . .

If txtStartDate is null, show "WHATEVERYOUWANTINHERE", otherwise, show "Date Range: " & [txtStart] & " to " & [txtEnd].

Thanks.

Kurt

 

[KenReay]

Hi

"Date Range: " & Nz(Forms.frmnuReportSelect.txtStartDate,"WHATEVERYOUWANTINHERE&quot
& " to " & Nz(Forms.frmnuReportSelect.txtEndDate,"WHATEVERYOUWANTINHERE&quot



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Hoving]

> "Date Range: " & Nz(Forms.frmnuReportSelect.txtStartDate,"WHATEVERYOUWANTINHERE&quot
> & " to " & Nz(Forms.frmnuReportSelect.txtEndDate,"WHATEVERYOUWANTINHERE&quot

Thanks. Getting closer. This preserves a " " to " " format in either a null or not null scenario, so I could have it say something like "Date Range: None to None" when the values are null. However, I would prefer to replace the " " to " " format entirely if txtStartDate and txtEndDate are Null, and have it say something like "Date Range: All Dates."

Anyway I can edit the syntax to allow for this?

Thanks again. - Kurt

 

[Hoving]

Got it working. I ended up using this syntax . . .

=IIF([txtStartDate] is null and [txtEndDate] is
null,"All Dates", "Date Range: From "& [txtStartDate]
& " To " & [txtEndDate]

. . . combined with two hidden text boxes on the report (txtStartDate and txtEndDate), with a Control Source of =Forms.frmnuReportSelect.txtStartDate and
=Forms.frmnuReportSelect.txtEndDateDate, respectively.

- Kurt