Date question (should be easy!) 1

[deBassMan]

Hi all
I have a table of transactions which include a date field.

I need to produce a column chart of the last six monthly total number of transactions.

The chart will ignore current month and start with previous month, then work backwards to previous month etc for six months.

I am using a recordset and SQL queries but am struggling with the dates (and year change) - any ideas?

 

[tb]

If you work with DateAdd in SQL then the changing of year should not be a problem ...

Simple exercise in Q-Analyser:
declare @date datetime

set @date = '2004/01/01'
print @date
set @date = dateadd("m",-1,@date)
print @date

??


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[deBassMan]

Thanks for that TB

but how do I code the SQL statement to select all transactions that have a transaction date that falls in the previous month?

 

[tb]

Post what you have so far and I'll help you


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[deBassMan]

Ideally I need to SELECT only those records that fall in the previous month ....

Here's the relevant code so far ...



'return all recs from previous month
mTargetDate = Format(DateAdd("m", -1, Date), "mmm yyyy")

strsql = "SELECT * FROM tblReferrals"

Set rs = db.OpenRecordset(strsql)

rs.MoveLast
Debug.Print "Previous month total recs: " & rs.RecordCount

 

[tb]

And... the WHERE clause?

Assuming that Date in this ..
mTargetDate = Format(DateAdd("m", -1, Date), "mmm yyyy")
is the system date ...

You'll need to get the first of that month and the last day of the month or ..is the date in the table only mmm yyyy?

If only mmm yyyy in the table then...

mTargetDate = "'" & Format(DateAdd("m", -1, Date), "mmm yyyy") & "'"

WHERE "DateField" = mtargetDate

If the day is stored with then you'll need some calculations...(not tested)

dim date1 as date
dim date2 as date

date1 = Format(date, "mmm yyy 01") (or whatever you use) - This will be the fisrt of this month
Date2 = date1 - Set to fisrt of this month
Date1 = DateAdd("m", -1, Date1) - This will give you the first of last month
Date2 = DateAdd("d", -1, Date2) - This will give you the last day of the previous month

"WHERE DateField BETWEEN " & "'" & Date1 & "' AND '" & Date2 & "'"

Hope this helps
Good luck


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[deBassMan]

thanks for your quick response tb

system date is used to determine current month

the date stored is a full date

I was hoping to SELECT on current month-1 (and subsequently -2,-3 etc) - perhaps using dateserial

p.s watch out for those frisbees

 

[tb]

Just thought of it now that you mentioned it ...
Yeah one hit me

Try this

"WHERE Datepart("m",Datefield) = '" & Datepart("m", Dateadd("m",-1,Date)) & "'"

Tried it in QA (just a bit modified) and it didn't give an error

Cheers


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[deBassMan]

thanks for that tb

the code now looks a bit like this ...

mTargetDate = DatePart("m", DateAdd("m", -1, Date))
strsql = "SELECT * FROM tblReferrals WHERE Datepart('m',RefDate) = " & mTargetDate
Set rs = db.OpenRecordset(strsql)
rs.MoveLast
Debug.Print "Previous month total recs: " & rs.RecordCount

I repeat the above line decrementing the the previous months - now if I could just figure out a loop ....