Last day of month in query

[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

 

[Stefan Hoffmann]

hi Andreas,

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

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 <--

 

[Jeff Boyce]

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.

 

[XPS350]

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

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

 

[fredg]

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

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. ;-)

 

[Lynn Trapp]

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

 

[KARL DEWEY]

What if the last day of month was a holiday or weekend and therefore no data
on that date?

 

[Paul Shapiro]

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.

 

[Andreas]

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

@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

 

[Douglas J. Steele]

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)

@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

 

[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.

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

 

[Douglas J. Steele]

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)

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.

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

 

[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)

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.

 

[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)

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.

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))

 

[John W. Vinson]

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))

Even simpler:

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

 

[James A. Fortune]

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))

Even simpler:

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

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.