Access Report - Pulling incorrect data from Control Source expression

Question

I built an Access Database from scratch, and I’m trying to build a useful report for my team. In the Real Estate world, one report we order is called a “Phase I Report”. If that report comes back clean, no further reports are needed. If contamination is found, a “Phase II Report” must be ordered and completed. I have built in a Yes/No checkbox in the table to mark if a Phase II Report is needed. I’ve inserted a clip of the Due Diligence table that the report is drawing from below:

I built a simple report showing the Phase I Report Received, a check box for if the Phase II Needed? was yes or no, and the Phase II Report Received fields (see below):

There was some confusion on whether we were still waiting for a Phase II Report, or if it wasn’t required, so I wanted to build an If/Then statement to have the Phase II field state “N/A” if the checkbox was not marked. On the report, I selected the field for Phase II Report Received and tried to change the Control Source to make this work. I used the statement below:

=IIf([Phase II Needed?]=False And Not IsNull([Phase I Report Received]),"N/A",[Phase II Report Received])

It kind of worked, except that the dates it returned for the “False” portion of the equation (should have been [Phase II Report Received]) were identical to the ‘Geotech’ dates. The image below shows the results after I changed the Control Source to an expression. You can see that the dates don’t match the previous image.

How in the world is this happening?? I’ve troubleshooted everything I can think of. Anyone have a better suggestion of how I can accomplish this “N/A” insertion? Or how I can fix this problem? Thank you so much!! It’s driving me insane.

Answer 1

I can't explain the behaviour you are experiencing on the basis of the information available, but I would suggest binding the control to the Phase II Report Received column, and then superimpose an unbound text box control on it, making sure the BackStyle property of each is set to Transparent.  Set the ControlSource property of the unbound control to the following expression:

    =IIf(Not [Phase II Needed?] And [Phase I Report Received] Is Not Null,"N/A",Null)

As only one control or the other can have a value, that with a value will show.

Answer 2

Beautiful!!!! You just solved two days of headaches in two minutes! I just started working with Access a couple weeks ago, so I hadn't even heard of an unbound text box. That will come in handy!

Thank you so much, Ken.