Last day of month in query

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,

On 20.01.2010 20:55, Andreas wrote:
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 --

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.

"Andreas" wrote in message
...
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

On 20 jan, 20:55, Andreas wrote:
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

On Wed, 20 Jan 2010 11:55:24 -0800 (PST), Andreas wrote:

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. ;-)
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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

--
Lynn Trapp
MCP, MOS, MCAS


"Andreas" wrote:

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
.

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

--
Build a little, test a little.


"Andreas" wrote:

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
.

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.

"KARL DEWEY" wrote in message
...
What if the last day of month was a holiday or weekend and therefore no
data
on that date?

--
Build a little, test a little.


"Andreas" wrote:

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?

On Jan 20, 9:41*pm, fredg wrote:

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

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
http://I.Am/DougSteele
(no e-mails, please!)

"Andreas" wrote in message
...
On Jan 20, 9:41 pm, fredg wrote:

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