DMin in query

[Guest]

Using the query below, I'm trying to find the earliest date in a group of
records that have the same beginning characters. For example:
Row 2 would be in a group with row 3, 4, & 5.
Row 3 would be in a group with rows 4 & 5.
Row 6 would be in a group by itself.

I know that I'm messing up in the 'criteria' part of the syntax, but I've
tried everything to no avail. Any ideas? Thanks so much.

SELECT NormalizationQuery.CombinePart, NormalizationQuery.LeadTimeDate,
DMin("[LeadTimeDate]","NormalizationQuery","[CombinePart] = [CombinePart] &
"*" [CombinePart]") AS NewLeadTimeDates
FROM NormalizationQuery;

Row CombinePart LeadTimeDate6 333-111-333-444-555 10/2/04

 

[Marshall Barton]

Using the query below, I'm trying to find the earliest date in a group of
records that have the same beginning characters. For example:
Row 2 would be in a group with row 3, 4, & 5.
Row 3 would be in a group with rows 4 & 5.
Row 6 would be in a group by itself.

I know that I'm messing up in the 'criteria' part of the syntax, but I've
tried everything to no avail. Any ideas? Thanks so much.

SELECT NormalizationQuery.CombinePart, NormalizationQuery.LeadTimeDate,
DMin("[LeadTimeDate]","NormalizationQuery","[CombinePart] = [CombinePart] &
"*" [CombinePart]") AS NewLeadTimeDates
FROM NormalizationQuery;

Row CombinePart LeadTimeDate6 333-111-333-444-555 10/2/04

Try this:

SELECT CombinePart, LeadTimeDate,
DMin("[LeadTimeDate]","NormalizationQuery",
"""" & [CombinePart] & """ LIKE [CombinePart] & """*""")
FROM NormalizationQuery

Be sure to count the quotes carefully.

An Alternative would be to use the Left function instead of
Like, it may (or may not) be faster.