Need to create a parameter query to show the previous months results.

[browninaz]

Hey guys,

I know I'm leaning on you like a crutch, but I have until Jan. 1 to get this file up and going.

I would greatly appreciate it if one of you could kindly give me the criteria to show the previous months results in a query. I know this is probably a dumb question, and I searched google for a minute, but I have a very large list of tasks to complete in just a couple of weeks.

Thanks in advance to all the programmers and advanced users out there helping this novice!

 

[jdraw]

could kindly give me the criteria to show the previous months results in a query.

If you have a query to show whatever data you want, you then add a constraint
to limit records to those with the Month of some date field = last month.

Suppose you had a query

Select allfields 
from MyTable 
[COLOR="Blue"]where
Month(RecordDate) = Month(Date) -1[/COLOR]

The Where clause constrains records to those from Last Month ( I am reading previous Month to be Last Calendar Month(NOV) based on today's Date (12/12/2012 DEC).

 

[John Big Booty]

In your query you would need to have a calculated field that uses the following;

[URL="http://www.techonthenet.com/access/functions/date/datepart.php"]DatePart[/URL]("m", [YourDateField])

then under that in the criteria you could use;

DatePart("m", [URL="http://www.techonthenet.com/access/functions/date/dateadd.php"]DateAdd[/URL]( "m", -1, [URL="http://www.techonthenet.com/access/functions/date/date.php"]Date()[/URL]))

 

[John Big Booty]

Too slow :banghead:

 

[browninaz]

Hey jdraw, thanks for such a quick response. I am a serious novice at SQL and VBA until I start my classes in January, so can you please tell me if I enter this expression in the criteria box in the ContractDate field of my query, and what is the proper syntax?

I know I'm asking a lot and it probably sounds like I have no idea what I'm doing, and in most cases I don't yet, but I am taking the steps to learn the programming language I need to know, but sadly I'm not quite there yet.

 

[John Big Booty]

If you are just going to do it in a query it would look something like;

 

[jdraw]

Just like John said.


Who is slow this time!!!

 

[John Big Booty]

You've got to let me win one or two

 

[browninaz]

Got it!!!

That worked like a champ! Thanks to the both of you!

 

[browninaz]

But wait... Instead of creating 11 copies of the query using different criteria in each, how do I create a parameter to ask the user how many months they want to see?

 

[John Big Booty]

You could put an unbound text box on your form, and the criteria might look something like;

DatePart("m",DateAdd("m",-[Forms]![YourFormName]![YourTextBoxName],Date()))

You would need to put some validation in the code that you are using to open the query to ensure that your user had entered a valid number.

 

[John Big Booty]

Sorry, no that will only give you a record X months ago, it would need to be;

Between DatePart("m",DateAdd("m",-[Forms]![YourFormName]![YourTextBoxName],Date())) And DatePart("m",DateAdd("m",-1,Date()))

 

[browninaz]

O.K., let me know if I have this right; Create a form from the query I just created (qryMonthlyDetailSalesReport), add an unbound text box to the form, add this expression: DatePart("m",DateAdd("m",-[Forms]![YourFormName]![YourTextBoxName],Date())), and please forgive my inexperience in this, but this will do what? The reason I ask is, I am just about to shut down for the night and start again fresh tomorrow night, and I need something to chew on while I work tomorrow.

 

[John Big Booty]

No you would have a form that simply has an unbound text box, into which your user is able to insert the number of past months they wish to view. You then have a button that opens your query (or a form that uses the query as it's Record Source).

 

[John Big Booty]

Even better simply have a combo box that allows your user to select the number of months they wish to see, it's default could be one, this would eliminate the need to validate what the user has entered in the text box.

 

[browninaz]

Excellent, John Big Booty. Thanks for all your help tonight. I will build that form tomorrow and thank you then when it works!

 

[John Big Booty]

Here's a very quick sample

 

[browninaz]

Thanks John Big Booty. I will break that db down tomorrow. Hopefully you'll be around tomorrow night for more questions. Have a good evening.