Using Max or DMax in DLookUp criteria?

[Nathan]

Hello all. I'm a new member, but have been searching the forums for several
weeks as I work on upgrading the functionality of my non-profit's database.
This time, however, I wasn't able to find an answer here or using google,
though perhaps I was just looking in the wrong place.

SYNOPSIS--can I use DLookUp to return a value of one field (e.g. "Date")
when the value of another field field in the same record (e.g. "count") is
maximized? I've been unsuccessful using Max and DMax functions in DLookUp
criteria. If this won't work, is there another way to do it? Thanks!
Details follow.

Here's what I've got: A main table "hawk" that contains hawk migration data
from 25 years, using date as a primary key. Each species/age/sex class (e.g.
juvenile Red-tailed Hawk as "RTHA_juv") has its own field with the number of
times it was sighted during on the given date.

I'm attempting to get the date that saw the maximum and minimum flights
(sightings per day) of a given species/age/sex class both overall and by
year. After working unsuccessfully with DLookUp, I used a very brute force
approach using multiple queries: one to get the maximum value, one to inner
join the max value table for each species (19!) with "hawks" to get the date
of the maximum value, and then an make-table query to pull everything back
together grouped by year.

Now, however, I want to quickly return both the overall and yearly maximums
using a calculated text box on a form. I've been experimenting with DLookUp,
again unsuccessfully.

In the control source box of the text box, I've tried using the following
code, all unsuccessful. I was similarly unsuccessful when trying to put this
code in a select query and linking the text boxes to that. "TUVU" is a
numeric field denoting the number of Turkey Vultures seen on a given day,
"Date" is a unique field with only date info (no time), and "hawk" is the
table I'm working from.

I started out with a Max function in the criteria of DLookUp:

=DLookUp("[Date]","hawk_by_day_filtered",Max([TUVU]))
'Returns "#Error" as does any combination of brackets and quotation marks
around TUVU

=DLookUp("[Date]","hawk_by_day_filtered","[TUVU]"=Max([TUVU]))
'Returns "#Error"

Then I tried DMax since that wasn't working...

=DLookUp("[Date]","hawk",DMax("[TUVU]","hawk"))
'Returns the first date on record

=DLookUp("[Date]","hawk","[TUVU]"=DMax("[TUVU]","hawk"))
'returns nothing--no errors, doesn't even say "Null"

=DLookUp("[Date]","hawk",DMax("[TUVU]","hawk"))
'putting quotation marks around the entire DMax (or Max) function returns
the following error: "The expression you have entered contains invalid
syntax. You may have entered an operand without an operator." Removing the
quotation marks or brackets from around the Date or TUVU fields doesn't seem
to matter either.

Any suggestions y'all have would be more than welcome, and please let me
know if I've left out important info. Again, thanks for your time!

 

[John W. Vinson]

Hello all. I'm a new member, but have been searching the forums for several
weeks as I work on upgrading the functionality of my non-profit's database.
This time, however, I wasn't able to find an answer here or using google,
though perhaps I was just looking in the wrong place.

SYNOPSIS--can I use DLookUp to return a value of one field (e.g. "Date")
when the value of another field field in the same record (e.g. "count") is
maximized? I've been unsuccessful using Max and DMax functions in DLookUp
criteria. If this won't work, is there another way to do it? Thanks!
Details follow.

Here's what I've got: A main table "hawk" that contains hawk migration data
from 25 years, using date as a primary key. Each species/age/sex class (e.g.
juvenile Red-tailed Hawk as "RTHA_juv") has its own field with the number of
times it was sighted during on the given date.

Gnnnnnn.... Sorry, but your table design is WRONG. That's good spreadsheet
design, but relational database tables aren't spreadsheets!!! I'd also be
inclined to break up this non-atomic field into species, age and sex fields.

A better design would be a tall-thin table with fields like Species, Sex, Age,
DateSeen, TimesSighted. You would of course also have a table of all the
species, and lookups (perhaps just combo boxes with value lists) for sex and
age groups.

I'm attempting to get the date that saw the maximum and minimum flights
(sightings per day) of a given species/age/sex class both overall and by
year. After working unsuccessfully with DLookUp, I used a very brute force
approach using multiple queries: one to get the maximum value, one to inner
join the max value table for each species (19!) with "hawks" to get the date
of the maximum value, and then an make-table query to pull everything back
together grouped by year.

That's why your design needs changing. With the tall-thin you would use a very
simple totals query.

Now, however, I want to quickly return both the overall and yearly maximums
using a calculated text box on a form. I've been experimenting with DLookUp,
again unsuccessfully.

I'm pretty good with queries, but I'd find this challenging indeed!

In the control source box of the text box, I've tried using the following
code, all unsuccessful. I was similarly unsuccessful when trying to put this
code in a select query and linking the text boxes to that. "TUVU" is a
numeric field denoting the number of Turkey Vultures seen on a given day,
"Date" is a unique field with only date info (no time), and "hawk" is the
table I'm working from.

I started out with a Max function in the criteria of DLookUp:

=DLookUp("[Date]","hawk_by_day_filtered",Max([TUVU]))
'Returns "#Error" as does any combination of brackets and quotation marks
around TUVU

=DLookUp("[Date]","hawk_by_day_filtered","[TUVU]"=Max([TUVU]))
'Returns "#Error"

Then I tried DMax since that wasn't working...

=DLookUp("[Date]","hawk",DMax("[TUVU]","hawk"))
'Returns the first date on record

=DLookUp("[Date]","hawk","[TUVU]"=DMax("[TUVU]","hawk"))
'returns nothing--no errors, doesn't even say "Null"

=DLookUp("[Date]","hawk",DMax("[TUVU]","hawk"))
'putting quotation marks around the entire DMax (or Max) function returns
the following error: "The expression you have entered contains invalid
syntax. You may have entered an operand without an operator." Removing the
quotation marks or brackets from around the Date or TUVU fields doesn't seem
to matter either.

Any suggestions y'all have would be more than welcome, and please let me
know if I've left out important info. Again, thanks for your time!

Is there ANY chance that you could restructure your table? Fear not, it's no
big deal to migrate your data.