Always Pick Last Month's $

G

Gwen H

How do I change the snipped of SQL code below to force it to always choose
the last month?

Last(Iif(Assistant_Retail_Manager_Activities.Month BETWEEN #01/01/2005# AND
#03/31/2005#,Assistant_Retail_Manager_Activities.Deposits_Outstanding,0)) AS
DO_Qtr1

This is from a quarterly query that sums some fields, and is *supposed* to
use the last month's numbers on other fields (mixture of "sum" and "last").
The problem is, if we filter and sort the table from which the results come
in different ways, then Last doesn't always pick the last month's data.
Instead, it picks what it interprets to be the last entry in the result set.

Thank you!
 
K

KARL DEWEY

Use this for your FIELD in the query grid --
X: DatePart("yyyy",[YourDateField])&DatePart("m",[YourDateField])

Use this as Criteria in the query grid --
DatePart("yyyy",Date()-Day(Date()))&DatePart("m",Date()-Day(Date()))
 
G

Gwen H

Um, as someone who is rather ignorant about SQL functions, would you mind
giving me a brief explanation of what this code will achieve? I was looking
at using Max or DMax on the Month field to make it return the last month from
the result set, but I'm not sure how to use that in my SQL code snippet. If
DatePart will work too ... let's stick with what works.

Thank you,
Gwen H

KARL DEWEY said:
Use this for your FIELD in the query grid --
X: DatePart("yyyy",[YourDateField])&DatePart("m",[YourDateField])

Use this as Criteria in the query grid --
DatePart("yyyy",Date()-Day(Date()))&DatePart("m",Date()-Day(Date()))


Gwen H said:
How do I change the snipped of SQL code below to force it to always choose
the last month?

Last(Iif(Assistant_Retail_Manager_Activities.Month BETWEEN #01/01/2005# AND
#03/31/2005#,Assistant_Retail_Manager_Activities.Deposits_Outstanding,0)) AS
DO_Qtr1

This is from a quarterly query that sums some fields, and is *supposed* to
use the last month's numbers on other fields (mixture of "sum" and "last").
The problem is, if we filter and sort the table from which the results come
in different ways, then Last doesn't always pick the last month's data.
Instead, it picks what it interprets to be the last entry in the result set.

Thank you!
 
K

KARL DEWEY

My response was not SQL code but it was criteria for your query date field.
I will break it down.
Date()-Day(Date()) is the current system date from your computer
minus the day of month number of the current date. Today is the 1st,
therefore today minus 1 equal the last day of last month.
DatePart("m",Date()-Day(Date())) extracts the numberical representation
of last month.
DatePart("yyyy",Date()-Day(Date())) extracts the numberical
representation of the year of last month.
DatePart("yyyy",Date()-Day(Date()))&DatePart("m",Date()-Day(Date()))
are concatenating the year (2005) and the month (6). This sets the criteria.

Putting DatePart("yyyy",[YourDateField])&DatePart("m",[YourDateField]) in
the field row of the query grid display your date field as YYYYM to match the
criteria.

If you want a display for your report then add another field in the field
row like --
Last Month: Format ([YourDateField],"mmm")& " " &
Format([YourDateField],"yyyy")
You can use 4 m's for it to spell out the month in full.

Gwen H said:
Um, as someone who is rather ignorant about SQL functions, would you mind
giving me a brief explanation of what this code will achieve? I was looking
at using Max or DMax on the Month field to make it return the last month from
the result set, but I'm not sure how to use that in my SQL code snippet. If
DatePart will work too ... let's stick with what works.

Thank you,
Gwen H

KARL DEWEY said:
Use this for your FIELD in the query grid --
X: DatePart("yyyy",[YourDateField])&DatePart("m",[YourDateField])

Use this as Criteria in the query grid --
DatePart("yyyy",Date()-Day(Date()))&DatePart("m",Date()-Day(Date()))


Gwen H said:
How do I change the snipped of SQL code below to force it to always choose
the last month?

Last(Iif(Assistant_Retail_Manager_Activities.Month BETWEEN #01/01/2005# AND
#03/31/2005#,Assistant_Retail_Manager_Activities.Deposits_Outstanding,0)) AS
DO_Qtr1

This is from a quarterly query that sums some fields, and is *supposed* to
use the last month's numbers on other fields (mixture of "sum" and "last").
The problem is, if we filter and sort the table from which the results come
in different ways, then Last doesn't always pick the last month's data.
Instead, it picks what it interprets to be the last entry in the result set.

Thank you!
 
G

Gwen H

So, what if I wanted to subtract one month from the current month? I will not
always be running this report on the first of the month. Can I change

Date()-Day(Date()) to Date()-Month(Date()) ?

And how do I use it in the following SQL snippet to tell it to always select
last month's figures?

Last(Iif(Assistant_Retail_Manager_Activities.Month BETWEEN #01/01/2005# AND
#03/31/2005#,Assistant_Retail_Manager_Activities.Deposits_Outstanding,0)) AS
DO_Qtr1

Like this?

Iif(Assistant_Retail_Manager_Activities.Month=DatePart
"yyyy",Date()-Month(Date()))&DatePart("m",Date()-Month(Date())),Assistant_Retail_Manager_Activities.Deposits_Outstanding,0) AS DO_Qtr1

I'm sorry to be so detailed in my questions, but a lot is riding on my
finding a solution to this problem. I have to make sure it works 100%
correctly.

Thank you!

Gwen H
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top