Set criteria for previous month

[Mike Hughes]

I have a query where a parameter value must be entered when the query is run.
I run this query each month. The parameter value is [YYYYMM]. I would like to add something to the criteria which would enter the prior month when the query is run.
An example would be the query is run in October 2011 and I want the criteria to pick up the YYYYMM of September.

 

[vbaInet]

It's possible but what happens when the user spells the name incorrectly? Also, your parameter value of [YYYYMM] isn't equivalent to October 2010. There's a space in the input.

The best thing to do is to use a pop-up form so you can perform some validation.

 

[Mike Hughes]

I'm sorry the returned YYYYMM format would be 201110 for october

 

[vbaInet]

And do the users enter the date exactly in that format?

 

[Mike Hughes]

Yes the would

 

[plog]

If you put this in your criteria section it will automatically calculate the prior months code in the format you need:

Format(DateAdd("m",-1,Date()),"yyyymm")

 

[vbaInet]

In that case, you can use something like this in the criteria:

DateSerial(Left([YYYYMM] & "", 4), Val(Right([YYYYMM] & "", 2)) - 1, "1")

 

[Mike Hughes]

vbaInet
When I enter your code "as is" in the criteria field for the date I'm still asked to enter the [YYYYMM]

PLOG
When I enter your code "as is" in the criteria field for the date the query just runs and runs.

Either of you have any other suggestions?

 

[vbaInet]

Isn't that what you already do? Get prompted to enter the date? Or is it fed from a textbox on your form?

 

[Mike Hughes]

Yes that is what I do now, but I want the system to enter the previous month and current year for me.

Sorry if I confussed you.

 

[Brianwarnock]

What is in the field that the criteria is being applied to

 

[vbaInet]

What is in the field that the criteria is being applied to

... or show us the SQL statement as is. In code tags please.

 

[Mike Hughes]

I just found something wrong with the query causing it to run and run. I corrected that and use
Format(DateAdd("m",-1,Date()),"YYYYMM")
And that did the trick.
Thanks to both of you for your time and help.
Mike

 

[Brianwarnock]

Good, I wondered why that was not working

Brian