Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29

    Unanswered: Date range issue

    Hi everybody,

    I'm trying to have a report show only records inside of a date range. I'm opening the report with the docmd.openreport command, and setting the wherecondition argument to:

    Code:
    "[" & dateForm & "] BETWEEN #" & startDate & "# AND #" & endDate & "#"
    Now, for some reason, I'm getting records from the end date and on. Is there something wrong with my where condition or does the issue lie somewhere else? This issue is replicated on every report I try to open.
    nfw04 uses Access 2007

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have you debugged the string to find out what that whole thing gets parsed out as?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    In a test run it shows up as:

    [Date] BETWEEN #10/09/2008# AND #15/09/2008#
    nfw04 uses Access 2007

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have you made sure you're using the proper date format? I see you're using a European dd/mm/yyyy format...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    Oh, hmm...I don't really know anything about the date formats. How can I find out about them or find out what formats are correct?

    This format came from me simply typing "sept 10 08" into a date text box and it automatically converted it for me.

    EDIT: I changed my text boxes to a "long date" and it still stored the date as shown above when passed into my VBA subroutine. Not sure if that's what you're talking about, but thought I'd share.
    Last edited by nfw04; 07-27-09 at 18:04.
    nfw04 uses Access 2007

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    JET is a US designed database, it requires dates to be either ISO yyyy mm dd or US date mm dd yyyy
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    Quote Originally Posted by healdem
    JET is a US designed database, it requires dates to be either ISO yyyy mm dd or US date mm dd yyyy
    Ok awesome. Looks like I'm getting somewhere. How can I get Access to put my dates in US form? Like I said earlier, the format that I'm getting for my dates is a result of Access "interpreting" my entry.
    nfw04 uses Access 2007

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the format function

    eg
    format(mydatecolumn,"mm dd yyyy")

    you may need to tweak the format mask, Im not certain if its mm dd yyyy or MM dd yyyy. the help system should point you in the right direction
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    Quote Originally Posted by healdem
    use the format function

    eg
    format(mydatecolumn,"mm dd yyyy")

    you may need to tweak the format mask, Im not certain if its mm dd yyyy or MM dd yyyy. the help system should point you in the right direction
    Thanks a million. I appreciate the help from you and from Teddy as well.

    Do you have any idea how I could get Access to format my date entries correctly from the get go? I'm guessing it's because my locale is Canada, so we share a lot of European formatting rules.
    nfw04 uses Access 2007

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Your best bet is to store it as yyyy mm dd since that's the ISO standard.

    Formatting at the database level should involve maintaining as much consistency across disparate systems as possible. Formatting for human-readability is best executed as a function of the UI (form or report, in this case).
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    Thanks a lot for all the help guys. Issue solved and insight gained.
    nfw04 uses Access 2007

Posting Permissions

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