Last If Statement in the Detail Section Only Being Read

[mizenerman]

I have a report that is populated from a table using a query. On the report is the following bound controls:

Name
MRN
MD

The following is unbound:

PathDateMS

In the unbound field I'm successfully populating it with data from two hidden controls on the report (combining them to populate the the unbound field) using the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me![AddBiopsyMS]) Then
Me![PathDateMS] = ""
Else
Me![PathDateMS] = [BiopsyDate] & " - " & [BiopsyMS]
End If
If IsNull(Me![AddBiopsyMS2]) Then
Me![PathDateMS] = ""
Else
Me![PathDateMS] = [BiopsyDate2] & " - " & [BiopsyMS2]
End If
End Sub

The issue becomes if the AddBiopsyMS field is not null for one record and the AddBiopsyMS2 field is not null only the records for the AddBiopsyMS2 field appear on the report. The code is ignoring the first If and only reading the last If statement. My questions are: Is what I'm attempting to do possible?

Obviously my code structure is the issue but where I'm I failing?

Thank you in advance for your assistance.

 

[HarleyQuinn]

At the moment if they are both not null then the second If statement will overwrite the value in [PathDateMS].

What do you want to appear in that field if neither is NULL?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[mizenerman]

HarleyQuinn,

Thanks for your reply. I neglected to mention that only one will be null. Using a check box on the form the user is making a choice as to which one they would like to appear on the report.

 

[HarleyQuinn]

How does this

if the AddBiopsyMS field is not null for one record and the AddBiopsyMS2 field is not null

occur then?

If ONLY ONE can be NULL then this should work (providing the user hasn't selected then deselected an option, then it would be True/False as the control can't be NULL anymore):

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me![AddBiopsyMS]) Then
Me![PathDateMS] = [BiopsyDate2] & "  -  " & [BiopsyMS2]
Else
Me![PathDateMS] = [BiopsyDate] & "  -  " & [BiopsyMS]
End If
End Sub

You can debug the code to check, the value for the Checkbox is going to be either Null (obviously NULL, 0 - False or -1 - True).

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[mizenerman]

That was what I needed HarleyQuinn. Sorry about the typo but you figured it out regardless. Thanks again.

 

[HarleyQuinn]

Glad I could help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[dhookom]

Why even use any code. Can't you simply set the control source of PathDateMS to:

=IIf(IsNull([AddBiopsyMS]),[BiopsyDate2] & "  -  " & [BiopsyMS2], [BiopsyDate] & "  -  " & [BiopsyMS])

Duane
Hook'D on Access
MS Access MVP

 

[HarleyQuinn]

Good catch Duane.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[mizenerman]

Here's a closing thought. What if the user had an option to select more than two? For example:

AddBiopsyMS
or
AddBiopsyMS2
or
AddBiopsyMS3

What if one of those 3 were not NULL?

 

[dhookom]

It looks like you would need to normalize your table structure so you didn't have 3 similar fields. I could be wrong but this doesn't look relational.

Duane
Hook'D on Access
MS Access MVP