By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

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

P: 11
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
Mar 2 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,324
If they're sequential you could use DMin and Dmax.
Mar 2 '07 #2

P: 11
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"
Mar 2 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
First one should be:

Expand|Select|Wrap|Line Numbers
  1.  
  2. =DMin("[HourMeterOut]","FOR","[AircraftID]=3 AND
  3. Year([Date])=Year(Date()) AND Month([Date])=Month(Date())-1")
  4.  
By using DateSerial you were only returning results where the date was the first of the month.

The second one should be:

Expand|Select|Wrap|Line Numbers
  1.  
  2. =DMin("[HobbsOut]","[FOR]","Year([Date])=Year(Date()) AND Month([Date])=Month(Date())-1 AND [AircraftID]=3")
  3.  
Mary
Mar 2 '07 #4

P: 11
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
Mar 2 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this...

Expand|Select|Wrap|Line Numbers
  1. =DMax("[HobbsIn]","FOR","[AircraftID]=6 AND
  2. Year([Date])=Year(Date()) AND Month([Date])=Month(Date())-1 AND Day(DateSerial(Year(Date()), Month(Date()), 1)-1)")
  3.  
Mary
Mar 2 '07 #6

P: 11
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
Mar 2 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Mar 2 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.