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

[JeffBarker]

Hi guys!

I'm hoping someone will be able to assist me in providing a piece of code in order to achieve the following, please!

We're looking at a way that we can easily display what stage our clients' email marketing campaigns are at - in one section of our CRM our Campaign Manager will enter information on whether the campaign has broadcast (ie: emailed out to the required circulation list(s)), if we're waiting for artwork from the client or if we're chasing for that information.

I have four text boxes (date format) hidden on a subform that I need to pull ONE max date from.

So, as an example:

Email1 (our first email to the client chasing for artwork) = 01/01/2015
Email2 (our second email to the client chasing artwork) = 08/01/2015
Email3 (our third chase email) = 15/01/2015
Brodcast (the date the email campaign was finally sent) = 29/01/2015

I would need some code to show in an unbound textbox "Broadcast: 29/01/2015"

But, on the other hand, if the dates looked like this:

Email1 = 01/01/2015
Email2 = 08/01/2015
Email3 = Null
Broadcast = Null

I would need some code to show the following in an unbound textbox "Last Chased: 08/01/2015"

So we can easily see the status of our marketing campaigns at any stage in the process.

I've tried using IF THEN ELSE statements in the subform's On Current event, but that populates the information from the record you've selected across all the other records on the sub as well.

So I'm wondering if it's worth setting the Control Source of the unbound textbox I want to display the campaign status in as a bunch of nested IIF statements instead? If there is, would anyone be able to help on the best practice here please?

I'd rather use code, so if anyone can help me to create a solution either way please, that would be excellent!

Thanks in advance!

Jeff

 

[pbaldy]

This help?

https://support.microsoft.com/en-us/kb/209857

 

[pbaldy]

By the way, the dates should probably be in a related table.

 

[JeffBarker]

By the way, the dates should probably be in a related table.

Hi pbaldy, thanks for the link - I'll take a look now!

The date fields are all in a table together, although not a separate table - they're housed with a bunch of other data in tblAllocation that deals with our Direct Marketing campaigns.

Cheers!

 

[pbaldy]

What happens when somebody decides they need a 5th date? Easy in a related table, not so much all in the same table.

 

[JeffBarker]

What happens when somebody decides they need a 5th date? Easy in a related table, not so much all in the same table.

We'll just add the new field in to the pre-existing table.

 

[vbaInet]

And if a 30th reminder needs to be sent? You'll add 27 more fields?

 

[JeffBarker]

And if a 30th reminder needs to be sent? You'll add 27 more fields?

Lol, I guess so!!

But seriously, three chases are all we are prepared to do for the artwork (so only need three chase date fields), and the next part of that process is to Broadcast.

 

[pbaldy]

And you'll have to redesign all forms and reports that display them, queries, etc. In a related table, the developer doesn't have to do anything, which is how it should be. Things like this shouldn't require a redesign of the database. More info here if you're interested:

http://www.r937.com/Relational.html

What you've got is basically the same as having Item1, Item2 in an orders table.

 

[pbaldy]

I guess I'll get out of the way.

 

[spikepl]

As the others have mentioned, comparing values in the same record is one of the surefire indicators of inappropriate data structure. It means that multiple items of the same kind got stored in one record.

Such items should be stored separately with information what they are. That would make many tasks easy, using the SQL tools designed for exactly that.

 

[vbaInet]

Lol, I guess so!!

But seriously, three chases are all we are prepared to do for the artwork (so only need three chase date fields), and the next part of that process is to Broadcast.

Just curious how you would keep handling that!

 

[JeffBarker]

I guess I'll get out of the way.

Yo, no need for that pbaldy - I appreciate the help, suggestions and feedback from you!

Also, thanks for the link about relational design...I'll definitely check that out when I get some time, be good to get some history on the subject.

I agree with (and welcome the idea of) anything that makes our databases run quicker and more efficiently, but half of my problem here is that most of the databases have been inherited from my predecessor, who didn't necessarily do things the correct way. He also liked to copy and paste a database and/or object each time he wanted to create something new...so we have databases with masses of objects that aren't actually needed and are just residue from previous copy/paste efforts, countless forms with piles of unnecessary text boxes, buttons, combo boxes and the like that have been hidden after a copy/paste job...it's a nightmare!

I've tried to clean up here and there and create newer, more streamlined versions of some of the forms and tables in our main database (the CRM), but as I have other duties outside of design it's hard to be able to assign time to renovation. Sadly, the term 'if it ain't broke...' definitely applies here!

There have been rumblings of a new CRM2.0, which will be a brand new database with only the objects and code in it that we need, but I've been here for five years and we are still talking about it!!

So when that eventually happens, I'll be reviewing every single aspect of the database, as well as trying to implement individual user front ends (right now, everyone is access the same Access Front End from our shared directory).

 

[JeffBarker]

Just curious how you would keep handling that!

How do you mean?

 

[JeffBarker]

As the others have mentioned, comparing values in the same record is one of the surefire indicators of inappropriate data structure. It means that multiple items of the same kind got stored in one record.

Such items should be stored separately with information what they are. That would make many tasks easy, using the SQL tools designed for exactly that.

Hi Spike, thanks for the input - sadly, I'm working on databases that have been implemented previously (without my input)! I'm all up for renovating where I'm able to, but most of my Database work is reactive as opposed to proactive due to other work duties!

 

[JeffBarker]

This help?

https://support.microsoft.com/en-us/kb/209857

Hey Paul (just seen your name in your sig) - this is really helpful, and I have copied the Maximum function into a new module, but how would I load the four dates from my continuous sub form into that function, and then add the correct text ("Last Chased:" or "Broadcast:" in front of the max date)??

Thanks in advance (again)!

 

[pbaldy]

That method would only find the max value. All the methods that jump into my head only work with normalized data.

I suppose you could add to that function a variable to hold the value of I and set it the same way the current value variable is set, so that at the end the variable contains 0-3, whichever is greatest. As long as you always pass them in order, you can test that variable and return the appropriate text.

 

[JeffBarker]

That method would only find the max value. All the methods that jump into my head only work with normalized data.

I suppose you could add to that function a variable to hold the value of I and set it the same way the current value variable is set, so that at the end the variable contains 0-3, whichever is greatest. As long as you always pass them in order, you can test that variable and return the appropriate text.

Hi Paul, thanks for the suggestion - would you be able to help me put that all together, please?

 

[pbaldy]

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

 

[shoji]

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