Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    11

    Unanswered: Count between dates

    Hi there.Can anybady help me to calculate dates on filter form, between two dates.
    Example, i filter closed request between 26/03/14 and 25/04/14, and i want to seperate closed request from 26/03/14 to 25/04/14 and older request ( request before 26/03/14 ).
    I tried this formula on Form Header Text Box =Count([ReceptionDate] Between #srqDateFrom# And #srqDateTo#]) but not working.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Kallukallu View Post

    ...not working...
    This isn't very informative! How is it 'not working?' Are you getting incorrect results? An Error message? What?

    I suspect the problem is related to the fact that you're not using the U.S. date format, which ultimately Access requires. Have a look at Allen Browne's article on the subject

    International Dates in Access

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2011
    Posts
    11
    I getting error msg. on text box #Error. I tried US format date, i'ts same error.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    used in a formula?
    thats a new one onme me, use forumlae in spreadsheets, yes but a DB....

    what i think you need to do is use the dcount function in two spearate controls
    say your text box controsl are called LowerLimit and Upper Limit and contain valid dates

    =dcount("acolumn","atableorquery", "yourdatecoilumn between #" & format(lowerlimit,"yyyy/mm/dd") & "# and #" & format(upperlimit,"yyyy/mm/dd") & "#")
    the other control is
    =dcount("acolumn","atableorquery", "yourdatecoilumn < #" & format(lowerlimit,"yyyy/mm/dd") & "#")
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Posts
    11
    I tried all metod but no luck.I attached stample db to help me.
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so no queries in your db
    no forms

    what is it you have tried?

    assumign that:-
    you create a pair of textbox controls which your user defines the upper and lower limit of your date banding, called tblowerlimit and tbupperlimit
    you create a pair of text box controls which will containthe result of your calculation, called tbInBand and tbOlder

    then

    Code:
    tbinband.value = DCount("dataMbylljes", "tblRasti", "dataMbylljes between #" & Format(tbLowerLimit, "yyyy/mm/dd") & "# AND #" & Format(tbUpperLimit, "yyyy/mm/dd") & "#")
    tbolder.Value = DCount("dataMbylljes", "tblRasti", "dataMbylljes < #" & Format(tbLowerLimit, "yyyy/mm/dd") & "#")
    call that code from wherever you need it
    you could put it in, say the after update of tblowerlimit or tbupperlimit
    or in a command button
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2011
    Posts
    11
    Ok healdam, i am new in access and I not now how to adapt your idea. Ok disregard. I will navigate on google.
    Last edited by Kallukallu; 04-29-14 at 15:25.

Tags for this Thread

Posting Permissions

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