Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2007
    Posts
    5

    Unanswered: Count dates matching criteria in MS Access

    Dear Sir/Madame,

    I have a query that looks like this:

    Name Date1 Date2 Date3 Date4
    John 13-Jan-07 20-Jan-07
    Sam 16-Jan-07 18-Jan-07
    Mike 21-Nov-06 16-Jan-07 18-Jan-07 20-Jan-07

    The query is designed to list all customers who have had appointments on dates ranging from 16-Jan-07 to 20-Jan-07. My intent is to program Access to calculate for me in a report how many times customers had visited the office from 16-Jan-07 to 20-Jan-07. The answer, according to this example, would be 6.
    Thank you in advance for your help.

    Best Regards,

    Glen
    Last edited by Ortley; 01-21-07 at 10:31.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, sorry

    the number of customers who had appointments from 16-Jan-07 to 20-Jan-07 is three -- John, Sam, and Mike

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you tried to use SQL to count the number of records?

    Code:
    SELECT Count(*)
    FROM (yourtable)
    WHERE (yourdatefield) BETWEEN (date1) AND (date2)
    -GeorgeV

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by r937
    nope, sorry

    the number of customers who had appointments from 16-Jan-07 to 20-Jan-07 is three -- John, Sam, and Mike

    Hey Rudy,

    I think our poster was refering to how many times a customer visited the office, not how many different customers visited.
    Quote Originally Posted by Ortley
    My intent is to program Access to calculate for me in a report how many times customers had visited the office from 16-Jan-07 to 20-Jan-07.
    In which case, John visited once, Sam twice, and Mike went 3 times, for a total of 6.
    Me.Geek = True

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, nick, but i was trying to point out something very important -- computers don't do what you want them to do, they do what you tell them to do

    if you want "how many appointments" then you should not ask for "how many customers"

    he did ask "how many customers" and i answered the question accurately
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    too true Rudy,

    but to be fair, he didn't ask "how many customers", he asked for "how many times customers", but either way you bring up a valid point.
    Me.Geek = True

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Ortley
    calculate for me in a report how many times customers had visited the office
    Quote Originally Posted by r937
    yeah, nick, but i was trying to point out something very important -- computers don't do what you want them to do, they do what you tell them to do

    if you want "how many appointments" then you should not ask for "how many customers"

    he did ask "how many customers" and i answered the question accurately
    But good point - accuracy is always key

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, you just quoted ortley as "originally" saying "how many times customers had visited the office"

    that is not what he originally posted

    nick, he changed his original post after i responded
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Haha, I missed that detail too!

    What a sly bean.

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    I missed that as well

    my bad
    Me.Geek = True

  11. #11
    Join Date
    Jan 2007
    Posts
    5
    I apologize for the confusion. I want to count how many visits were on 16, 18, and 20 Jan only since this time period represents a week when our office is open. I would like to be able to display a report that lists this number for me. I created a text box with the following expression to count the visits in the date1 field that equal 16-Jan:

    =DCount("[DATE1]","QRYCLAIMSWEEKLYCOUNT","[DATE1]=39098")

    Is there a way for me to write a SQL statement that would simply count the visits that occurred from 16-Jan to 20-Jan? There are eight date fields: date1, date2.....date8. If so, where do I write the SQL statement? Do I create a text box and write the statement in the control source of the date tab? I'd appreciate any help you can provide. Thanks so much for the support.

    Glen

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You could always repeat that expression for the other 3 dates and then count the totals?
    It's not a very neat way of doing it but it is the simplest.

    NB: you can set "Visible = False" on a textbox's properties - it'll still calculate but not be shown.


    OR try this

    Code:
    =if([date1]=(1stdatetotest),1,
      if([date1]=(2nddatetotest),1,
        if([date1]=(3rddatetotest),1,0
    )))
    I'll knock you up some SQL if needs be when I'm back in the office on Wednesday.

  13. #13
    Join Date
    Jan 2007
    Posts
    5
    Thanks George,

    I created a text box in the report design view with the following expression:

    =if([date1]=(39098),1,if([date1]=(39100),1,if([date1]=(39102),1,0)))

    But, when I run the report, a message box appears asking me to enter the parameter value for "if." I must have typed something wrong.

    Thanks again for your help. I really appreciate it, especially since I'm working on a database in Iraq.

    Glen

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Iraq? whoa!!

    the function is IIF, not IF

    =IIF([date1] in (39098,39100,39102),1,0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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