Form Date Fields used as Query Params then passed on to Chart Report

[SnoWBunnY]

Hello!

I have a form with 2 date fields [txtDateStart] and [txtDateEnd], these 2 fields are used as parameters in my query, the syntax is right but then when I open my report I get the following message: "The Microsoft Jet database engine does not recognize "[the dates]" as valid field names or expression." When I try this with a report that contains text data, it is fine it works but when I try it with my chart report I keep getting that error.

Then, I took the date paramaters out of my query and decided to try playing around with the query that controls the row source of my chart in the report.

When I created the chart report with the wizard, this is the sql statement that I ended up with:

TRANSFORM Count(*) AS [Count]
SELECT TreatmentQuery.TreatmentDiscrepancy
FROM TreatmentQuery
WHERE (((TreatmentQuery.DateIncident) Between #1/1/2001# And #12/31/2001#))
GROUP BY TreatmentQuery.TreatmentDiscrepancy
PIVOT (Year([DateIncident]));

but if I try to change the WHERE clause to the following:
WHERE (((TreatmentQuery.DateIncident) Between [Forms]![frmDate]![txtDateStart] And [Forms]![frmDate]![[txtDateEnd]))
I get that same error as above. Why? Does anybody know how I get passed this one. How to use the fields from my form as parameters for my chart report?

Thanks!
=o)

 

[MazeWorX]

try


Between #" & Format$(Me.txtDateStart, "mm/dd/yyyy&quot & "# And #" & Format$(Me.txtDateEnd, "mm/dd/yyyy&quot & "# "


hth

 

[SnoWBunnY]

thanks for replying
I have a quick question though, where am I supposed to put that code? You have 'me.date' so that would mean that I would put it in the form that contains those 2 dates but the syntax you provide looks like it goes into a query?

once again thanks
I will be working on it some more tomorrow and maybe I will be able to figure it out ......

thanks
=o)

 

[MazeWorX]

You have a form with 2 date fields and a "preview" button.
in the OnClick event for the Preview button:


Private Sub Preview_Click()
If IsNull(me.txtDateStart) or IsNull (me.txtDateEnd) Then
MsgBox "Both dates are required"

Else
DoCmd.OpenReport "Report Name here", [acPreview],, "[DateIncident] Between #" & Format$(Me.txtDateStart, "mm/dd/yyyy&quot & "# And #" & Format$(Me.txtDateEnd, "mm/dd/yyyy&quot & "# "

End IF
End Sub

checks to see if the date fields contain data and if they do runs the report. if not they get a msg box with an ok button ... simple

if you don't want the date fields to be required then remove the if/else statement

DoCmd.OpenReport method:

Report Name,View,FilterName,Valid SQL Where clause without the Where

 

[murphysdad]

How would you be able to get this to report as a bar graph showing monthly comparisons between two sets of data? I have a similar issue where i am trying to graph incomplete work orders vs complete work orders(per month).

-lost and confused

 

[SnoWBunnY]

Thank you very much MazeWorX!
everything works fine
your help is greatly appreciated!
=o)

as for murphysdad's question if your dates are dynamic then just do what MazeWorX suggested, it works like a charm but if your dates are static you can do it from the chart wizard when creating a new report, after you have selected the fields for your graph (one of them will obviously be a date field), when you see the layout of your graph double click on date field and you will get 'group date by' options and 2 text fields to enter the 'between date and date'. In my case, my dates are never going to be static so MazeWorX' method is all good!
Good-luck bud!

B-)

 

[SnoWBunnY]

umm sorry, this date stuff is not quite working yet ... it keeps showing the same data everytime (all of the data actually)? I think that I got a little too excited and replied too fast but I will keep at it till I get it and let you guys know!

=o)

 

[MazeWorX]

make sure your query has no where clause. Let DoCmd.OpenReport supply the "Where" There is another way to do this but it involves considerably more code (-:

 

[murphysdad]

Thanks!!

Murphsdad

 

[SnoWBunnY]

Hello again!

Ok so here is what I did to make this work. In my form that contains the dates that I needed as paramaters for the chart in my report, we had to create a table on the fly. This worked very well. I managed to generate reports according to the specified year, in between 2 years, according to the specified month but my new problem is that I can't get my HAVING clause right to get results in between the months specified.
Here is a sample of my code that doesn't work:
(ps. TD will stand for TreatmentDiscrepancy because it is a long name and it makes my sql command a little complicated to look at)

Private Sub cmdOk_Click()
Dim SQLcommand As String

On Error Resume Next
DoCmd.DeleteObject acTable, "PivotSourceData"
On Error GoTo Err_cmdOk_Click

'if SelectTreatment of the frmReportsMenu form is
'null, display all of the treatment discrepancies
If IsNull(Forms![frmReportsMenu]!SelectTreatment) Then
SQLcommand = "SELECT TD.TDName,
IncidentTable.DateIncident,
Format([DateIncident],'mmmm yyyy') AS Monthh INTO PivotSourceData " & _
"FROM TypeOfPerson INNER JOIN
(TD INNER JOIN IncidentTable ON
TD.TD= IncidentTable.TD) ON
TypeOfPerson.PersonAffected =
IncidentTable.PersonAffected " & _
"GROUP BY TypeOfPerson.PersonAffected, TD.TD, TD.TDName,
IncidentTable.DateIncident, Format([DateIncident],'mmmm yyyy') " & _
"HAVING Format([IncidentTable].
[DateIncident], 'mmmm yyyy') BETWEEN
[BeginningDate] AND [EndingDate]
AND TypeOfPerson.PersonAffected ='2'
AND TD.TD<>'16';&quot;

'else display only the treatment discrepancy selected in
'the SelectTreatment list box of the frmReportsMenu form
Else
'same sql command goes here the only thing that
'changes is the &quot;treatment discrepancy&quot; in the having clause

End If

DoCmd.SetWarnings False
DoCmd.RunSQL SQLcommand
DoCmd.SetWarnings True

Debug.Print SQLcommand
DoCmd.OpenReport &quot;ReportName&quot;, acViewPreview

Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click

End Sub


All of the code above works except for the HAVING clause statement
&quot;HAVING (Format([IncidentTable].[DateIncident], 'mmmm yyyy') BETWEEN [BeginningDate] AND [EndingDate]) AND TypeOfPerson.PersonAffected ='2' AND TreatmentDiscrepancy.TreatmentDiscrepancy<>'16';&quot;

it gives me all of the dates of all of the years .... any suggestions, solutions ......

Thanks
=o)

 

[MazeWorX]

&quot;HAVING ((Format([IncidentTable].[DateIncident], 'mmmm yyyy') BETWEEN [BeginningDate] AND [EndingDate]) AND ((TypeOfPerson.PersonAffected)=2) AND ((TreatmentDiscrepancy.TreatmentDiscrepancy)<>16));&quot;


Also I noticed

Format([DateIncident],'mmmm yyyy') AS Monthh INTO PivotSourceData


hth Remember amateurs built the ark - professionals built the Titanic

[flush]