Bound control based on calculated control

[Eljefegeneo]

I have a calculated control [DiffDate] on the Main form whose calculation uses the value from a calculated control [LastContactDate] on a Subform. This works fine. I want the control [DiffDate] to populate a bound control [Aging1]. If [DiffDate] is less than 365 then [Aging1] = "A", if between 366 and 719 then ' "B", else "C". I can update the [Aging1} control by using the On Focus event on [DiffDate]. But I cannot figure out how to do it on Form Load, Form Open, Form Close, etc. Or even on the change of the date control that is used to calculate [LastContactDate]. When I open the form or go to a new record, I see the [DiffDate] control change from the number 41312 to the correct number of days between today and the last contact.
I do not necessarily need it to be updated immediately, that is when the current record is displayed, but it should be updated. The [Aging1] control doesn't need to be updated unless there is a change.
Is there any way of doing this?

 

[MarkK]

Data is like meat: Store it raw, and cook it just before you consume it. Always avoid storing the result of a calculation.

A good time in the lifetime of a form to display information about the current record is the Form_Current event, which fires every time the form loads a new record. So if you have a calculation you need to perform, like an age calculation from a birthdate in the current record, you'd do something like ...

Private Sub Form_Current()
[COLOR="Green"]  'calls the Age Calc function using the current record's birthday
  'and displays it for the user at retrieval time[/COLOR]
  Me.tbUnboundAge = AgeCalcFunction(Me.tbDateOfBirth)
End Sub

Store it raw. Process it when you need it.

 

[Eljefegeneo]

Thanks for the quick reply. I do understand that a calculation should be in a query, not a table, but the problem I am having is that the calculations are so complex, at least for me, that I cannot figure out how to properly code them every time I run a query.
I have a tblMain which contains all of my names and addresses, etc. Then I have a related table tblSalesContacts, with all contacts of these records by date. What I want to do is code the query so that I can tell with whom I have had contact in the last year, last two years, and others. Three codes, A, B and C. I call this [Aging].
FrmMain comes from tblMain and frmSalesContactsSub comes from tblSalesContacts. Thus there can be only one record in tblMain, but could be many records in tblSalesContacts. I figured out how to extract the last date of contact using Max([DateOfContact]). I then inserted in frmSalesContactsSub an calculated field [LastContactDate] code = IIf([DateOfContact]="" Or IsNull([DateofContact]),[Forms]![frmMain].[DateEntered],Max([DateOfContact])), which would give me the last date of contact OR the date of the entry of the data on tblMain. So if there was no record in tblSalesContacts for a related record in tblMain, the last contact date would be the date the original record in tblMain was entered [DateEntered]. Otherwise it would be the last contact in tblSalesContacts.
Then I calculated the difference in Days, from which I could get my three codes, A, B and C.
However, I do realize that it would be better to use a query. So I tried to figure it out, but it keeps telling me that I cannot have criteria for the calculated field. Or update based on a criteria for a calculation. Or at least it is telling me that my coding is wrong. When in the query I use the code of LastDate: IIf([DateOfContact]="" Or IsNull([DateofContact]),[Forms]![frmMain].[DateEntered],Max([DateOfContact])) it tells me that "Cannot have aggregate function in WHRERE clause (Max([DateOfContact])). This IIf statement works in the form, but not in the query.
What I can't figure out is how to code the query so that:
1. If there are values in [DateOfContact], the code Aging would be based on the DateDiff of MAX([DateOfContact]) date of Contact and today. One, two, or three or more years.
2. If there are no values in [DateOfContact] and the DateDiff of[DateEntered] more than two years, then the Aging is C. If it is between one year and two years it is B and less than one year ( a new entry) the it is A.
Although I think I have explained it clearly, I realize it may be confusing. For that I apologize. But I think you can understand what I am trying to do.

 

[spikepl]

I tried to understand what you want but gave up, not being very patient. Your issue is lost somewhere in the many details that totally obscure the objective. This is like trying to juggle an imaginary Rubik's cube.

What tables do you have with which data? Give an example. A screenshot, an Excel spreadsheet...


What data is to be produced based on that data? Give an example and comment. Make clear brief points, each single point addressing one aspect only.

 

[Eljefegeneo]

There are two tables:
tblMain
ClientID
DateEntered
Aging

tblSalesContacts
ContactID
ClientID
DateOfContact

I define "contact" below as either the last DateOfContact, or if that is null, then DateEntered.
Relationship is by the ClientID
Requirement: Aging is either "A", "B" or "C" categories.
"A" contact within the last year
"B" contact between year one and year two (366 to 720 days)
"C" contact older than two years (>720 days)
This is from the [DateOfContact] on tblSalesContacts

If [DateOfContact] is null, then the [DateEntered] from tblMain is used instead of the [DateOfContact] from tblSalesContacts

Need a query to extract names for a mail merge that satisfy Aging A or B Or C , or A and B together.

 

[MarkK]

So tblMain. Is 'aging' the result of a calculation, and is 'DateEntered' the day the calculation was made? If so, none of that is raw data, so we ignore it because it is already out of date.

Is tblSalesContact raw data, an actual record of real-world measurable events? That's what we use. Presumably those are all your contacts dates for all your clients, so there are probably many ContactDates for each client and we want only the latest? OK. And we can calculate how many years ago that last contact date was too, using SQL like ...

SELECT ClientID, Max(ContactDate) AS MaxDate, DateDiff("yyyy",Max(ContactDate),Date()) AS YearsDiff
FROM tblSalesContacts
GROUP BY ClientID;

... so one record per contact showing the latest contact date, and how many years ago that was. Getting closer...

 

[Eljefegeneo]

Thank you for your reply. I almost have the problem solved, but there is still one thing I just cannot figure out. The following code will give me the "Aging" that I want, but the results give me multiples of the same ClientID if there is more than one contact with the client. If there is a [DateOfContact], then I only want the last one, not all. The data of DateEntered from tblMain is needed in case there has not been any contact with the "client" even though they really aren't a "client', but rather a prospect – just some internal nomenclature. Thanks.

  SELECT tblMain.ClientID, tblMain.DateEntered, Max(tblSalesContacts.DateofContact) AS LastDate, IIf(IsNull([DateOfContact] Or [DateOfContact]=""),[DateEntered],Max([DateOfContact])) AS LastContactDate, DateDiff("d",[LastContactDate],Date()) AS DiffDate, IIf([DiffDate]<365,"A",IIf([DiffDate] Is Null,"C",IIf([DiffDate]="","C",IIf([DiffDate]>720,"C","B")))) AS Aging
  FROM tblMain LEFT JOIN tblSalesContacts ON tblMain.ClientID = tblSalesContactsFinal.ClientID
  GROUP BY tblMain.ClientID, tblMain.DateEntered, tblSalesContacts.DateOfContact
  ORDER BY tblMain.ClientID;

 

[Eljefegeneo]

Corrected code, made a mistake:

  SELECT tblMain.ClientID, tblMain.DateEntered, Max(tblSalesContacts.DateofContact) AS LastDate, IIf(IsNull([DateOfContact] Or [DateOfContact]=""),[DateEntered],Max([DateOfContact])) AS LastContactDate, DateDiff("d",[LastContactDate],Date()) AS DiffDate, IIf([DiffDate]<365,"A",IIf([DiffDate] Is Null,"C",IIf([DiffDate]="","C",IIf([DiffDate]>720,"C","B")))) AS Aging
  FROM tblMain LEFT JOIN tblSalesContacts ON tblMain.ClientID = tblSalesContacts.ClientID
  GROUP BY tblMain.ClientID, tblMain.DateEntered, tblSalesContacts.DateOfContact
  ORDER BY tblMain.ClientID;

 

[Eljefegeneo]

After working on this for three days, I think I may have found the solution. I created a second query that used the aging criteria from the first query [qrySelectLastDate1] that I displayed above. The second query is:

  SELECT tblMain.ClientID, Last(qrySelectLastDate1.Aging3) AS LastOfAging3
  FROM tblMain INNER JOIN qrySelectLastDate1 ON tblMain.ClientID = qrySelectLastDate1.ClientID
  GROUP BY tblMain.ClientID
  HAVING (((Last(qrySelectLastDate1.Aging3))="A" Or (Last(qrySelectLastDate1.Aging3))="B"))
  ORDER BY tblMain.ClientID;
   
  The key to this whole exercise seems to be the use of the "Last" criteria.  What I would like to know is if I am doing this the correct way or some convoluted way. That is, have I solved it the hard way or the easy way?  If there is an easier way, what is it?  And thanks for all the help.  It did prod me on to try to solve this.