Returning the first value For a Date Range on a Form (DLookup?)

[dunkleypilot]

Hello,

I have created a database to track the flights on three aircraft owned by the flight school I manage. The relevant fields in the FlightRecords table are Date, AircraftID, HourMeterOut, and HourMeterIn. [HourMeterIn] - [HourMeterOut] = TotalFlightTime.
Each month I generate a report that calculates the total flight time for each aircraft. That report calls a Dialog box which has BeginDate and EndDate, BeginHourMeter (which is the first HourMeterOut value for the first day of the month) and EndHourMeter (which is the last HourMeterIn for the last day of the month.
What I would like to do is have the default value of the BeginHourMeter txtBox lookup the first HourMeterOut time for the last month. And the EndHourMeter txtBox lookup the last HourMeterIn time for the last month. I have already figured out how to make the BeginDate and EndDate get the previous month with DateSerial().

Are there any suggestions? I have experimented with DLookup() but to no avail.

Thanks in advance,
Steve

[Rabbit]

If they're sequential you could use DMin and Dmax.

[dunkleypilot]

This is what I tried:

=DMin([HourMeterOut],[FOR],[AircraftID]=3 And [Date]=DateSerial(Year(Date()),Month(Date())-1,1))

It came back with "#Name?" error when I loaded the form.

I also tried:

=DMin("[HobbsOut]","[FOR]","[Date]=DateSerial(Year(Date()),Month(Date())-1,1)" And "[AircraftID]=3")

This time is came back with "0"

[MMcCarthy]

First one should be:

 
=DMin("[HourMeterOut]","FOR","[AircraftID]=3 AND
Year([Date])=Year(Date()) AND Month([Date])=Month(Date())-1")
 

By using DateSerial you were only returning results where the date was the first of the month.

The second one should be:

 
=DMin("[HobbsOut]","[FOR]","Year([Date])=Year(Date()) AND Month([Date])=Month(Date())-1 AND [AircraftID]=3")
 

Mary

[dunkleypilot]

Thanks Mary,

The first one worked, however I had to change it to:

=DMin("[HobbsOut]","FOR","[AircraftID]=6 AND
Year([Date])=Year(Date()) AND Month([Date])=Month(Date())-1 AND Day(1)")

by adding the AND Day(1) to get the first day of the month.

Now I'm trying to use DMax Function

=DMax("[HobbsIn]","FOR","[AircraftID]=6 AND
Year([Date])=Year(Date()) AND Month([Date])=Month(Date())+1 AND Day(1)-1")

This doesn't return any information. The txtbox is blank. I am trying to get the last HobbsIn time for the end of the previous month.

Thanks,
Steve

[MMcCarthy]

Try this...

=DMax("[HobbsIn]","FOR","[AircraftID]=6 AND
Year([Date])=Year(Date()) AND Month([Date])=Month(Date())-1 AND Day(DateSerial(Year(Date()), Month(Date()), 1)-1)")
 

Mary

[dunkleypilot]

Wonderful!

Thanks so much, those solved all my problems. I am still learning about all the syntax and the way to use functions. They are all there in my head it's just a matter of putting them down right order. Thanks again

Steve

[MMcCarthy]

Wonderful!

Thanks so much, those solved all my problems. I am still learning about all the syntax and the way to use functions. They are all there in my head it's just a matter of putting them down right order. Thanks again

Steve

You're welcome.