I'm trying to create an unbound field on my main form which holds the latest [DateContacted] a person on our [Contacts] form has been contacted. The [DateContacted]dates are held in a subtable [tableContactDetails]. I have this in an unbound text box [LastContactDate] but it either gives me errors or name errors depending on where I put it. [ContactID] is the linking field between the two.

Any ideas?

=DMax("[DateContacted]","[tableContactDetails]","[ContactID] = #" & [Forms]![Contacts]![ContactID] & "#")

[This message has been edited by David R (edited 01-28-2002).]

Try:

=DMax("[DateContacted]","[tableContactDetails]","[ContactID] = " & [Forms]![Contacts]![ContactID])

Y'know, Jack, that would be frighteningly logical. I'll try that Monday when I get back into the office.

*sigh* This is what I get for staring at the database for a week straight (no, not just on this issue)

David R

The field calculates like a champ now. Thanks Jack for helping the blind to see.

However I cannot seem to sort by this field now (presumably because the field is not bound to the underlying table). The Sort Ascending/Descending buttons are greyed out and when I try to specify it in the Form's Order By property as Me.LastContactDate it tries to ask for a parameter.
If I try to set it to Forms!Contacts.LastContactDate it doesn't prompt for a parameter, but it doesn't sort properly, either.

I believe this is possible to do in an underlying query but I am unclear on how it would be accomplished.

TIA,
David R

[This message has been edited by David R (edited 01-28-2002).]

Try [LastOrderDate] on the property sheet

No luck, Rich. [LastContactDate] still gives me a parameter prompt (when I put Me.[LastContactDate] the parameter prompt is Me.LastContactDate, without the brackets, interestingly). I checked the underlying table for a Sort order, and it doesn't have one set either. The unbound field's name is LastContactDate, I checked (again).

David R


[This message has been edited by David R (edited 01-28-2002).]

Aha! More information appears.

First, I can replicate the seemingly random order in which the records appear. There is a filter placed on the records by the command button on the Switchboard which limits [ActiveContact] to either -1 or 0 depending on the button. When I sort an independent query Ascending by this field, I get the same order of records. Therefore I assume it is automatically sorting by the filtered field. However this is the code behind the button:
Private Sub buttonActiveContacts_Click()
On Error GoTo Err_buttonActiveContacts_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Contacts"
stLinkCriteria = "[ActiveStatus] = " & -1

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_buttonActiveContacts_Click:
Exit Sub

Err_buttonActiveContacts_Click:
MsgBox Err.Description
Resume Exit_buttonActiveContacts_Click

End Sub


I just put this behind the Contact form's Open event: Me.OrderBy = "LastContactDate", and this changed the order, but not correctly. It did do the expected and put "LastContactDate" in the Order By property sheet. Now it seems to be sorted by the table's Primary ID, and it takes 3x as long to open. What is going on here??

Any thoughts on this?

It's kinda difficult sometimes to envisage what a Member's trying to do, you're opening a flagged Contacts form and then want to order by the Last Contact date and not say the contact name, correct?

The table(s) are a contact log. It's filtered by Active Status from the Switchboard, and then I want to sort it by the last contact date on the subform. In this manner, the person we have NOT contacted in the longest time will be first on the list.

I can get the field to display correctly now, but the Sort functions are not effective, as noted above.

David R

You're going to have to bite the bullet and use the query method, it was the only way I could get it to work.

Can you show me what you did to get this to calculate in the underlying query? I added the expression LastContact: DMax("[DateContacted]","[tableContactDetails]",[tableContactDetails]![ContactID]=[tableContacts]![ContactID]) but it of course creates X 'copy' records for each subentry, even though they all contain identical data. However if you choose Unique Values, the Recordset is not updateable.

I thought Dlookup might help but I'm how it would be any different than using Dmax. Am I missing something bleeding obvious here?

David R

OK use a totals query group by ContactID etc MaxDateContacted, you can either add the flag field to the grid and set the criteria there or filter the form, I also dragged the original contact subform onto the new form and was able to order by on the max date field.
HTH

Rich, I understand what you're trying to do but not the execution of it. Can you send me what you did, and is it updateable? If not this whole exercise is for naught.

Right now I have a queryLastContactDate which is composed of two fields: ContactID = (Group By) tableContacts!ContactID and LastContact = (Max) tableContactDetails!DateContacted. I can't join this with another query as the underlying source because the dataset becomes non-updateable.
So my Dmax field became a Dlookup field:
=DLookUp("LastContact","queryLatestContactDate","queryLatestContactDate.ContactID = " & [Forms]![Contacts]![ContactID]), and it's STILL not sortable by this field!

I _know_ it's staring me in the face but I don't 'get' Domain Aggregate functions and this thing has been staring at me for three days. Sorry for ranting.

David R

OK by updateable you mean the form that's now filtered by the max date, you can't add new contacts to that form but you can add contact dates into the contacts subform for those contacts displayed on the main form, the only way I can see to add new contacts is to use two separate forms. Hope this makes sense it's late etc http://www.access-programmers.co.uk/ubb/smile.gif

Ok, that's what I was wondering if I was doing something wrong. Thanks for clarifying.