Results 1 to 2 of 2

Thread: Reports

  1. #1
    Join Date
    Sep 2003
    Raleigh, NC

    Unanswered: Reports

    I have use following codes to filter out my reports...this code wrks fine when i have only "txtStartdate" but when i have "txtStartdate" and "txtEnddate" the report doesn't filter out between those tow dates. I have try to figure out what's wrong but can't get any right ans.

    strReport = "rptOrder"
    strField = "HomeVisitor"
    strField2 = "ScheduleDate"

    Dim msg As String

    If IsNull(Me.cboHomeVisitor) Then
    msg = "Plese Select the Home Visitor Name from the list"
    End If

    If IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) Then
    msg = msg & vbCrLf & "You must select at least one date"
    End If

    If msg <> "" Then
    MsgBox msg
    Exit Sub
    End If

    If Not IsNull(Me.cboHomeVisitor) Then
    strWhere = "[HomeVisitor]= '" & Me![cboHomeVisitor] & "'"
    End If

    If Not IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " And " & strField2 & ">" & Format (Me.txtStartDate, conDateFormat)
    ElseIf Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " And " & strField & "<" & Format(Me.txtEndDate, conDateFormat)
    strWhere = strWhere & " And " & strField2 & ">" & Format(Me.txtStartDate, conDateFormat)
    strWhere = strWhere & " And " & strField2 & "<" & Format(Me.txtEndDate, conDateFormat)
    End If

    ''strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
    ' & " And " & Format(Me.txtEndDate, conDateFormat)

    DoCmd.OpenReport strReport, acViewPreview, , strWhere

  2. #2
    Join Date
    Jun 2003
    Don't know if this is EXACTLY what you're looking for but since no one else answered your question I'll throw it out with the hope that it might help somehow.

    The free downloadable sample database at uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

    And here is how the query by form concept can work.

    On the invoices dialog there are the following controls:
    InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
    InvDateMax with DefaultValue of =Date()
    InvRepNum with DefaultValue of *
    InvCustNum with DefaultValue of *

    Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
    DoCmd.OpenForm "frmInv"

    And of course there could be a button to open a report the same way:
    DoCmd.OpenReport "rptInv", acViewPreview

    The invoices form (frmInv) has RecordSource property of qryInv.

    And the qryInv query's criteria for the InvDate field has:
    Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

    And the qryInv query's criteria for the RepNum field has:
    Like [Forms]![frmInvDialog]![InvRepNum]

    And the qryInv query's criteria for the CustNum field has:
    Like [Forms]![frmInvDialog]![CustNum]

    One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown. And to counter that you might consider creating the query's SQL statement dynamically so that the criteria on a particular field isn't used unless needed.
    J. Paul Schmidt, Freelance Web and Database Developer
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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