DMax

[Stemdriller]

Hi People

I am trying to nail some DMax code, and could do with some help

I have an unbound text box called DateOfPrev, I am trying to get the code to lookup the last date that a particular client visited our site

=Dmax("DateOfVisit","tblVisits","CustRef = txtCustRef")

The above code just brings back the latest date from tblVisits, I am trying to only bring back the latest date for the specifc Client specified in txtCustRef.

Help would be appreciated.

Thanks

Gareth

 

[Galaxiom]

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

 

[Stemdriller]

Galaxiom

I tried everything but that, as I thought as I was already on the form why would I have to tell it where to find the textbox again.

Buy hey ho, it works

So thanks again

 

[Galaxiom]

I thought as I was already on the form why would I have to tell it where to find the textbox again.

Look at the arguments of the DMax. They are text strings. They are being submittted to a function that has no awareness of anything but those strings.

Consequently the full reference must be supplied.

It is different when you refer directly to a control in the ControlSource Property. Outside of a string the reference does know about other objects on the form. This will also work because it puts the value from the textbox into the string.

=DMax("DateOfVisit","tblVisits","CustRef =" & txtCustRef)

The advantage of the full reference in the string is that Access takes care of the datatype. So if txtCustRef is a string it doens't have to have quotes put in it like this which is required for the concatenation of a string value.

=DMax("DateOfVisit","tblVisits","CustRef='" & txtCustRef & "'")

A further advantage of the full reference is anomalies like apostrophes in the control value are handled reliably. They are a major problem when concatenating the value.