Last day of month in query

  • Thread starter Thread starter Andreas
  • Start date Start date
A

Andreas

Hi all,

I have a query that gives me the dates and account balances like in
the following example:

Original query:
Date Account balance
2009-01-03 500
2009-01-07 300
2009-01-31 1000
2009-02-15 800
2009-02-28 800
2009-03-30 800
2009-04-28 2000
2009-04-30 2000
....

I now would like to extract only the values for the last day of a
month and put them in a new query. Keep in mind that the last day
could be 31st, 30th, 28th (29th). In addition, I have multiple years
of data.
Also, I need the percentage change from one month to another. As a
result, this second query should then looks like that:

Date Account balance %-Change
2009-01-31 1000 0%
2009-02-28 800 -20%
2009-03-30 800 0%
2009-04-30 2000 +150%
....

What do I have to do in order to come up with a query like this?

Regards,
Andreas
 
hi Andreas,

What do I have to do in order to come up with a query like this?
Click to expand...
The last day in a month is always the first day of the next minus one
day, e.g.

DateSerial(2010, 3, 1) - 1


mfG
--> stefan <--
 
Andreas

You might be able to use a little trick that works on the DateSerial()
function.

If you put in a Year and a Month, then use "0" for the day, DateSerial()
returns the last day of the preceding month.

So, for example, if you used:

DateSerial(Year(Date()), Month(Date()),0)

today, when Date() returns the 20th of January, the DateSerial() function
above would return the 31st of December, 2009.

Use that in a query and figure out how to specify the Year and Month, and I
think you've got a way to select your dates!

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Hi all,

I have a query that gives me the dates and account balances like in
the following example:

Original query:
Date             Account balance
2009-01-03    500
2009-01-07    300
2009-01-31    1000
2009-02-15     800
2009-02-28     800
2009-03-30     800
2009-04-28     2000
2009-04-30     2000
...

I now would like to extract only the values for the last day of a
month and put them in a new query. Keep in mind that the last day
could be 31st, 30th, 28th (29th). In addition, I have multiple years
of data.
Also, I need the percentage change from one month to another. As a
result, this second query should then looks like that:

Date             Account balance          %-Change
2009-01-31    1000                           0%
2009-02-28     800                            -20%
2009-03-30     800                            0%
2009-04-30     2000                          +150%
...

What do I have to do in order to come up with a query like this?

Regards,
Andreas
Click to expand...

Selecting last of month records could look like:
SELECT * FROM YourTable
WHERE YourDate=DateAdd("d", -1, DateAdd("m", 1, DateSerial(Year
(YourDate), Month(YourDate), 1)))


Groeten,

Peter
http://access.xps350.com
 
Hi all,

I have a query that gives me the dates and account balances like in
the following example:

Original query:
Date Account balance
2009-01-03 500
2009-01-07 300
2009-01-31 1000
2009-02-15 800
2009-02-28 800
2009-03-30 800
2009-04-28 2000
2009-04-30 2000
...

I now would like to extract only the values for the last day of a
month and put them in a new query. Keep in mind that the last day
could be 31st, 30th, 28th (29th). In addition, I have multiple years
of data.
Also, I need the percentage change from one month to another. As a
result, this second query should then looks like that:

Date Account balance %-Change
2009-01-31 1000 0%
2009-02-28 800 -20%
2009-03-30 800 0%
2009-04-30 2000 +150%
...

What do I have to do in order to come up with a query like this?

Regards,
Andreas
Click to expand...

As criteria on the Date Field write:
=DateSerial(Year([DateField]),Month([DateField])+1,0)

The above will return just records for the last day of the month of
the [DateField].
By the way, there are 31 days in March, not 30 as you
indicated above. ;-)
 
Put the following in one of the fields for the query: DatePart("d",[dob]) and
the following in the criteria: 28 Or 29 Or 30 Or 31
 
What if the last day of month was a holiday or weekend and therefore no data
on that date?
 
If you need the last entered date for each account within a particular
year/month, and not necessarily the very last day of that month, you would
have to use a subquery to get the maximum date for each year/month,
something like:
Select accountNumber, year(TheDate), month(TheDate), max(day(TheDate)) as
LastDayOfTheMonth
From MyTable
Group by accountNumber, year(TheDate), month(TheDate)

You could then join that query to your original table to select the actual
balance on that last day of the month.
 
As criteria on the Date Field write:
=DateSerial(Year([DateField]),Month([DateField])+1,0)
Click to expand...


@all
Thanks for giving me all these tips.

@fredg
This works particular well.

Now, what if I tell you that my date field is a combined date/time
field. Is it possible to ignore the time when performing the query?
Alternatively, I could place an extra entry for every last day of the
month with 11:59pm as time, such as 2009-12-31 11:59pm. How must the
search criteria (or SQL code) look like to eather ignore the time
completely (favourite solution) or search for the last day of the
month including 11:59pm as time?

@all
What about my problem regarding the calculation of the percentage
change from one month to another? Any suggestions for this issue?

Thanks a lot!

Regards,
Andreas
 
Since your criteria is strictly >= a given date, you don't need to do
anything because of the time. You'd only have to if your criteria was <= a
given date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

As criteria on the Date Field write:
=DateSerial(Year([DateField]),Month([DateField])+1,0)
Click to expand...


@all
Thanks for giving me all these tips.

@fredg
This works particular well.

Now, what if I tell you that my date field is a combined date/time
field. Is it possible to ignore the time when performing the query?
Alternatively, I could place an extra entry for every last day of the
month with 11:59pm as time, such as 2009-12-31 11:59pm. How must the
search criteria (or SQL code) look like to eather ignore the time
completely (favourite solution) or search for the last day of the
month including 11:59pm as time?

@all
What about my problem regarding the calculation of the percentage
change from one month to another? Any suggestions for this issue?

Thanks a lot!

Regards,
Andreas
 
Since your criteria is strictly >= a given date, you don't need to do
anything because of the time. You'd only have to if your criteria was <= a
given date.
Click to expand...

What do you mean with >= ? Is it possible that you misinterpreted the
">"-sign from the quoted text? The criteria so far is DateSerial(Year
([DateField]),Month([DateField])+1,0) .

Andreas
 
Ah, yes, you're right: I did misinterpret.

Instead of

=DateSerial(Year([DateField]),Month([DateField])+1,0)

as your criteria, use
= DateSerial(Year([DateField]),Month([DateField])+1,0) And <
DateSerial(Year([DateField]),Month([DateField])+1,1)
Click to expand...

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Since your criteria is strictly >= a given date, you don't need to do
anything because of the time. You'd only have to if your criteria was <= a
given date.
Click to expand...

What do you mean with >= ? Is it possible that you misinterpreted the
">"-sign from the quoted text? The criteria so far is DateSerial(Year
([DateField]),Month([DateField])+1,0) .

Andreas
 
Ah, yes, you're right: I did misinterpret.

Instead of

=DateSerial(Year([DateField]),Month([DateField])+1,0)

as your criteria, use
= DateSerial(Year([DateField]),Month([DateField])+1,0) And <
DateSerial(Year([DateField]),Month([DateField])+1,1)
Click to expand...
Click to expand...


OK, that works quite well.

Now, how do I have to incorporate a specific time in that search
criteria? For every last day of the month I have an extra entry with
11:59pm as time, e.g. 2009-12-31 11:59pm or 2009-11-30 -11:59pm. I
only want to filter for these specific entries, i.e. it is possible to
have some more entries for the last day of the month, but with a
different time. I don't want to filter those entries.
 
Ah, yes, you're right: I did misinterpret.
Click to expand...
Instead of
=DateSerial(Year([DateField]),Month([DateField])+1,0)
Click to expand...

as your criteria, use
= DateSerial(Year([DateField]),Month([DateField])+1,0) And <
DateSerial(Year([DateField]),Month([DateField])+1,1)
Click to expand...
Click to expand...

OK, that works quite well.

Now, how do I have to incorporate a specific time in that search
criteria? For every last day of the month I have an extra entry with
11:59pm as time, e.g. 2009-12-31 11:59pm or 2009-11-30 -11:59pm. I
only want to filter for these specific entries, i.e. it is possible to
have some more entries for the last day of the month, but with a
different time. I don't want to filter those entries.
Click to expand...

I figured it out. I needed to modify my criteria like this:

WHERE (((Test.datDate)>=DateSerial(Year([datDate]),Month([datDate])
+1,0) And (Test.datDate)<=DateSerial(Year([datDate]),Month([datDate])
+1,1) And Val(Format([datDate],"hhnn"))=2359))
 
I figured it out. I needed to modify my criteria like this:

WHERE (((Test.datDate)>=DateSerial(Year([datDate]),Month([datDate])
+1,0) And (Test.datDate)<=DateSerial(Year([datDate]),Month([datDate])
+1,1) And Val(Format([datDate],"hhnn"))=2359))
Click to expand...

Even simpler:

= DateAdd(DateSerial(Year([datDate]), Month([DateDate]) + 1, 0) + #23:59#
 
I figured it out. I needed to modify my criteria like this:
Click to expand...
WHERE (((Test.datDate)>=DateSerial(Year([datDate]),Month([datDate])
+1,0) And (Test.datDate)<=DateSerial(Year([datDate]),Month([datDate])
+1,1) And Val(Format([datDate],"hhnn"))=2359))
Click to expand...

Even simpler:

= DateAdd(DateSerial(Year([datDate]), Month([DateDate]) + 1, 0) + #23:59#
Click to expand...

I've learned from playing pool that when confronted with two different
potential shots, be certain that you have firmly settled upon one or
the other before taking the shot. If you don't, your mind might come
up with something in between, which is usually worse than either.
Personally, I prefer using the DateAdd() function to using arithmetic
functions directly on Date values. However, the reason for my
preference is not compelling enough to make me consider those whose
apply the arithmetic functions directly to Date values guilty of more
than a peccadillo, especially in cases like this one where using the
DateAdd() function to apply time can become slightly awkward.
Converting the time portion to seconds would remove some intuition
from the answer because the time portion would not be immediately
apparent without adding yet another date function or adding a few ' *
60's. Some might consider my choice in this situation to be
overrefined, but it is my programming style to choose a certain kind
of correctness in lieu of overall simplicity. I only ask that others,
when making the choice, at least nod at the tradeoff they are making
between a potential change in the Date/Time Data Type's underlying
implementation, however miniscule, versus the increase in the
probability of making a mistake due to the increased complexity of
using the DateAdd() function to add a time portion. It's great that
we, as database programmers, get to agonize over such life altering
decisions :-). Also, note that the OP's modified criteria is
incorrect.

James A. Fortune
(e-mail address removed)

Why are you wasting your time in IT? -- Walid Elsady

I attended the North American International Auto Show in Detroit this
week. Of all the cars I sat in, one of the BMW's gave me the best
behind-the-wheel experience. Disclaimer: I didn't get to experience
the cars from a few of the manufacturers represented, such as Jaguar,
because they had all their display vehicles locked. When I asked the
Jaguar representative if it would be possible for me to have a car
unlocked temporarily for comparison purposes, he replied that I should
make a trip to a dealership.
 
Back
Top