Display Max Date Text Box

[pastelrain]

Hi,

I've read several discussions on here and still have not been able to find a solution to my problem. I'm wanting to display the max date of a field in a text box on my report. I've tried the following:

=DMax("[HbA1c_Test_Date]","QI_GAP_REPORT") w/and w/o brackets/quotes on each

=DLookUp(Max("[HbA1c_Test_Date]"),"QI_GAP_REPORT") w/ and w/o brackets/quotes on each

And I keep getting #Error as my result. Can anyone help?

Thanks.

 

[sneuberg]

Try substituting the query name of the record source of the report for QI_GAP_REPORT like

=DMax("[HbA1c_Test_Date]","[the name of the query]")

 

[pastelrain]

Thank you. The name of the query with the record set is QI_GAP_REPORT.

 

[sneuberg]

I'm not sure about this one but try:

=Max([HbA1c_Test_Date])

 

[plog]

=DMax("[HbA1c_Test_Date]","QI_GAP_REPORT")

There' are 3 reasons that would return #Error:

1. HbA1c_Test_Date is not a field in QI_GAP_REPORT.

2. QI_GAP_REPORT is not a table/query in your database.

3. The HbA1c_Test_Date field contains #Error values itself.

My money is on #3. I bet HbA1c_Test_Date is a calculated field using a date function (CDate, DateValue, DateAdd, etc.) and one of the values you are passing it is invalid--either null or not a date or not a number.

Run QI_GAP_REPORT by itself and sort it in Descending order on the HbA1c_Test_Date field. My bet is it won't sort. THen you have to find the offending record and route around it.

 

[pastelrain]

That was the first thing I tried and it did not work, but I just found that the format of the field was text, so I changed that to date and now it works. Thank you for your help!

 

[pastelrain]

Thanks, plog. You were right!