Dmin for Dates in Microsoft Access

Question

I'm really suck with using dmin in Microsoft Access. I'm trying to do this in Design Query and dmin is giving me grief.

The purpose of this is to design a general query, which when given name of a company, which will create a table containing all existing fields and one new field which contains the earliest due date for that given company

for example source table is below :

Company         Facility  Fee   Due Date
ABC Pty Ltd Tr. 1     500   1-Jun
ABC Pty Ltd Tr. 1      51   3-Jun
ABC Pty Ltd Tr. 2     400   1-May
EFT Pty Ltd Tr. 1     400   5-Jun
EFT Pty Ltd Tr. 2     845   6-Jun
EFT Pty Ltd Tr. 3     100   3-Sep

So in example below, when user inputs company name as "ABC Pty ltd" into my query, he should get back :

Company         Facility  Fee   Due Date   Earliest
ABC Pty Ltd Tr. 1     500   1-Jun      1-May    
ABC Pty Ltd Tr. 1      51   3-Jun      1-May
ABC Pty Ltd Tr. 2     400   1-May      1-May

I tried to use dmin as a field in my query like so :

Expression : DMin([Due Date],"source","[company] ='" & [source]![company name] & "'")

however all I get is

Company         Facility  Fee   Due Date   Earliest
ABC Pty Ltd Tr. 1     500   1-Jun      1.552545454   
ABC Pty Ltd Tr. 1      51   3-Jun      4.3253968253968
ABC Pty Ltd Tr. 2     400   1-May      3.543083900

So it doesn't appear that Dmin is working (not to mention the fact that dmin can't seem to handle dates)

Can anyone please help?

Thanks

[Due Date] field data type is Date? DMin works fine with fields Data data type. — Sergey S.

Gustav Gustav · Accepted Answer · 2016-06-14T12:06:13

If your dates are true Date values, DMin will work right away.

If they are strings like "1-Jun", use CDate to convert them:

DMin(CDate([Due Date]),"source","[company] ='" & [source]![company name] & "'")

If you want the minimum date for all companies, don't filter on company, thus:

DMin("[Due Date]"),"source")

If you want the minimum date for one company, filter on that company, thus:

DMin("[Due Date]","source","[company] ='" & [source]![company name] & "'")

Dmin for Dates in Microsoft Access

1 Answers