Max function manfunction.

[Guest]

I want to te set the default value on a data entry form for a field named
"trk_sle_id" to the next sequential number in the field. I am attempting to
use the expression =1+Max([trk_sle_id]) or =Max([trk_ssle_id])+1.

The result always comes back as 1 (the max function always returns 0 value).

Why is this the case?

 

[Rick Brandt]

I want to te set the default value on a data entry form for a field
named "trk_sle_id" to the next sequential number in the field. I am
attempting to use the expression =1+Max([trk_sle_id]) or
=Max([trk_ssle_id])+1.

The result always comes back as 1 (the max function always returns 0
value).

Why is this the case?

Use DMax() instead of Max().

 

[Douglas J. Steele]

I want to te set the default value on a data entry form for a field
named "trk_sle_id" to the next sequential number in the field. I am
attempting to use the expression =1+Max([trk_sle_id]) or
=Max([trk_ssle_id])+1.

The result always comes back as 1 (the max function always returns 0
value).

Why is this the case?

Use DMax() instead of Max().

Of course, DMax would require 2 arguments: the name of the field and the
name of the table (in that order)

 

[Guest]

Using the DMax function as you reccomended does provide the desired results.
However, Being the inqiring person that I am, I would like to understand why
the Max function does not provide the expected result. By all description it
seems like a simple and intuitive fnction to use for this purpose.

I want to te set the default value on a data entry form for a field
named "trk_sle_id" to the next sequential number in the field. I am
attempting to use the expression =1+Max([trk_sle_id]) or
=Max([trk_ssle_id])+1.

The result always comes back as 1 (the max function always returns 0
value).

Why is this the case?

Use DMax() instead of Max().

Of course, DMax would require 2 arguments: the name of the field and the
name of the table (in that order)

 

[Douglas J. Steele]

Access doesn't have a Max function. Jet (the database component) does, but
it's only for use within a query. To be honest, I don't understand why you
were getting 0 returned when you used it: I would have expected an error to
have been raised.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Using the DMax function as you reccomended does provide the desired results.
However, Being the inqiring person that I am, I would like to understand why
the Max function does not provide the expected result. By all description it
seems like a simple and intuitive fnction to use for this purpose.

Waynerdude wrote:
I want to te set the default value on a data entry form for a field
named "trk_sle_id" to the next sequential number in the field. I am
attempting to use the expression =1+Max([trk_sle_id]) or
=Max([trk_ssle_id])+1.

The result always comes back as 1 (the max function always returns 0
value).

Why is this the case?

Use DMax() instead of Max().

Of course, DMax would require 2 arguments: the name of the field and the
name of the table (in that order)

 

[Rick Brandt]

Using the DMax function as you reccomended does provide the desired
results. However, Being the inqiring person that I am, I would like
to understand why the Max function does not provide the expected
result. By all description it seems like a simple and intuitive
fnction to use for this purpose.

With DMax() you are supplying *everything*. The field, the domain, and the
criteria so they are not context sensitive. IOW a Domain Aggregate function
works the same no matter where you put it.

The normal aggregate functions (Min(), Max(), etc.), are context sensitive
meaning that the domain they aggregate over is dependent on where you use them.
If you have a report with A ReportHeader, one GroupHeader, and a Detail section,
then Max([SomeField]) will give a different result in each section.

In the ReportHeader it will provide the maximum value in the entire report
RecordSet. In the GroupHeader it will give the maximum value "per-group", and
in the detail section it will simply return each individual value of [SomeField]
and essentially be the same as not using Max() in the first place.

So...in your case it is possible that Max() might have worked had you used it in
the FormHeader or FormFooter, but using an expression as a default for a control
in the detail section will necessarily mean that you are doing an aggregation
over the one current record and when you are on a new record the Max() of that
will be zero (assuming this field has a zero default at the table level).