Earliest Date

[wakiwi]

This site is SO helpful, I'd be lost without you all answering questions! Here is my latest problem. I have a field on a report that reflects dates that a client entered in a search box[txtstartdate]. When no dates are entered by the client the report is based on all records. In this case I'd like the field on the form to reflect the earliest date. There may be blank fields which may be problem too, but right now dates are present for every entry. I have tried this code, and several variations, without anyluck

=IIf(IsNull(Forms!SearchForm!txtStartDate), Min[Forms]![SearchForm]![txtStartDate], Forms!SearchForm!txtStartDate)

Can anyone help? Where did I go wrong???

 

[ShiftyJaffa]

Hi

Your problem is that your txtStartDate field is not tied to any data in the back table and if it were I don't think your statement would work anyway.

Try this

If IsDate(nz(txtStartDate)) = False then txtStartDate = Dmin("TableDate","MyTable")

This assumes "TableDate" is the name of the date field in your table and "MyTable" is your table name.

Null fields shouldn't be a problem because the Dmin function should ignore them. If it doesn't for some weird reason, then use this instead:-

If IsDate(nz(txtStartDate)) = False then txtStartDate = Dmin("TableDate","MyTable","IsDate(nz(TableDate))=True")

Hope that helps

Paul