Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    20

    Unanswered: Search between two dates in

    I have a data base that tracks UI claims. I want to be able to track claims between creation dates. For instance, i want to be able to enter 01/01/2006 to 01/01/2007 and the report display all claims that were dated between those dates. I have a field that is labeled "due date" that is used to extract this data.

    Can anyone tell me step by step how to create a search that does this?

    Thanks so much!

    R

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    Why not use parameters in the query the report is based on?

    eg for the criteria for the due date field something like this

    Between [Enter start date] And [Enter end date]

    You should then be prompted for the dates.

    You could take this a step further and use values from a form for the parameters.

    Between Forms![DateForm]!StartDate And Forms![DateForm]!EndDate

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487

    Talking

    Like norie has stated, a better way would be to utilize two form fields for this sort of thing where as a Start Date and a End Date can be provided perhaps via a popup calendar and then that data provided within a filter or SQL criteria of some sort.

    But....

    If you're bent on using only one field for this sort of thing then get ready for a little coding. Lets assume.....

    - a single Form field will be used for date entry, be it either a single date entry (01/01/2006) or a date range entry (01/01/2006 To 01/01/2007). The name for this Form field is DateEntry.
    - There will be NO command button. The ENTER key is used upon the date entry field.
    - a filter will be used to display the required data within the current Form.
    - The Allow Filters property is set to Yes on the current Records Display Form.

    Here is how you might go about it (code not tested):

    Through a code window (VBE) for the Form DateEntry field's KeyDown event enter (or copy and paste) the following code:

    Code:
       ' Is there anything supplied in the DateEntry Field and
       ' if so was the ENTER Key pressed...
       If IsNull(Me.DateEntry) = False And KeyCode = 13 Then
          ' Yup..I quess there is so let's declare our
          ' variables.
          Dim StartDate As String       ' To hold the Start Date as a String.
          Dim EndDate As String         ' To hold the End Date as a String.
          Dim SDate As Date             ' To hold the Start Date
          Dim EDate As Date             ' To hold the End Date
          Dim Strg As String            ' To hold the contents of the DateEntry field.
          Dim CriteriaStrg As String    ' To Hold the Filter criteria.
          Dim L As Integer              ' To hold the start location of the word 'To'
                                        ' if available in the DateEntry field.
          
          ' Fill the Strg variable with the contents of the DateEntry field.
          Strg = Trim(Me.DateEntry)
          ' Fill the L variable with the start location of the 'To' word within
          ' the Strg variable (which now holds the contents of the DaeEntry field)
          ' if it exists. If it doesn't then L will equal zero (0) which we can
          ' then assume that only a single date is supplied. Notice that the contents
          ' of the Strg variable is checked in Uppercase. This is because we wont know
          ' how the word 'To' will be entered into the DateEntry field by any given
          ' user and we need a definate condition to compare to. One user might supply
          ' 'to' and another may supply 'To' and yet another might supply 'TO'. By
          ' doing it this way it really doesn't matter how the word 'To' is supplied.
          L = InStr(UCase(Strg), "TO")
          
          ' Is there a 'To' word start location...
          If L > 0 Then
             ' Yup...So we can naturally assume there is a Start and End date supplied.
             ' Load up the StartDate variable.
             ' Notice the 'Trim' function is used. This is used to remove any spaces
             ' that might be supplied before or after any date.
             StartDate = Trim(Left$(Strg, L - 1))
             ' fill the EndDate variable.
             EndDate = Trim(Mid$(Strg, L + 2, Len(Strg)))
          Else
             ' Nope...So we can have to assume there is only a Start date supplied.
             ' fill the StartDate variable.
             StartDate = Trim(Strg)
          End If
          
          ' If the StartDate variable actually contains a date then
          ' Fill the SDate date variable
          If StartDate <> "" Then SDate = CDate(StartDate)
          ' If the EndDate variable actually contains a date then
          ' Fill the EDate date variable
          If EndDate <> "" Then EDate = CDate(EndDate)
          
          ' Ok...Lets create our Filter criteria based on the data supplied...
          ' If there is a Start Date and the 'To' word is there but there is no End Date then...
          If StartDate <> "" And L > 0 And EndDate = "" Then
             ' Get the claim date located in the last entered record of the Claims table.
             Dim LastClaimDate As Date
             LastClaimDate = CDate(DLast("[yourTableClaimDate]", "[yourClaimsTableName]"))
             ' Set the filter criteria to pull up claim records range base on the supplied
             ' Start Date and the Last claim entered into table.
             CriteriaStrg = "[yourTableClaimDate] = #" & SDate & "# AND #" & LastClaimDate & "#"
          ' If there is just a single date and the word 'To' was not supplied then
          ElseIf StartDate <> "" And L = 0 Then
             ' Set the Filter criteria to pull up claim records that contain the supplied date.
             CriteriaStrg = "[yourTableClaimDate] = #" & SDate & "#"
          ' If there is no Start Date but the 'To' word IS supplied and the End Date is also
          ' supplied then...
          ElseIf StartDate = "" And EDate <> "" And L > 0 Then
             ' Get the claim date located in the First entered record of the Claims table.
             Dim FirstClaimDate As Date
             FirstClaimDate = CDate(DFirst("[yourTableClaimDate]", "[yourClaimsTableName]"))
             ' Set the filter criteria to pull up claim records range base on the supplied
             ' first claim entered into table and the End Date.
             CriteriaStrg = "[yourTableClaimDate] BETWEEN #" & FirstClaimDate & "# AND #" & EDate & "#"
          ' If Both the Start Date and the End Date are supplied (seperated by the 'To'
          ' word) then...
          ElseIf StartDate <> "" And EndDate <> "" Then
             ' Set the filter criteria to pull up claim records range base on the supplied
             ' Start Date and the End Date.
             CriteriaStrg = "[yourTableClaimDate] BETWEEN #" & SDate & "# AND #" & EDate & "#"
          Else
            ' If nothing is supplied then set the criteria to show ALL claims
            ' within the claims table.
            CriteriaStrg = ""
          End If
          
          ' Fill the Form Filter
          Me.Filter = CriteriaStrg
          ' Fire the Filter.
          If CriteriaStrg <> "" Then Me.FilterOn = True Else Me.FilterOn = False
       End If
    OK, OK...I know...it's excessive and there's this and there's that but it was fun . Remember...this was quickly done and not tested and most likely full of spelling mistakes like everything else I tend to type.

    The code looks rather intimidating but it's just a lot of commenting that makes it so.

    The whole idea is this. You supply the date range within a single form field and pull up the Claims records ranging between those dates using the Forms Filter property. Here are the different types entries you can make to view claim records:

    View All Records which contain this date:

    01/01/2006

    View Records that fall on and between 01/01/2005 to 03/12/2006:

    01/01/2005 To 03/12/2006

    OR

    01/01/2005To03/12/2006

    View All Records which range between the supplied date and the Last record entered into table:

    01/01/2006 To

    OR

    01/01/2006To

    View All Records which range between the first record entered into table and the date supplied:

    To 03/11/2006

    OR

    To03/11/2006

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

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