Automatically generated monthly reports

[vinfurnier]

Hi -

I'm trying to generate automatic monthly reports using MSAccess. I'm
close, but am not getting accurate results.

I've been using a parameter query with the "Between" function (for the
dates) to currently get results and it's been working fine. But
wanting to automate this procedure, I'd looked at some code from Arvin
Meyer

http://groups.google.com/group/micr...s+automatically&rnum=3&hl=en#8666885a040da0e3

and Rick Brandt

http://groups.google.com/group/micr...s+month+queries&rnum=1&hl=en#9b10f581ca80287a

but I'm falling short one day in March 2006. In other words, I do not
get the results for March 31, 2006 (I get March 1-30, 2006). I've
tried all sorts of combinations within both sets of code and am still
stumped.

Hopefully, it's a simple solution and someone out there has run into
the same issue and can advise.
Thanx in advance, vf
MSAccess 2000, WinXP

 

[vinfurnier]

Hi Rob -

I should have mentioned this, but there IS data to report on 3/31/06.
One any given day, there are between 6,000 and 12,000 records added to
this db.

Like I mentioned in the previous post, I've tried all types of
combinations within the code.
"WHERE DateFieldName BETWEEN DateSerial(Year(Date()), Month(Date()) -
1, 1) AND DateSerial(Year(Date()), Month(Date()), 0)"
I've changed or added 1's and 0's and even -1's & 2's just for
giggles.

I thought the code would've worked right off the bat too, but I'm
having issues with the last day of the month.
Thanx, vf

 

[vinfurnier]

The above WHERE statement is the exact one that I've used. But, like I
mentioned before, I have changed some of the 1's to 0's or 2's or even
-1's.

"WHERE DateFieldName BETWEEN DateSerial(Year(Date()), Month(Date())
-1,1) AND DateSerial(Year(Date()), Month(Date()), 0)"

I've also built the 2 Modules as well with the same result. Tho, it
does take almost forever to run.

Function BeginLastMonth() As Variant
BeginLastMonth = DateAdd("m", -1, DateSerial(Year(Date), Month(Date),
1))
End Function

Function EndOfLastMonth() As Variant
EndOfLastMonth = DateSerial(Year(Date), Month(Date), 0)
End Function

Now, in your criteria box of the query for your date report use the
expression:
Between BeginLastMonth() And EndOfLastMonth()

Thanx for being so prompt about this!!!
I'm still trying other options here as well, vf

 

[vinfurnier]

I'm pulling the dates from a Query and the field name is "Create_Dt".
This is the date that the record was created. This query only contains
only this date field.

 

[vinfurnier]

Correct.
And again, I'm just not getting March 31, 2006 in my totals. I get the
rest of March 06.

In that WHERE statement, am I supposed to declare anything specific?
Like Month = 'March'?

 

[Douglas J Steele]

Do your date fields contain time as well as date? (i.e.: did you use Now
instead of Date to populate them?)

If so, use

"WHERE DateFieldName BETWEEN DateSerial(Year(Date()), Month(Date())
-1,1) AND DateSerial(Year(Date()), Month(Date()), 1)"

 

[vinfurnier]

Doug, Rob -
Thanx a bunch!!!

I totally APOLOGIZE for not mentioning that this Create_Dt field DOES
have a time stamp associated with it.

Doug - your formula worked to a "T"
Rob - THANX SO MUCH for your attention to this matter!!!
vf

 

[vinfurnier]

I used that "BETWEEN" before for criteria, but I'm looking to
automatically generate this report on the first of the month. So, I
think this is the best solution to get me there.
Thanx again!!!!