Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    556
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Date Range (2002/SP2)

    I have a vacation report that is sorted by date in a field called VacDate. I created a form to allow the user to put in the start and end date, so that they can narrow down the focus of the report. After they enter the dates, they click on an OK button, which is tied to the following code:

    Dim stDocName As String
    Dim strCriteria As String
    strCriteria = "[VacDate] = '"Between "" & Me.txtBegDate & "and" & Me.txtEndDate & " ' "
    stDocName = "rptDayByDateSelect"
    DoCmd.OpenReport stDocName, acViewPreview, , strCriteria

    I'm real sure that I just have the syntax screwed up on the strCriteria line. Too many quotes or not enough or all in the wrong place. Can anybody help me fix my mess? Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Date Range (2002/SP2)

    Dates are not strings, so you shouldn't put quotes around date values. Instead, use # characters. Moreover, there must be a space before and after the And.

    strCriteria = "[VacDate] = '"Between #" & Me.txtBegDate & "# And #" & Me.txtEndDate & "#"

    If there is a chance that your database will be used by someone who has a system date setting different from the mm/dd/yy used in the USA, use

    strCriteria = "[VacDate] = '"Between #" & Format(Me.txtBegDate, "mm/dd/yyyy") & _
    "# And #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"

    This ensures that the criteria string uses US date format, regardless of the user's system settings.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    556
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Date Range (2002/SP2)

    Hans,

    Thank you for your help. I am still having a problem, I think I still might be missing something. I used your expression, I copied and pasted it, so I would get it right, and I am getting a
    Compile Error:

    Expected: End of Statement

    and the word "Between" is highlighted. I'm not sure what it is looking for. Any ideas? Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Date Range (2002/SP2)

    I'm sorry, I concentrated only on the dates, not on the string as a whole. You can't use =Between. I should have noticed that. Here is the corrected instruction:
    <code>
    strCriteria = "[VacDate] Between #" & Me.txtBegDate & "# And #" & Me.txtEndDate & "#"
    </code>
    or the international version
    <code>
    strCriteria = "[VacDate] Between #" & Format(Me.txtBegDate, "mm/dd/yyyy") & _
    "# And #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"</code>

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    556
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Date Range (2002/SP2)

    Hans,

    Don't be sorry, you are a genius! Thank you so much for your help. It works perfectly.

Posting Permissions

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