Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: parameter dialog

    The module below is used to generate three types of monthly reports. The reports’ record sources are queries created in the code. The first two reports created without any problems. When clicking on the last report, a parameter dialog box appeared prompting for ActShipDate before created the report. The OTshipping is a table and ActShipDate is a field in the OTShipping table. The table and the field is spelled correctly. Can anyone see where the problem(s) is. I’ve been looking at it for two days.

    This is section of the code that is displaying the parameter dialog box:
    Rem but = 2
    On Error Resume Next
    DB.QueryDefs.Delete ("PerLateQuery")
    On Error GoTo 0



    sqlstate = "SELECT OTShipping.Customer, TShipping.CustomerNumber, OTShipping.PartNumber, OTShipping.ProductType, Count(OTShipping.ID) AS NumShipments, Sum(-[qlate]) AS NumLate FROM OTShipping WHERE (OTShipping.ActShipDate >= " + sdate + " And OTShipping.ActShipDate <= " + edate + ") GROUP BY OTShipping.Customer, OTShipping.CustomerNumber, OTShipping.PartNumber, OTShipping.ProductType HAVING (((Sum(-[qlate]))<>0) AND (" & criteria & "));"



    Set tq = DB.CreateQueryDef("PerLateQuery", sqlstate)
    tq.SQL = sqlstate
    ‘’’***ActShipDate parameter dialog appears **

    DoCmd.OpenReport "LATENESS REPORT(Per Late)", A_PREVIEW
    DoCmd.CancelEvent
    DoCmd.SelectObject A_REPORT, "LATENESS REPORT(Per Late)"


    Here is the module where the three reports are being created:

    Select Case ProdType
    Case "AL"
    criteria = "1=1"
    Case Else
    criteria = "ProductType='" & ProdType & "'"
    End Select

    If but = 1 Then

    On Error Resume Next
    DB.QueryDefs.Delete ("rquery")
    On Error GoTo 0

    sqlstate = "SELECT * FROM OTShipping WHERE ((OTShipping.ActShipDate>=" + sdate + " AND OTShipping.ActShipDate<=" + edate + " and " & criteria & "));"
    'Debug.Print sqlstate
    Set tq = DB.CreateQueryDef("rquery", sqlstate)
    tq.SQL = sqlstate
    DoCmd.OpenReport "LATENESS REPORT(DATES)", A_PREVIEW
    DoCmd.CancelEvent
    DoCmd.SelectObject A_REPORT, "LATENESS REPORT(DATES)"
    Else
    If but = 0 Then
    On Error Resume Next
    DB.QueryDefs.Delete ("latequery")
    On Error GoTo 0

    sqlstate = "SELECT * FROM OTShipping WHERE ((OTShipping.ActShipDate>=" + sdate + " AND OTShipping.ActShipDate<=" + edate + ") AND (OTShipping.PL>='L')) ;"
    Set tq = DB.CreateQueryDef("latequery", sqlstate)
    tq.SQL = sqlstate
    DoCmd.OpenReport "LATENESS REPORT(DETAIL)", A_PREVIEW
    DoCmd.CancelEvent
    DoCmd.SelectObject A_REPORT, "LATENESS REPORT(DETAIL)"
    Else
    Rem but = 2
    On Error Resume Next
    DB.QueryDefs.Delete ("PerLateQuery")
    On Error GoTo 0



    sqlstate = "SELECT OTShipping.Customer, TShipping.CustomerNumber, OTShipping.PartNumber, OTShipping.ProductType, Count(OTShipping.ID) AS NumShipments, Sum(-[qlate]) AS NumLate FROM OTShipping WHERE (OTShipping.ActShipDate >= " + sdate + " And OTShipping.ActShipDate <= " + edate + ") GROUP BY OTShipping.Customer, OTShipping.CustomerNumber, OTShipping.PartNumber, OTShipping.ProductType HAVING (((Sum(-[qlate]))<>0) AND (" & criteria & "));"



    Set tq = DB.CreateQueryDef("PerLateQuery", sqlstate)
    tq.SQL = sqlstate
    ‘’’ActShipDate parameter dialog appears

    DoCmd.OpenReport "LATENESS REPORT(Per Late)", A_PREVIEW
    DoCmd.CancelEvent
    DoCmd.SelectObject A_REPORT, "LATENESS REPORT(Per Late)"

    End If
    End If


    DoCmd.Hourglass False
    End If

    End Function

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    Have you checked your sorting and grouping window for an order on ShippingDate?

  3. #3
    Join Date
    May 2002
    Posts
    395

    parameter dialog

    Where do I find the sorting and grouping window?
    The design grid in my query has the following on the ActualShippingDate:
    Field: ActShipDate
    Table: OTShipping
    Total: Where
    Sort: (no)
    Show: (no)
    Criteria: >=#8/1/02# and <=#8/31/02#

    The report(LATENESS REPORT(PER late) does print out the late shipments for the month. I believe the problem lies after the query(“PerLateQuery”) is created and before report(“LATENESS REPORT(Per Late)”) is created in opening report, that is where the parameter dialog popped up. The record source for the report is PerLateQuery query. I just can understand why ActShipDate in query written in code is fine but can’t reference it on the report.

    Set tq = DB.CreateQueryDef("PerLateQuery", sqlstate)
    tq.SQL = sqlstate
    ‘’’ActShipDate parameter dialog appears

    DoCmd.OpenReport "LATENESS REPORT(Per Late)", A_PREVIEW
    DoCmd.CancelEvent
    DoCmd.SelectObject A_REPORT, "LATENESS REPORT(Per Late)"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •