How to pull one max date from the value in four different text boxes on cont. subform

[JeffBarker]

Hi Jeff,

Here are my 2 cents.

Your question would have been easy: the codes would have looked like

If Nz(Me.subform!Email1) = "" Then
  TextBox = "Not chased yet"
Else
  If Nz(Me.subform!Email2) = "" Then
    TextBox  = "Last Chased: " & Me.subform!Email1
  Else
    If Nz(Me.subform!Email3) ="" Then
      TextBox  = "Last Chased: " & Me.subform!Email2
    Else
      If Nz(Me.subform!Broadcast) ="" then
        TextBox  = "Last Chased: " & Me.subform!Email3
      Else
        TextBox  = "Broadcast: " & Me.subform!Broadcast
      End If
    End If
  End If
End If

Until you mentioned that this sub form is CONTINUOUS!

Now, where is this unbound textbox? If it is also on the sub form (did you say this is hidden?), you can write a very long IIF statement in the control source of Textbox using the above logic, i.e.,

Enter in the control source of the textbox

= IIF(Nz([Email1])="","Not chased yet",
IIF(Nz([Email2])="", "Last Chased: " & [Email1],
IIF(Nz([Email3])="", "Last Chased: " & [Email2],
IIF(Nz([Broadcast])="", "Last Chased: " & [Email3],
"Broadcast: " & [Broadcast]
))))

I hope I understood your question correctly, but if not, will try again.
Good luck.

Shoji

Hey Shoji,

Thanks for the suggestion - it's on the right track, but I have a couple of examples I am working on where we have a date in [Broadcast] and[Email1], [Email2] and [Email3] are empty, but the text box with your code above is showing "Not chased yet".

 

[JeffBarker]

What step specifically are you having trouble with? Post your attempt at putting it together and we'll sort it out.

Hi Paul,

Thanks for the offer - I've copied the Min/Max function into a new module in my database, and am thinking about loading the values of my date fields in to it via the OnCurrent event of the subform, but as Shoji has pointed out about the continuous nature of the sub, will this be the correct method to approach the function?

 

[shoji]

Hi Jeff,

Oh, I assumed that all the other email dates are in before "Broadcast" is entered. If Broadcast has a date while all or any others are empty, you can easily amend the IIF statement. But if any email date fields may be empty without any order, it is a bit messy.

How about checking the date fields the other way?

= IIF(Nz([Broadcase])<>"","Broadcast: " & [Broadcast],
IIF(Nz([Email3])<>"", "Last Chased: " & [Email3],
IIF(Nz([Email2])<>"", "Last Chased: " & [Email2],
IIF(Nz([Email1])<>"", "Last Chased: " & [Email1],
"No Chase Sent Yet"
))))

Because you have only 4 fields, I am trying to avoid more involved Max function. Brute force will do here.

 

[JeffBarker]

Because you have only 4 fields, I am trying to avoid more involved Max function. Brute force will do here.

Shoji, you are a genius!! Thank you so much! I have tried this with lots of different combinations of the four fields being blank or filled in, and it works!

Thanks everyone for your input!!!

 

[JeffBarker]

Hi Jeff,

Oh, I assumed that all the other email dates are in before "Broadcast" is entered. If Broadcast has a date while all or any others are empty, you can easily amend the IIF statement. But if any email date fields may be empty without any order, it is a bit messy.

How about checking the date fields the other way?

= IIF(Nz([Broadcase])<>"","Broadcast: " & [Broadcast],
IIF(Nz([Email3])<>"", "Last Chased: " & [Email3],
IIF(Nz([Email2])<>"", "Last Chased: " & [Email2],
IIF(Nz([Email1])<>"", "Last Chased: " & [Email1],
"No Chase Sent Yet"
))))

Because you have only 4 fields, I am trying to avoid more involved Max function. Brute force will do here.

Shoji, apologies, one final piece of the puzzle - how do I show/hide this text box based on the value of another text box on the subform record, please?

 

[shoji]

Hi Jeff,

I can think of 2 ways to do that.
One is just to make Textbox null. If this is good enough, it is simple: you just edit the IIF statement.

=IIF([NewTextBox]<>"xxx", "", <the original IIF statement here>)


The second way is to use the Conditional Formatting.It works this way: if the condition is not met, the textbox's color(frame and text) will turn to exactly the same as the background of the form so you cannot see anything. If you want to use this, you can Google about it and study how to use it.

Since this TextBox seems to be just for a notification, I would make the text bold without any frame so when it has nothing in it, it becomes indistinguishable from the background but when it has a text it will stand out.

Shoji