Criteria for a minimum itself

[June7]

Look at the spreadsheet you posted.



The NameID field in the spreadsheet is a name string, not a numeric ID.

I did the date edit in the spreadsheet before importing to Access table. Since the Dates field in your Access table is a date datatype, the '-' character may be a format setting for display purposes and not actually in the date value. Format settings in table can carry over into queries. However, I thought this should still work in the query if your criteria follow the same structure but maybe not so for date values. Check the Format property for the Dates field in the table, remove anything there and view the field without formatting. What does the data look like?

I never set formatting or Lookups in tables just to avoid this sort of confusion.

[hawkins]

sweet got it to work thanks! I was wondering though, if I plug in exactly what you gave me it works but say I plug in, different range and date value:
SELECT * FROM Example
WHERE Dates Between #3/1/2009# And #5/30/2009# And Dates=Nz(DMin("Dates","Example","Month(Dates)=5 AND NameID='" & [NameID] & "'"),DMin("Dates","Example","NameID='" & [NameID] & "'"));

or even Month(Dates)=4, I only get that month back but yours gives me the correct value. I just dont understand why when I change those 2 things it causes it to not work.

[June7]

Sorry, I should have done more testing. Will have to put the date range in both of the DMin terms. Change the WHERE part to:

WHERE Nz(DMin("Dates","Example","Dates Between #3/1/2009# And #5/31/2009# And Month(Dates)=5 AND NameID='" & [NameID] & "'"),DMin("Dates","Example","Dates Between #3/1/2009# And #5/31/2009# And NameID='" & [NameID] & "'"))

Note that March, May, July, August, October, December have 31 days, not 30 and then there is February.