Date question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to create a query that will give me...
the last date of the previous month
the last date of the current month.
My application is for use in tracking mileage and maintenance on a fleet of vehicles. My Maintenance table is set up with fields that include
ID, MaintDate,OdometerRead.
I'm close, I'm sure, but am stumped!
Help, if you can.
 
Dear Kate:

Using MyDate as the value of the date from which to start

- the last date of the previous month:

DateAdd("d", -Day(MyDate), MyDate)

that is, from Jan 7, 2004, back up 7 days, giving December 31, 2003

- the last date of the current month:

DateAdd("d", -Day(DateAdd("m", 1, MyDate)), DateAdd("m", 1, MyDate))

that is, move to the same date in the next month (but only to the
last day of the next month if it is a shorter month), then back up to
the last date of the previous month, as above.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I am attempting to create a query that will give me...
the last date of the previous month
the last date of the current month.
My application is for use in tracking mileage and maintenance on a fleet of vehicles. My Maintenance table is set up with fields that include
ID, MaintDate,OdometerRead.
I'm close, I'm sure, but am stumped!
Help, if you can.
Click to expand...

There's a sneaky way to use the DateSerial function for this purpose.
The last day of a month is the zeroth day of the *next* month:

LastDayPrevious: DateSerial(Year([MaintDate]), Month([MaintDate]), 0)

LastDayCurrent: DateSerial(Year([MaintDate]), Month([MaintDate])+1, 0)
 
John, you're just naturally sneaky! So, the "zeroth" day of the month
is the last day of the previous month. So what is the -1th day of the
month? Last Thanksgiving?

The only problem would be if Microsoft ever "fixes" the function so it
doesn't do this anymore. Hopefully that won't happen, but if you
"misuse" a function like this (and that's a judgement call) and they
later "fix" it, then a lot of queries are going to stop working.

I would tend to recommend not using a "trick" method because of this
danger. I have enough things to fix in order to get my applications
working in Access 2003 without having to worry about such things. But
everyone should decide for themselves (and be prepared to face the
consequences).

Although it's messier, I'll stick with the alternative I posted. But
your suggestion is tempting. I like tricky things, too!

So what is the zeroth month of the year? Are there any values for
month and day that result in DateSerial eventually blowing up?

And how do you know that this undocumented feature never fails? Will
you be calling Microsoft to complain that DateSerial is broken because
it doesn't do this odd behavior in some particular case? Good luck!

(I believe John know me well enough to know I don't mean any of this
in an unfriendly manner.)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I am attempting to create a query that will give me...
the last date of the previous month
the last date of the current month.
My application is for use in tracking mileage and maintenance on a fleet of vehicles. My Maintenance table is set up with fields that include
ID, MaintDate,OdometerRead.
I'm close, I'm sure, but am stumped!
Help, if you can.
Click to expand...

There's a sneaky way to use the DateSerial function for this purpose.
The last day of a month is the zeroth day of the *next* month:

LastDayPrevious: DateSerial(Year([MaintDate]), Month([MaintDate]), 0)

LastDayCurrent: DateSerial(Year([MaintDate]), Month([MaintDate])+1, 0)
Click to expand...
 
Tom Ellison said:
John, you're just naturally sneaky! So, the "zeroth" day of the month
is the last day of the previous month. So what is the -1th day of the
month? Last Thanksgiving?

The only problem would be if Microsoft ever "fixes" the function so it
doesn't do this anymore. Hopefully that won't happen, but if you
"misuse" a function like this (and that's a judgement call) and they
later "fix" it, then a lot of queries are going to stop working.

I would tend to recommend not using a "trick" method because of this
danger. I have enough things to fix in order to get my applications
working in Access 2003 without having to worry about such things. But
everyone should decide for themselves (and be prepared to face the
consequences).

Although it's messier, I'll stick with the alternative I posted. But
your suggestion is tempting. I like tricky things, too!

So what is the zeroth month of the year? Are there any values for
month and day that result in DateSerial eventually blowing up?

And how do you know that this undocumented feature never fails? Will
you be calling Microsoft to complain that DateSerial is broken because
it doesn't do this odd behavior in some particular case? Good luck!

(I believe John know me well enough to know I don't mean any of this
in an unfriendly manner.)
Click to expand...

According to the help file this is a perfectly legitimate use of the DateSerial
function and not a "trick" that exploits undocumented behavior.

Help file quote:

....the range of numbers for each DateSerial argument should be in the accepted
range for the unit; that is, 1-31 for days and 1-12 for months. However, you can
also specify relative dates for each argument using any numeric expression that
represents some number of days, months, or years before or after a certain date.
 
I am totally enthralled by your convo. I'm new at all of this (posting questions and db development - yes a rookie, so sorry for any ignorance on my part).

OK... you're suggestion got me seeing the light at the end of the tunnel... THANK YOU!!! I've been pulling my hair out on this for over a week. now...

how can I get it to find the record that contains the data for the "myDate" that is <= the last date of the previous month? I only want the last date (record) of that previous month. I'm attempting to take the last odometer read of the previous month and subtract it from the last reading of the current month to result in a real "total miles driven" for the current month (This calculation, I beleive, would be best done in the report, don't you think?). I have the query set up to give me a range (BETWEEN[start]and[end]) and it gives me all the current month data that I want, now I only need to see that last month's date and odometer read!
Again!!! THANK YOU!!!!!!!
 
I must have read more into John's comment that this is "a sneaky way
to use the DateSerial function." If this is documented then have at
it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
For me, there is an additional concern in any query I write:
portability.

For me, this means portability between Jet and MSDE.

The code I suggested would have such portability. I just tried it in
MSDE and it works without alteration. There is no DateSerial function
in MSDE, so this method won't work.

Portability does have something to do with not using "tricks", at
least usually it does.

I have partially trained myself to try to use query methods that work
in both Jet and MSDE.

If you do not have such concerns, if your concern is just to get
something working in Jet, then you're probably in the majority here in
this newsgroup. And there's nothing wrong with John's suggestion. In
fact, now that I know it is documented, most of my earlier suspicions
have evaporated.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Try the UNTESTED query below (modified for your table name)

SELECT A.ID, A.MaintDate, A.OdometerRead
FROM SomeTable As A
WHERE MaintDate In
(SELECT Max(Tmp.MaintDate)
FROM SomeTable As Tmp
WHERE A.ID = tmp.ID
AND tmp.MaintDate <=
DateSerial(Year(Date()), Month(Date()), 0))

You could also use the DMAX or DLOOKUP function to return a value.
 
Back
Top