DMin Function

[sherrygroves]

Hi!!

I have two tables (My Notes and Reporting Dates) and input forms for each. The Reporting Dates table contains the State Names (two letter abbreviation) and report due dates for each state throughout the year. The My Notes table contains the State Names (two letter abbreviation) and general information specific to each state. My Notes has one record per state and of course the Reporting Dates table has many records per state.

When I open the My Notes form, I want to display a field that has the Next Report Due date for that state. I have a query (Next Due Date) which has the state field from the Reporting Dates table that returns the dates I want. I am using the following statement to try to do that:

=DMin("[Min Of Report Due Date]","Next Due Date","[Next Due Date]![State] = [Reporting]![State]")

where Min of Report Due Date is the field in the query that holds the dates and Next Due Date is the query name. With the above statement, it returns the min due date in every record, not the min date for that particular state.

Can someone help me?

Thanks,


Sherry

[June7]

Concatenate the State variable. I assume it is a text field.

=DMin("[Min Of Report Due Date]","Next Due Date","[State] ='" & [State] & "'")

[sherrygroves]

Thank you so much!!! I have been trying to figure this out for a couple of weeks and just found this forum!!