Problem with Dmin

[Bob Quintal]

I can not get the criteria to work correctly in the following
statement:

MinScore: DMin("[Score]","qryCommodityReport-Step1"," [Part #] =
[Part #]")

My results are as follows:

Part # Score MinScore
0000-0000 373.75 3.95
0000-0000 372.5 3.95
70065 4.95 3.95
70065 5 3.95
70065 4.9 3.95
70065 7.5 3.95
70065 3.95 3.95


Desired results:

Part # Score MinScore
0000-0000 373.75 372.50
0000-0000 372.5 372.50
70065 4.95 3.95
70065 5 3.95
70065 4.9 3.95
70065 7.5 3.95
70065 3.95 3.95

I want the min for each part# not for the entire set as I am
getting in the above example.

If I manually type in the part # with apostrophes it will give me
the dmin of the part# I put in the apostrophes. I don't
understand what I am doing wrong. Please help and explain.

greatfully yours,
Robert

MinScore: DMin("[Score]",
"qryCommodityReport-Step1",
"[Part #] = " & [Part #] )

 

[Robert_DubYa]

I can not get the criteria to work correctly in the following statement:

MinScore: DMin("[Score]","qryCommodityReport-Step1"," [Part #] = [Part #]")

My results are as follows:

Part # Score MinScore
0000-0000 373.75 3.95
0000-0000 372.5 3.95
70065 4.95 3.95
70065 5 3.95
70065 4.9 3.95
70065 7.5 3.95
70065 3.95 3.95


Desired results:

Part # Score MinScore
0000-0000 373.75 372.50
0000-0000 372.5 372.50
70065 4.95 3.95
70065 5 3.95
70065 4.9 3.95
70065 7.5 3.95
70065 3.95 3.95

I want the min for each part# not for the entire set as I am getting in the
above example.

If I manually type in the part # with apostrophes it will give me the dmin
of the part# I put in the apostrophes. I don't understand what I am doing
wrong. Please help and explain.

greatfully yours,
Robert

 

[Pieter Wijnen]

Dmin will always return one value only
you have to loop through the data or make a query
SELECT [Part#], Min(Score) As MinScore
FROM [qryCommodityReport-Step1]
GROUP BY [Part#]

HtH

Pieter

 

[Ken Snell \(MVP\)]

Move the second [Part #] outside the quotes -- this example assumes that
[Part #] is a numeric field:

MinScore: DMin("[Score]","qryCommodityReport-Step1","[Part #] =" & [Part #])

 

[John W. Vinson]

I can not get the criteria to work correctly in the following statement:

MinScore: DMin("[Score]","qryCommodityReport-Step1"," [Part #] = [Part #]")

You need to pull the [Part #] you're using as a criterion out of the quote
marks, and add *more* quote marks to serve as delimiters. What you're doing
here is finding the minimum score for all those records where the part number
is equal to itself - that is, all the records in the table!

Try

MinScore: DMin("[Score]","qryCommodityReport-Step1"," [Part #] = '" & [Part #]
& "'")

Spaced out for readability, that's

MinScore: DMin("[Score]","qryCommodityReport-Step1"," [Part #] = ' " & [Part
#] & " ' ")

The effect will be to generate a criteria string like

[Part #] = '0000-0000'

using ' as a delimiter.

I'd also strongly suggest that you avoid using punctuation like - and # in
fieldnames; Access can get confused.

John W. Vinson [MVP]

 

[Robert_DubYa]

Tried the following and it did not work at all:

MinScore: DMin("[Score]",
"qryCommodityReport-Step1",
"[Part #] = " & [Part #] )

 

[Ken Snell \(MVP\)]

This example assumes that [Part #] is a text field:

MinScore: DMin("[Score]","qryCommodityReport-Step1","[Part #] ='" & [Part #]
& "'")

--

Ken Snell
<MS ACCESS MVP>

Tried the following and it did not work at all:

MinScore: DMin("[Score]",
"qryCommodityReport-Step1",
"[Part #] = " & [Part #] )

 

[Robert_DubYa]

John,

Thank you very much, your solution worked. You tried to explain why this
works, and after reading it several times I still don't understand. Would
you mind explaining the last section (the criteria) piece by piece?

thank you again,
Robert

I can not get the criteria to work correctly in the following statement:

MinScore: DMin("[Score]","qryCommodityReport-Step1"," [Part #] = [Part #]")

You need to pull the [Part #] you're using as a criterion out of the quote
marks, and add *more* quote marks to serve as delimiters. What you're doing
here is finding the minimum score for all those records where the part number
is equal to itself - that is, all the records in the table!

Try

MinScore: DMin("[Score]","qryCommodityReport-Step1"," [Part #] = '" & [Part #]
& "'")

Spaced out for readability, that's

MinScore: DMin("[Score]","qryCommodityReport-Step1"," [Part #] = ' " & [Part
#] & " ' ")

The effect will be to generate a criteria string like

[Part #] = '0000-0000'

using ' as a delimiter.

I'd also strongly suggest that you avoid using punctuation like - and # in
fieldnames; Access can get confused.

John W. Vinson [MVP]

 

[John W. Vinson]

John,

Thank you very much, your solution worked. You tried to explain why this
works, and after reading it several times I still don't understand. Would
you mind explaining the last section (the criteria) piece by piece?

The third argument to any domain function needs to be a String value, which
will be interpreted as if it were the WHERE clause to a SQL query. To see how
WHERE clauses work, take any query in your database which has criteria, and
select View... SQL from the menu. Read down through the text you'll see (which
is the *real* query, the query grid is just a tool to build SQL) and you'll
find the WHERE clause, and can see how it works for that query.

In your example you had

MinScore: DMin("[Score]","qryCommodityReport-Step1"," [Part #] = [Part #]")


The criteria here was

" [Part #] = [Part #]"

which means that it will find all records in qryCommodityReport-Step1 for
which [Part #] is equal to [Part #] - and since, by definition, any non-NULL
value is always equal to itself, that's everything in the entire query!

What you want instead is, for each record in the query, to find the minimum
score for *that record's* [Part #].

The way to do that is to construct the third argument out of pieces - a
literal text string

"[Part #] ='"

which includes the field you want to search and the leading quote of the
criterion; to this you want to append the [Part #] *VALUE* from the "outer"
query, and then a closing quote "'". The three pieces (spaced out for
readability) are

"[Part #] = ' "
[Part #] (no quotes, you want the value of the field, not its name
" ' "

For a record with 0000-0000 as the Part # you'll construct a string

[Part #] = '0000-0000'

by concatenating these three pieces.

John W. Vinson [MVP]

 

[Robert_DubYa]

Thank you for the explanation. You and others like you on this web site have
all helped me out me immensely (many times you don't even know it)! I can't
thank you all enough.

Robert