Automatically generated monthly reports

V

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
 
V

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
 
V

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
 
R

RobFMS

Ok

Let's step back to some basics.

How are you capturing the Begin Date and the End Date? Are you entering them
from a form? Are you entering them from a query that prompts you?

What are the Begin Date and End Date field names called?



--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
 
V

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.
 
V

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'?
 
D

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)"
 
V

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
 
V

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!!!!
 

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

Similar Threads

A2002 bug: Can't set RecordsetType 5
Mr. Kallal & Mr. Fenton: What did I do wrong? 55
Did Office 2003 SP2 fix this? 3
Select..Into Statement Options 7
DPAPI Crypt32.dll 1MB Limit issue 2
Syntax Error in From Clause - Nothing Changed...? 2
Print HTML with Process.Start 1
Return objects 6

Top