Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2012
    Posts
    31

    Unanswered: Problem with Date range

    Anyone can help me of my problem of my dates

    This is a scenario i'm using vbAccess and the i'm askin' what would be the exact format with the two dates..
    Let say for example...

    I'm going to create a report base on the two dates but the problem here is when I'm going to have a report last december 20,2012 to January 8, 2013 I cannot retrieved anymore the data with in that dates...I tried to have another dates example from December 1,2012 to December 31,2012 It's quite good the data is there..I think the problem is with the year...


    Any help plsss...

    Tnx in advance..

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    What is the problem? You don't say what happens when you run the query.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Query like there dates in a usa format mm/dd/yyyy
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off make certain you are storing dates using the date/time datatype

    then make certain when you set a date and you want to se it to todays date use the date() function NOT the NOW() function which returns the current system date AND time

    if you want to retrieve rows bewtween a certain date range then you have lots of chocies
    use the BETWEEN operator
    eg WHERE MyDate BETWEEN lowervalue AND higher value
    OR
    WHERE MyDate >= #adateliteral# AND MyDate <= #anotherdateliteral#
    use the US dateformat mm/dd/yyyy and enclose it in hash marks eg #01/08/2013#
    OR
    use the inbuilt date time functions for access such as
    MS Access: Functions - Category
    WHERE MyDate BETWEEN datediff('d', -100,date())
    which will return all rows that are 100 days or less than todays date
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2012
    Posts
    31

    Still having problems

    These are the examples of date that i want to retrieve in my report:
    1/5/2013
    1/4/2013
    1/9/2013
    1/9/2013
    1/2/2013
    1/8/2013

    The data that is being retrieve are:
    1/5/2013
    1/4/2013
    1/2/2013
    1/8/2013

    This is my expression
    >=1/1/2013 And <=1/9/2013

    The problem is why is it that the two 1/9/2013 datas are not included in the report?

    tnx...to those who replies yesterday...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what data type is the column?
    ..is it string/text or DateTime
    what is the actual SQL you are using (assuming you are using the query designer, switch the view to SQL (icon under the file menu top left).

    this problem is most likely to be an incorrect datatype, where you are suign string NOT date time to store the original value
    OR
    you are not qualifying the date (effectivley telling Access that the values are a date type) eg >= #1/1/2013# And <= #1/9/2013#

    assuming you have defined the underlying storage for the column to datetime then it could be how you set the value for the date/ Access/JET use a numeric value to store date times, the integer part is the number fo days since (IIRC 31/12/1899) the decimal part is the proportion of the the day ie .5 = 12:00:00 as so on. so how have you set the date values.

    lastly are you certain that there are other rows that would be returned
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2012
    Posts
    31
    My datatype is Date/Time......


    my query is this one..

    SELECT Badge.BadgeActivation FROM Badge WHERE Badge.BadgeActivation>=1/1/2013 AND Badge.BadgeActivation <=1/9/2013

    Then i cannot retrieved tha data that belongs to 1/9/2013...

    I already used the Between operator but still the same problem...

  8. #8
    Join Date
    Dec 2012
    Posts
    31
    The values that i set is like this 1/1/2013 11:12:13 AM and so on

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You need to delimit your date values with hashes (#):
    Code:
    SELECT
    Badge.BadgeActivation
    FROM
    Badge
    WHERE
    Badge.BadgeActivation BETWEEN #1/1/2013# AND #1/9/2013#;
    Last edited by weejas; 01-09-13 at 08:54. Reason: typo
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your problem is that you have included a time element
    you are specifying the upper date limit to be 01/09/2013
    but your data is 01/09/2013 11:12:13, so your limit discards that row as it is not less than or equal to 01/09/13

    how do you set the value for the date value?

    is the time relevant? if not don't store it.

    fixes
    either increase the upper limit by one and request dates less than

    eg
    BadgeActivation>=1/1/2013 AND Badge.BadgeActivation <1/10/2013
    ..im assuming you are using the US style date format

    OR fix the data problem so you don't store times
    or specify a time element in the query
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Dec 2012
    Posts
    31
    The date and time is really important sir.
    That's right weejas

    But my actual codes is like this...

    SELECT
    Badge.BadgeActivation
    FROM
    Badge
    WHERE
    Badge.BadgeActivation >=[Forms]![ChooseBadgeReport]![dteFrom] And <=[Forms]![ChooseBadgeReport]![dteTo];

    where:

    Forms]![ChooseBadgeReport]![dteFrom]=date where date covered starts eg:1/1/2013

    <=[Forms]![ChooseBadgeReport]![dteTo]=date where date covered ends eg:1/9/2013

    So how can i put the hashes(#) in this kind of codes?

  12. #12
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You don't. In this case, you require the form's controls to be correctly formatted. If the format of the text boxes holding the dates is anything other than one of the accepted Date formats, you're likely to have a problem.

    (Please note that if you had mentioned that your query was mediated via a form, you might have got a more useful answer considerably sooner...)
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you don't want rows which are less than or equal to 01/09/2013
    you want rows that are
    less than or equal to 01/09/2013 23:59:59
    OR
    less than 01/10/2013
    ]
    how you go about that is upto you
    you could add one to the specified data using, say the dateadd function
    OR add the timestamp
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Dec 2012
    Posts
    31
    Yeah iwant this data 01/09/2013 23:59:59 healdem

Posting Permissions

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