Results 1 to 11 of 11

Thread: date range

  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: date range

    can somebody help me figure out my problem re date range in filter by form, please? i tried using "between (date1) and (date2)" as my criteria in filter by form (date submitted field). it worked for the same year , but not if the year differs. i like to print records with date submitted between 06/17/2003 and 01/31/2004.

    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    THat should work, tho it might help to use # which is like ' except for dates.

    between #06/17/2003# and #01/31/2004#

    Regards

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    Originally posted by namliam
    THat should work, tho it might help to use # which is like ' except for dates.

    between #06/17/2003# and #01/31/2004#

    Regards


    thanks, i've tried that already but still didn't work. i'm using access 2000.

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    ? I am lost, i dont understand why it should work for 1 year but not for an overlapping period....

    The field your querying is a date format i hope...

    When querying the new period, what is going wrong?

    Regards

  5. #5
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    Check the format (the country settings) for the date field. It might be because of the d/m or m/d difference or the regional and language settings of the PC if these are OK.

    - Saqib

  6. #6
    Join Date
    Feb 2004
    Posts
    8
    Originally posted by namliam
    ? I am lost, i dont understand why it should work for 1 year but not for an overlapping period....

    The field your querying is a date format i hope...

    When querying the new period, what is going wrong?

    Regards
    yes, it's a date format. when querying the new period, it doesn't give me a range instead just a record (just 1) for 6/17/2003, some records in january and including records to date which should not be, bec february is not included in my criteria range.

  7. #7
    Join Date
    Feb 2004
    Posts
    8
    Originally posted by Saqib
    Check the format (the country settings) for the date field. It might be because of the d/m or m/d difference or the regional and language settings of the PC if these are OK.

    - Saqib
    thanks, i've checked it and it's okay. could it be because i'm using access as my front-end and mysql as my back-end? is there a possibility of conflict there?

  8. #8
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    Originally posted by ela
    thanks, i've checked it and it's okay. could it be because i'm using access as my front-end and mysql as my back-end? is there a possibility of conflict there?

    It is quite possible, if query processing is done at the server's end and not at the clinet's end which in this case it seems as you might be using ODBC connectivity to link to your mysql tables. In this case check if the enclosing character for date is # as in access and not some other character specific to mysql. Also if you're getting the DateFrom and DateTo form some text fields then you might want to use

    between cdate(txtdatefrom) and cdate(txtdateto)

    as your filter as cdate() will convert a string type to date type...

    - regards,
    Saqib

  9. #9
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Try passing the date like so:

    dd/mmm/yyyy (31/jan/2004)

    If its a date conversion problem that might solve it...

    otherwise try using dateserial(Year,Month,Day)

    After that i am just about lost....

    Regards

  10. #10
    Join Date
    Feb 2004
    Posts
    8

    date range

    namliam and saqib,

    thank you very much guys for your suggestions. i'm almost lost, too but the solution i found is very simple. i just separated the range of different year, something like this : between 06/17/2003 and 12/31/2003 or between 01/01/2004 and 01/31/2004, and there it gave me the correct report i needed!

    thanks again!

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    are you using odbc or passthru?
    odbc should be able to "translate" A's double-precision floats to mySQL format. in passthru, you might need to consider mySQL's date formats:
    You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

    As a string in either 'YYYY-MM-DD HH:MMS' or 'YY-MM-DD HH:MMS' format. A ``relaxed'' syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
    As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
    As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
    As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
    As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
    As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
    As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.
    dates are the next step in my mySQL(innodb) education, so this is a completely inexpert suggestion.

    izy
    currently using SS 2008R2

Posting Permissions

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