Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates to filter report (2000)

    I have read other posts about dates on forms for filtering reports but hopefully I'm not repeating a problem.
    I'll be using two text boxes on a form txtBeginDate and txtEndDate to filter a report.
    The first thing I need to do is populate the text boxes with default values then validate after any updates.
    The default for BeginDate should be the date of the the earliest completion project. (EndDate is the current date - done this bit)
    Basically BeginDate should not be before the date of the first completed project and EndDate should not be in the future,
    BeginDate should also be before EndDate - these bits I think I can do.

    My problem is retrieving the date of the earliest completed project, to use both as the default for BeginDate and to validate any date entered in txtBeginDate.

    I created this query which returns the value I need.
    SELECT Min(tblSiteInfo.CompletionDate) AS MinOfCompletionDate
    FROM tblSiteInfo;

    I'm sure this is easy but I just can't figure out how to use it in code or as an expression in the default value property for a text box. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    You can use
    DFirst("CompletionDate","tblSiteInfo")
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    Thanks, discovered DMin was actually the function I needed.
    I have a question regarding my train of thought of this matter.
    Is using text boxes to set a date range for printing the best way to do this, would combos containing actual date values from my table be better? I think the text box option is probably the right way to go but any opinion is helpful.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    If you need a date existing in the table, use a combobox, otherwise a textbox.
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    Having trouble manipulating strings for the filter.

    In code I have used
    strSiteName = "SiteName = Forms![frmReportsDialog]!lstSelectSite"
    and then
    DoCmd.OpenReport "rptTier1Compliance", PrintMode, , strSiteName
    to select a record by a name in a list box. I'm happy with the sql for this but I'm getting confused when I need to get a bit more complex.

    I'm trying to set the where clause for the string strDateRange in the line below
    DoCmd.OpenReport "rptTier1Compliance", PrintMode, , strDateRange

    I've used a parameter query in the report record source criteria which is ok but I can't seem to transfer if to code to set the value for the string strDateRange

    I've tried strDateRange = "CompletionDate = Between [Forms]![frmReportsDialog]![BeginningDate] And [Forms]![frmReportsDialog]![EndingDate]"

    but it doesn't work and is therefore clearly wrong. I doesn't work if I only use the value for either BeginningDate or EndDate so I'm guessing I'm way off course here.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    For the string use <pre>strSiteName = "SiteName = '" & Forms![frmReportsDialog]!lstSelectSite & "'"
    </pre>

    For the dates use <pre>strDateRange = "CompletionDate = Between #" & [Forms]![frmReportsDialog]![BeginningDate] & _
    "# And #" & [Forms]![frmReportsDialog]![EndingDate] & "#"</pre>

    Francois

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    For some reason I couldn't reply to your other post.

    I have tried the code
    strDateRange = "CompletionDate = Between #" & [Forms]![frmReportsDialog]![BeginningDate] & "# And #" & [Forms]![frmReportsDialog]![EndingDate] & "#"

    which produces the string

    CompletionDate = Between #12/02/1999# And #12/02/2002#

    But the report won't preview. Something is happening as the cursor changes to the egg timer but no report. Also the line below the DoCmd for print does not run.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    You may not have = between CompletionDate and Between.
    <pre>strDateRange = "CompletionDate Between #" & [Forms]![frmReportsDialog]![BeginningDate] & _
    "# And #" & [Forms]![frmReportsDialog]![EndingDate] & "#"
    </pre>

    I didn't see it when I took over your code, sorry <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Did this solve the problem ?
    Francois

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    Again, can't reply to the latest post, the button seems to be missing.
    The code now works but not in the way I expected.
    I'm playing with some sample data containing only 3 records. The text boxes on the form default to the min and max date values in the table.
    The code to run and filter the report runs from a button on the form.

    I am actually usually an option group to change the field used to filter for the report. Hopefully the code below should explain it:

    On Error GoTo Err_PrintReports
    ' This procedure used in Preview_Click and Print_Click Sub procedures.
    ' Preview or print report selected in the ReportToPrint option group.
    ' Then close the Print Sales Reports Dialog form.

    Dim strSiteName As String
    Dim strDateRange As String

    strSiteName = "SiteName = Forms![frmReportsDialog]!lstSelectSite"

    strDateRange = "CompletionDate Between #" & [Forms]![frmReportsDialog]![BeginningDate] & "# And #" & [Forms]![frmReportsDialog]![EndingDate] & "#"


    Select Case Me!fraReportFilter
    Case 1
    If IsNull(Forms![frmReportsDialog]!lstSelectSite) Then
    DoCmd.OpenReport "rptTier1Compliance", PrintMode
    Else
    MsgBox (strSiteName)
    DoCmd.OpenReport "rptTier1Compliance", PrintMode, , strSiteName
    End If
    Case 2
    DoCmd.OpenReport "rptTier1Compliance", PrintMode, , strDateRange

    End Select

    DoCmd.Close acForm, [frmReportsDialog]

    Exit_PrintReports:
    Exit Sub

    Err_PrintReports:
    Resume Exit_PrintReports

    End Sub

    If I run it, leaving in these defaults I would expect to see all three records but I don't.
    The one with the earliest date is ommitted.

    Is my understanding of the Between clause therefore wrong or is the code not doing what it should?

    I really appreciate your help with all of this. I'm learning so much right now.

    Futher food for though:
    Would it also be possible change the way the report is sorted in the same block of code acording to the option chosen. i.e if all Sites are printed (case 1) sort in alphabetical order, if by date range (case range) sort by ascending date order.

    Darsha

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    In your previous post you use #12/02/1999# and #12/02/2002#
    Is this 12 december or 2 february ?
    Francois

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    If it is 12 february, we have to play with format:

    <pre>strDateRange = "CompletionDate Between #" & _
    Format([Forms]![frmReportsDialog]![BeginningDate],"mm/dd/yyyy") & _
    "# And #" & Format([Forms]![frmReportsDialog]![EndingDate],"mm/dd/yyyy") & "#"</pre>


    I'm looking to code for the sorting problem.
    Francois

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    For the sorting use the following code in the On Open event of the report:
    <pre>Private Sub Report_Open(Cancel As Integer)
    Select Case [Forms]![frmReportsDialog]!fraReportFilter
    Case 1
    Me.OrderBy = "SiteName"
    Case 2
    Me.OrderBy = "CompletionDate"
    End Select
    Me.OrderByOn = True
    End Sub
    </pre>

    You'll have to remove the line
    DoCmd.Close acForm, [frmReportsDialog]
    from your code as the report code need to read the fraReportFilter control on this form.
    This implicate also that if the report is opened when the form is not open it will ask for [Forms]![frmReportsDialog]!fraReportFilter.
    Francois

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to filter report (2000)

    Thanks for all your help.
    Right now I'm trying to get to grips with Not In List and will probably be making a post today - I'm beginning to realise just how complex Access is!

    Darsha

Posting Permissions

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