Trying to pull records for the current month

ASP Free Forums Sponsor:

Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

May 1st, 2008, 03:05 PM

bryceowen

Contributing User

Join Date: Aug 2007

Posts: 49

Time spent in forums: 1 Day 2 h 10 m 37 sec
Reputation Power: 6

VBScript - Trying to pull records for the current month

I have a database that tracks the number of certian charges customers call in and make. I've been asked to find a way to send regular emails each day that include the totals for a given day.

I was originally wirting something in ASP, but in my research, I discovered that VBscript is basically the same thing. That, and I can use "AT" to schedule the script to run.

Is there any resource I can look at to find what I need to do? The MDB file I'm using has the date field set to 'date' (mm/dd/yyyy) and I need something that can look at what today is, start at the first of the month and count to the current date. I've written a script that lets me pull just the current day's figures, but I don't know how enough to work with dates in vbs.

This is the code I have now and it works great for just pulling today's figures.

Code:

'-----------------------------------------
' declare variables
'-----------------------------------------
Dim Conn
Dim rs
Dim datetoday
Dim rmrtotal
Dim equipmenttotal
Dim servicetotal
Dim checktotal
Dim cctotal
Dim dailytotal
Dim body
Dim objCDO

'---------------------------------
'Pull today's date into variable and reset other values to zero
'---------------------------------
datetoday = date()

rmrtotal = 0
equipmenttotal = 0
servicetotal = 0
cctotal = 0
checktotal = 0
dailytotal = 0
'---------------------------------
'Create connection to database
'---------------------------------
Set Conn = CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\wwwroot\_private\master.mdb"

set rs = conn.Execute("SELECT * FROM payments WHERE paydate=#" & datetoday & "#")

'-----------------------------------------
'Do the math!
'-----------------------------------------
do until rs.EOF

	select case rs("reason")
		case "RMR"
			rmrtotal = rmrtotal + rs("amount")
		case "Add Equipment"
			equipmenttotal = equipmenttotal + rs("amount")
		case "Service"
			servicetotal = servicetotal + rs("amount")
	end select

	select case rs("paymenttype")
		case "Credit Card"
			cctotal = cctotal + rs("amount")
		case "Check"
			checktotal = checktotal + rs("amount")
	end select

	dailytotal = cctotal + checktotal

	rs.movenext
loop	

'-----------------------------------------
' prepare email body text
'-----------------------------------------
body = body & "<html><body><table border='1' cellspacing='0' width='*'><tbody>"
body = body & "<tr><td align='center'>Date</td><td align='center'>CC</td><td align='center'>Check</td><td align='center'>RMR</td><td align='center'>Equipment</td><td align='center'>Service</td><td align='center'>Total</td></tr>"
body = body & "<tr><td align='center'>" & datetoday & "</td><td align='center'>" & cctotal & "</td><td align='center'>" & checktotal & "</td><td align='center'>" & rmrtotal & "</td><td align='center'>" & equipmenttotal & "</td><td align='center'>" & servicetotal & "</td><td align='center'>" & dailytotal &"</td></tr>"
body = body & "</tbody></table></body></html>"

'-----------------------------------------
' compile and send email
'-----------------------------------------
Set objCDO = CreateObject("CDO.Message")
objCDO.To = "xxx@xxx.com"
objCDO.From = "xxx@xxx.com"
objCDO.Subject = "Total payments for " & datetoday
objCDO.HTMLBody = Body
objCDO.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'-----------------------------------------
'Name or IP of Remote SMTP Server
'-----------------------------------------
objCDO.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "192.168.1.9"
objCDO.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objCDO.Configuration.Fields.Update
objCDO.Send

'---------------------------------
'Cleanup
'---------------------------------
rs.Close
Set rs = Nothing
Set Conn = Nothing
Set objCDO = Nothing

Can anyone offer me an idea?

May 2nd, 2008, 05:28 AM

selwonk

Contributing User

Join Date: Jun 2004

Posts: 2,831

Time spent in forums: 5 Days 15 h 16 m 28 sec
Reputation Power: 57

Have a look at DateDiff():

http://www.techonthenet.com/access/functions/date/datediff.php

__________________
selwonk

May 2nd, 2008, 10:44 AM

bryceowen

Contributing User

Join Date: Aug 2007

Posts: 49

Time spent in forums: 1 Day 2 h 10 m 37 sec
Reputation Power: 6

I actually thought I was onto something with dateadd. Here's what I've added, but it doesn't work because dateadd evidently doesn't like variables:

Code:

datetoday = month(date) & "/" & 1 & "/" & year(date)
do until datetoday = date()
...
recordpulling, etc...
...
dateadd("d",1,datetoday)
loop

It keeps giving me "cannot use parentheses when calling a Sub" for the 'dateadd' line.

EDIT:
Nevermind. Duh... Forgot to make datetoday EQUAL the dateadd line...
datetoday = dateadd("d",1,datetoday)

Last edited by bryceowen : May 2nd, 2008 at 11:03 AM.

May 2nd, 2008, 11:24 AM

Wolffy

Slaprentice of Wolves

Join Date: Aug 2007

Location: Mossville, IL

Posts: 1,015

Time spent in forums: 1 Week 4 Days 2 h 3 m 50 sec
Reputation Power: 329

You're looking for records in the current month, yes? If so, try this query

Code:

SELECT * FROM payments 
WHERE MONTH(paydate) = MONTH(DATE())
    AND YEAR(paydate) = YEAR(DATE())

Comments on this post

bryceowen agrees: Genius! Thanks!

__________________
Wolffy
------------------------
Teaching people to fish.

Viewing: ASP Free Forums > System Administration > Windows Scripting > VBScript - Trying to pull records for the current month

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!