DMax Question

[Stemdriller]

The following code is working ok.

The thing is I have been asked to change the DB slightly, my question is

Within the table tblVisits can the DMax check 4 other fields in the table eg CustRef1, CustRef2, 3 and 4??

So I am basically getting the DMax command to check 5 Fields instead of the initial one?

=DMax("DateOfVisit","tblVisits","CustRef= Forms!frmAddVisit.CustRef")

Thanks
GW

 

[missinglinq]

What kind of Fields are you talking about? DMax() is intended to be to be used against Number or Date Fields. If you wanted to pull the maximum sum of two Numeric Fields you can use

Me.BiggestSumField = DMax("[Field1] + [Field2]", "MyTableName")

Linq ;0)>

 

[Stemdriller]

The current vba code i have listed brings back the latest date. So when we bring up the profile of a client, it shows the last time they visited.

But if the client is involved in another visit and is not the 'Leader' there name will go in another field CustRef1, CustRef2, CustRef3 or CustRef4.

So the DMax command will need to look in all fields and bring back the latest date.

 

[Galaxiom]

Your problem begins with inefficient data structure. CustRefs shoud be held as individual records in a related table. It would be best to redesign it.

However an alternative would be to use a union query to get all the fields into one then get a Max from that. Alternatively use a query with a derived field to get the latest date from the five in each record and then get a Max from that.