Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inbetween dates (2k)

    I'm trying to open a form where the info displayed is inbetween two dates. I've got two text boxes, one asking for the starting date, the other asking for the ending date. Could someone show me the code to be able to do this?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inbetween dates (2k)

    Name the text boxes you are using to store your dates, for example: txtBegDate and txtEndDate.

    In your query for the criteria use: Between Forms!frmName!txtBegDate and Forms!frmName!txtEndDate

    Replace frmName with the name of your form containing the input text boxes.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  3. #3
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inbetween dates (2k)

    I can get it working in the query, but I want to do it through code. This is what I have:

    DoCmd.OpenForm "Sort", , , [Date of Offense] > txtDate1.Value And < txtDate2.Value

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inbetween dates (2k)

    Try the below in the Form_Open event of your subform. Don't forget to change MyTableName to the table name where your records are stored.


    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim dtBeginningDate As Date
    Dim dtEndingDate As Date

    Set db = CurrentDb

    dtBeginningDate = CDate(Forms!DateEntryForm!Text0.Value)
    dtEndingDate = CDate(Forms!DateEntryForm!Text2.Value)

    strSQL = "SELECT MyTableName.*"
    strSQL = strSQL & " FROM MyTableName"
    strSQL = strSQL & " WHERE (((MyTableName.DATE) Between #" & dtBeginningDate & "# And #" & dtEndingDate & "#));"

    Me.RecordSource = strSQL

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inbetween dates (2k)

    Correction:

    Try the below in the Form_Open event of your subform. Don't forget to change MyTableName to the table name where your records are stored and the name of your form and the name of your text boxes.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim dtBeginningDate As Date
    Dim dtEndingDate As Date

    Set db = CurrentDb

    dtBeginningDate = CDate(Forms!MyFormName!MyTextBoxName.Value)
    dtEndingDate = CDate(Forms! MyFormName!MyTextBoxName.Value)

    strSQL = "SELECT MyTableName.*"
    strSQL = strSQL & " FROM MyTableName"
    strSQL = strSQL & " WHERE (((MyTableName.DATE) Between #" & dtBeginningDate & "# And #" & dtEndingDate & "#));"

    Me.RecordSource = strSQL

  6. #6
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inbetween dates (2k)

    its giving me an error when i try to run, saying that DAO.Database is not allowed

  7. #7
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inbetween dates (2k)

    someone suggested using:

    Dim Date1 As Date
    Dim Date2 As Date

    Date1 = txtDate1.Value
    Date2 = txtDate2.Value

    DoCmd.OpenForm "Sort", acNormal, , "Between #" & Date1 & "# and #" & Date2 & "#"

    But this is popping up with another error. Thought this might open some new waves of thought.

  8. #8
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inbetween dates (2k)

    Have you set a reference to a DAO library?

    Open any module. On the menu bar select Tools then References...

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inbetween dates (2k)

    Get rid of:

    'Dim db As DAO.Database
    'Dim rst As DAO.Recordset

    'Set db = CurrentDb

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Inbetween dates (2k)

    The problem with this is that you don't say what field is supposed to be between the dates.

    Here is some similar code, copied from a form where it works.

    Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCriteria = "[date of Event] Between #" & Format(Me![txtDate1], "mm/dd/yyyy") & " # and #" & Format(Me![txtDate2], "mm/dd/yyyy") & "#"
    stDocName = "frmTesting"
    DoCmd.OpenForm stDocName, , , stLinkCriteria


    I am not sure that you need to wrap the dates in the format function. Here in Australia we do, because we normally use dates in dd/mm/yyyy format.
    Regards
    John



  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inbetween dates (2k)

    Try this:

    DoCmd.OpenForm "Sort", , , "[Date of Offense] > = " & CDate(Me.[txtDate1]) & " And [Date of Offense] <= " & CDate(Me.[txtDate2])

    This assumes that [Date of Offense] is, in fact, a date field rather than date-formatted text, and contains a general date. Keep in mind that if you are using any kind of non-US date format on your machine, you'll run into problems comparing dates in what amounts to a SQL string, since SQL requires US date format of month/day/year. If the textboxes on your form are unbound, the values in them will be strings, even if they look like dates, and CDate will convert them to actual dates in the system date format of the machine.
    Charlotte

Posting Permissions

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