I am trying to create a report but need help with one aspect.

There are two fields - Programmed Commencement Date and Actual Commencement Date. I want the query/report to show the Actual Commencement Date but sometimes this is null and the Programmed Commencement Date needs to show in the report.

What I really need is very basic instructions on this as I'm fairly new to databases and don't have much SQL experience.

Please help!

Hi,

   Put both fields on the report, and set them both to visible = No. Lay them right on top of each other.

Then put code in the Details On Format event like this:

Me.Actual Commencement Date.Visible = False
Me.Programmed Commencement Date.Visible = False

If IsNull(Me.Actual Commencement Date) Then
   Me.Programmed Commencement Date.Visible = True
   Else
   Me.Actual Commencement Date.Visible = True
End If

If your report is not too large (many hundreds of records) this will work pretty quickly.

P.S. Those are bad field names. They should be something like:
ProgCommDate
ActCommDate
with Captions that have the full name. This code may be kludgy since there are spaces in the field names. A big no-no. Date is also a reserved name, so be careful.

HTH



mmcdonal

Another approach would start with you putting both dates and making them invisible like mmcdonal said, but then leave them be. Put a third visible, but UNbound, textbox (call it, say CommencementDate) on there and in the report's detail section's ON FORMAT event you put this simple one line.

Me.CommencementDate = Nz(Me.[Acutal Commencement Date], Me.[Me.Programmed Commencement Date])

You can also forget the ON FORMAT event code and put

= Nz(Me.[Acutal Commencement Date], Me.[Me.Programmed Commencement Date])

directly into the textbox's controlsource in the report's design view.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I love finding more elegant ways to do things on this site. I like the last one best as it is probably the fastest.

mmcdonal

Thanks to both of you for your help - I'll go and test it out!