asked on

Access 2007: getting dates as names in chart from sql query

Hello, I've made a query with the following code:
DaSELECT Imported.[Modified By],
Count(1) AS UserCount,
(Select Count (*) from Imported) AS TotCount,
Round([Usercount]*100/[TotCount],2) AS Userpercent,
Sum(IIf([Date Modified]=Date()-14,1,0)) AS Day14,
Sum(IIf([Date Modified]=Date()-13,1,0)) AS Day13,
Sum(IIf([Date Modified]=Date()-12,1,0)) AS Day12,
Sum(IIf([Date Modified]=Date()-11,1,0)) AS Day11,
Sum(IIf([Date Modified]=Date()-10,1,0)) AS Day10,
Sum(IIf([Date Modified]=Date()-9,1,0)) AS Day9,
Sum(IIf([Date Modified]=Date()-8,1,0)) AS Day8,
Sum(IIf([Date Modified]=Date()-7,1,0)) AS Day7,
Sum(IIf([Date Modified]=Date()-6,1,0)) AS Day6,
Sum(IIf([Date Modified]=Date()-5,1,0)) AS Day5,
Sum(IIf([Date Modified]=Date()-4,1,0)) AS Day4,
Sum(IIf([Date Modified]=Date()-3,1,0)) AS Day3,
Sum(IIf([Date Modified]=Date()-2,1,0)) AS Day2,
Sum(IIf([Date Modified]=Date()-1,1,0)) AS Day1,
Sum(IIf([Date Modified]=Date(),1,0)) AS Day0,
Sum(IIf([Date Modified]>Date()-7,1,0)) AS Last7Days,
Sum(IIf([Date Modified]>Date()-30,1,0)) AS Last30Days
FROM Imported
WHERE (((Imported.[Modified By]) Is Not Null))
GROUP BY Imported.[Modified By];

I am wanting to make a histogram using Day0 - Day14 in a form chart. I tried to define the row source as:
SELECT qryAudit.[Modified By],
Sum(qryAudit.Day14) AS Date()-14,
Sum(qryAudit.Day13) AS Date()-13,
Sum(qryAudit.Day12) AS Date()-12,
Sum(qryAudit.Day11) AS Date()-11,
Sum(qryAudit.Day10) AS Date()-10,
Sum(qryAudit.Day9) AS Date()-9,
Sum(qryAudit.Day8) AS Date()-8,
Sum(qryAudit.Day7) AS Date()-7,
Sum(qryAudit.Day6) AS Date()-6,
Sum(qryAudit.Day5) AS Date()-5,
Sum(qryAudit.Day4) AS Date()-4,
Sum(qryAudit.Day3) AS Date()-3,
Sum(qryAudit.Day2) AS Date()-2,
Sum(qryAudit.Day1) AS Date()-1,
Sum(qryAudit.Day1) AS Date()
FROM qryAudit
GROUP BY qryAudit.[Modified By];

However, this does not work because Date() is reserved. I want the legend and axis of my chart to give the actual date (I.e. Date()-1 would give 2/24/11 etc...) does anyone know how I need to end this to get these statistics?

Gustav Brock

You can't use a function to name the alias:

Sum(qryAudit.Day14) AS Date()-14

You will have to build the SQL string in VBA:

strSQL = strSQL & "Sum(qryAudit.Day14) AS " & Format(DateAdd("d", -14, Date), "m\/d\/yy") & ", "

etc.
Then use strSQL as the recordsource.

/gustav

Gustav Brock

or probably:

strSQL = strSQL & "Sum(qryAudit.Day14) AS " & Format(DateAdd("d", -14, Date), "\[m\/d\/yy\]") & ", "

Bevos

ASKER

So I will need to define 15 vars in VBA strSQL0-14?

Gustav Brock

No ... the next line would read:

strSQL = strSQL & "Sum(qryAudit.Day13) AS " & Format(DateAdd("d", -13, Date), "\[m\/d\/yy\]") & ", "

/gustav

Bevos

ASKER

I guess I'm not understanding. I tried to put this in the frmUserProgress which contains the chart1 which I will use 'Select strSQL' as the record source.

Dim strSQL As String
strSQL = strSQL & "Sum(qryAudit.Day14) AS " & Format(DateAdd("d", -14, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day13) AS " & Format(DateAdd("d", -13, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day12) AS " & Format(DateAdd("d", -12, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day11) AS " & Format(DateAdd("d", -11, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day10) AS " & Format(DateAdd("d", -10, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day9) AS " & Format(DateAdd("d", -9, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day8) AS " & Format(DateAdd("d", -8, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day7) AS " & Format(DateAdd("d", -7, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day6) AS " & Format(DateAdd("d", -6, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day5) AS " & Format(DateAdd("d", -5, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day4) AS " & Format(DateAdd("d", -4, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day3) AS " & Format(DateAdd("d", -3, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day2) AS " & Format(DateAdd("d", -2, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day1) AS " & Format(DateAdd("d", -1, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day0) AS " & Format(DateAdd("d", -0, Date), "\[m\/d\/yy\]") & ", "

Thanks Gustav,
Bevo

Gustav Brock

That's just the middle of it. You have to build a complete SELECT statement (using qryAudit) as you wrote yourself.
Then, when ready, assign it as the recordsource (or what it is called for charts which I don't work with in Access):

MyChart.RecordSource = strSQL

/gustav

Bevos

ASKER

Okay, I've been trying this:

Dim strSQL As String
strSQL = "SELECT Imported.[Modified By], Count(1) AS UserCount, (Select Count (*) from Imported) AS TotCount, Round([Usercount]*100/[TotCount],2) AS Userpercent, Sum(IIf([Date Modified]=Date()-14,1,0)) AS Day14, Sum(IIf([Date Modified]=Date()-13,1,0)) AS Day13, Sum(IIf([Date Modified]=Date()-12,1,0)) AS Day12, Sum(IIf([Date Modified]=Date()-11,1,0)) AS Day11, Sum(IIf([Date Modified]=Date()-10,1,0)) AS Day10, Sum(IIf([Date Modified]=Date()-9,1,0)) AS Day9, Sum(IIf([Date Modified]=Date()-8,1,0)) AS Day8, Sum(IIf([Date Modified]=Date()-7,1,0)) AS Day7, Sum(IIf([Date Modified]=Date()-6,1,0)) AS Day6, Sum(IIf([Date Modified]=Date()-5,1,0)) AS Day5, Sum(IIf([Date Modified]=Date()-4,1,0)) AS Day4, Sum(IIf([Date Modified]=Date()-3,1,0)) AS Day3, Sum(IIf([Date Modified]=Date()-2,1,0)) AS Day2, Sum(IIf([Date Modified]=Date()-1,1,0)) AS Day1, Sum(IIf([Date Modified]=Date(),1,0)) AS Day0, Sum(IIf([Date Modified]>Date()-7,1,0)) AS Last7Days, Sum(IIf([Date Modified]>Date()-30,1,0)) AS Last30Days"
From Imported
WHERE (((Imported.[Modified By]) Is Not Null))GROUP BY Imported.[Modified By]"
strSQL = strSQL & "Sum(qryAudit.Day14) AS " & Format(DateAdd("d", -14, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day13) AS " & Format(DateAdd("d", -13, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day12) AS " & Format(DateAdd("d", -12, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day11) AS " & Format(DateAdd("d", -11, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day10) AS " & Format(DateAdd("d", -10, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day9) AS " & Format(DateAdd("d", -9, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day8) AS " & Format(DateAdd("d", -8, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day7) AS " & Format(DateAdd("d", -7, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day6) AS " & Format(DateAdd("d", -6, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day5) AS " & Format(DateAdd("d", -5, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day4) AS " & Format(DateAdd("d", -4, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day3) AS " & Format(DateAdd("d", -3, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day2) AS " & Format(DateAdd("d", -2, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day1) AS " & Format(DateAdd("d", -1, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day0) AS " & Format(DateAdd("d", -0, Date), "\[m\/d\/yy\]") & ", "
Me.Graph1.RowSource = strSQL

But I can't seem to get it. The error highlighting stops on 'GROUP' which it says is an unexpected end of statement. Do you know what might be wrong?

Bevo

Bevos

ASKER

I made some changes so I'm not getting errors with the code (I'm sure something is still botched), but I get the new error: "An error occurred while sending data to the OLE server (the application used to create the object)."

I changed the code to:
Dim strSQL As String
strSQL = "SELECT qryAudit.[Modified By], qryAudit.[UserCount], qryAudit.[TotCount], qryAudit.[Userpercent], qryAudit.[Day14], qryAudit.[Day13], qryAudit.[Day12], qryAudit.[Day11], qryAudit.[Day10], qryAudit.[Day9], qryAudit.[Day8], qryAudit.[Day7], qryAudit.[Day6], qryAudit.[Day5], qryAudit.[Day4], qryAudit.[Day3], qryAudit.[Day2], qryAudit.[Day1], qryAudit.[Day0], qryAudit.[Last7Days], qryAudit.[Last30Days]"
strSQL = strSQL & "Sum(qryAudit.Day14) AS " & Format(DateAdd("d", -14, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day13) AS " & Format(DateAdd("d", -13, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day12) AS " & Format(DateAdd("d", -12, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day11) AS " & Format(DateAdd("d", -11, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day10) AS " & Format(DateAdd("d", -10, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day9) AS " & Format(DateAdd("d", -9, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day8) AS " & Format(DateAdd("d", -8, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day7) AS " & Format(DateAdd("d", -7, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day6) AS " & Format(DateAdd("d", -6, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day5) AS " & Format(DateAdd("d", -5, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day4) AS " & Format(DateAdd("d", -4, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day3) AS " & Format(DateAdd("d", -3, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day2) AS " & Format(DateAdd("d", -2, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day1) AS " & Format(DateAdd("d", -1, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day0) AS " & Format(DateAdd("d", -0, Date), "\[m\/d\/yy\]") & ", "

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bevos

ASKER

Thanks so much for all your help Gustav. I tried pasting the code you corrected into VBA but it is still giving me that: "An error occurred while sending data to the OLE server (the application used to create the object)." I tried adding a "Dim strSQL as String" before the code, but that didn't help. Sorry for not being able to trouble shoot this on my own, but I am new to SQL and VBA.

Bevo

Bevos

ASKER

Got it, changed it to this:
Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT qryAudit.[Modified By], "
strSQL = strSQL & "Sum(qryAudit.Day14) AS " & Format(DateAdd("d", -14, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day13) AS " & Format(DateAdd("d", -13, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day12) AS " & Format(DateAdd("d", -12, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day11) AS " & Format(DateAdd("d", -11, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day10) AS " & Format(DateAdd("d", -10, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day9) AS " & Format(DateAdd("d", -9, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day8) AS " & Format(DateAdd("d", -8, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day7) AS " & Format(DateAdd("d", -7, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day6) AS " & Format(DateAdd("d", -6, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day5) AS " & Format(DateAdd("d", -5, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day4) AS " & Format(DateAdd("d", -4, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day3) AS " & Format(DateAdd("d", -3, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day2) AS " & Format(DateAdd("d", -2, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day1) AS " & Format(DateAdd("d", -1, Date), "\[m\/d\/yy\]") & ", "
strSQL = strSQL & "Sum(qryAudit.Day0) AS " & Format(DateAdd("d", -0, Date), "\[m\/d\/yy\]") & " "
strSQL = strSQL & "FROM qryAudit GROUP BY qryAudit.[Modified By];"
Me.Graph1.RowSource = strSQL
Me.Graph1.Requery
End Sub

Bevos

ASKER

Jawsome!

Gustav Brock

Great!

/gustav