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;
Microsoft Access

Avatar of undefined
Last Comment
jjafferr

8/22/2022 - Mon
jadedata

You have a reference in your sql or recordsource that is mispelled or does not exist in the query
jadedata

run the query alone and see if you get asked for a parameter
mustish1

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

Thanks.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rey Obrero (Capricorn1)




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



jjafferr

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
Rey Obrero (Capricorn1)

jjafferr
isn't that what i said???

how r u?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mustish1

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

mustish1

ASKER
Its not a subform.
mustish1

ASKER
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,
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rey Obrero (Capricorn1)

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.
mustish1

ASKER
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.
Rey Obrero (Capricorn1)

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mustish1

ASKER
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;
mustish1

ASKER
Yes this field is in the table tbl_webmail.
Rey Obrero (Capricorn1)

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Rey Obrero (Capricorn1)

as far as the grouping of CRC, you can do it in the report by using

  -the report wizard
or
 -sorting and grouping
mustish1

ASKER
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.
Rey Obrero (Capricorn1)

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mustish1

ASKER
Yes it was ask me for WC_Date. Thank You Sir my problem is solved.
jjafferr

Nice work Cap :)

jaffer