Link to home
Start Free TrialLog in
Avatar of mustish1
mustish1

asked on

Prompt to enter value again

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;
Avatar of jadedata
jadedata
Flag of United States of America image

You have a reference in your sql or recordsource that is mispelled or does not exist in the query
run the query alone and see if you get asked for a parameter
Avatar of mustish1
mustish1

ASKER

No it not ask in the query any thing but it ask in the form.

Thanks.



is this query the record source of the report?

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;

try it without the where clause

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

and open the report the way you are doing it from the form



is frmWeb_Mailfilter a Form or a SubForm?

Why do you have a Criteria when you open a Report, AND a where in the query?
one of them is enough, so try it with one of them.

jaffer
jjafferr
isn't that what i said???

how r u?
I change the query but it still ask me the date prompt. WM_Date is not in report as report based on query

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 & "')"

Its not a subform.
I think 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.  But how to i put the WM_Date in query as i dont want to group on date.

Thanks,
mustish1

1. what is the record source of your report?

   post it here

2 .In your filter you included the WM_date field, you have to add this field to the query where you are using as
the record source of your report.
Record source is the query. Filter i am sorry i dont understand where i use that filter i mean in the query.

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

Thanks.
then  why are you trying to open the report with a where condition in this line

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

using  WM_Date  as field for the filter.



do you have a field WM_Date  from  tbl_WebMail?
In the post some one told me try it without WM_Date thats why i remove it. My actual query was:

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;
Yes this field is in the table tbl_webmail.
try this as the record source of your report
{query you posted}

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;

and run the report from the form after filling out txtdate1 and txtdate2  using this line

DoCmd.OpenReport "rpt_qrywebmail2", acViewPreview
as far as the grouping of CRC, you can do it in the report by using

  -the report wizard
or
 -sorting and grouping
Yes now it works but i have other filter too in my filterform and that is CRC. 2nd why when i run this line it prompt extra for date?

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

Thanks.
what is the exact message?

it is treating non existing field from the record source  as input parameter of the query.

try this query

SELECT tblWebMail.CRC
FROM tblWebMail
WHERE tblWebMail.CRC=[WC_Date]

it will ask you for WC_Date
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes it was ask me for WC_Date. Thank You Sir my problem is solved.
Nice work Cap :)

jaffer