Prompt to enter date again

[Anna]

Hi:
When i run my report it ask me a prompt to Enter Date, I already enter
date from the parameter form, I dont know why it ask

me to enter date. The date variable is in the query but i didnt define
into the report

Private Sub cmd_prntrpt_Click()
On Error GoTo Err_cmd_prntrpt_Click
If IsNull(Me.ddlcrc) = False Then
WCriteria = "[crc] = '" & Me.ddlcrc & "'"
End If

If IsNull(Me.ddlwm_desc) = False Then
If Len(WCriteria) > 0 Then
WCriteria = WCriteria & " AND [wm_desc] = '" & Me.ddlwm_desc &
"'"
Else
WCriteria = "[wm_desc] = '" & Me.ddlwm_desc & "'"
End If
End If

If IsNull(Me.txtDate1) = False And IsNull(Me.txtDate2) = False Then
If Len(WCriteria) > 0 Then
WCriteria = WCriteria & " AND ([WM_Date] BETWEEN #" & _
Me.txtDate1 & "# AND #" & Me.txtDate2 &
"#)"
Else
WCriteria = "[WM_Date] BETWEEN #" & _
Me.txtDate1 & "# AND #" & Me.txtDate2 & "#"
End If
Else
If IsNull(Me.txtDate1) = False Then
If Len(WCriteria > 0) Then
WCriteria = WCriteria & " AND [WM_Date] = #" & Me.txtDate1 &
"#"
Else
WCriteria = "[WM_Date] = #" & Me.txtDate1 & "#"
End If
ElseIf IsNull(Me.txtDate2) = False Then
If Len(WCriteria > 0) Then
WCriteria = WCriteria & " AND [WM_Date] = #" & Me.txtDate2 &
"#"
Else
WCriteria = "[WM_Date] = #" & Me.txtDate2 & "#"
End If
End If
End If

Dim stDocName As String
DoCmd.OpenReport "rpt_qrywebmail2", acPreview, , WCriteria
Forms!frmWeb_Mailfilter.Visible = False
Exit_cmd_prntrpt_Click:
Exit Sub

Err_cmd_prntrpt_Click:
MsgBox Err.Description
Resume Exit_cmd_prntrpt_Click

End Sub


QUERY
-----
SELECT tbl_WebMail.CRC, Sum(tbl_WebMail.WM_Amt) AS Totamnt
FROM tbl_WebMail
WHERE (((tbl_WebMail.WM_Date) Between
[Forms]![frmWeb_Mailfilter]![txtdate1] And
[Forms]![frmWeb_Mailfilter]![txtdate2]))
GROUP BY tbl_WebMail.CRC;

 

[J. Goddard]

Hi -


The query must have all of [crc], [wm_desc] and [wm_date] as fields,
even if you don't have them in the report. None of the report controls
should be named crc, wm_desc or wm_date you will have to modify it.
Access gets confused if you do that.

Since you are passing the date and other criteria to the report through
WCriteria, you don't need the data criteria in the query as well. It's
never a very good idea to include form control references in a query anyway.

But, since you need a number of fields in the criteria, it might be
better just to have the record source for the report as tblWebMail, and
let the report do the required grouping and summing.


John

 

[Anna]

Now this time i change the query and my form too but it still prompt
for date.

I dont know why its prompting for date as i already put date in my form

SELECT tbl_WebMail.CRC, Sum(tbl_WebMail.WM_Amt) AS Totamnt
FROM tbl_WebMail
GROUP BY tbl_WebMail.CRC;


QUERY IN FORM

 

[J. Goddard]

Its because the query for the report does not have the date in it. The
where condition in the docmd.openreport line is evaluated by the report,
not the form. The report records are selected by the report, not the
form, so the report has to have the date available to it ( i.e. in the
query).

John

 

[Anna]

Is there any way i put date in my query without grouping it?

Thank You.

 

[J. Goddard]

Yes, you can use the date in a where clause without grouping it, for
example from one of my own databases:

SELECT Expenses.[Product Type], Sum(Expenses.[Purchase total]) AS
[SumOfPurchase total]
FROM Expenses
WHERE (((Year([purchase date]))=2000))
GROUP BY Expenses.[Product Type];

But, you cannot do it this way in your case.

You are being prompted for the date because you are referring to the
date in the OpenReport command Criteria, and the report source query
does not have the date in it (as a selected field)

Change the query to a simple select query (so it does not need the GROUP
BY clause) which includes the date, then do the grouping and summing in
the report. From what I can see of your requirement, the report will
have no detail lines - only group footers, which is where totals usually go.

John

Is there any way i put date in my query without grouping it?

Thank You.

Now this time i change the query and my form too but it still prompt
for date.

I dont know why its prompting for date as i already put date in my form

SELECT tbl_WebMail.CRC, Sum(tbl_WebMail.WM_Amt) AS Totamnt
FROM tbl_WebMail
GROUP BY tbl_WebMail.CRC;


QUERY IN FORM
-------------------
DoCmd.OpenReport "rpt_qrywebmail2", acViewPreview,
wherecondition:="([WM_Date] between #" &
Forms.frmWeb_Mailfilter.txtDate1 & "# and #" &
Forms.frmWeb_Mailfilter.txtDate2 & "# and [crc] = '" & Me.ddlcrc & "')"