Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Unanswered: Count between 2 dates

    We have a folder checklist. All the data is in 8 columns as checkboxes. I can sum the columns and it gives me a total of true responses which is great. My dilema is I cannot get it to sum between 2 dates. Specifically Oct 04 through Dec 04.

    Any help is greatly appreciated.

    Michael
    Gotta to do some code

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What does that even mean?

    How are checkboxes dates?

    Are there Dates on the row?

    Do you want to sum data between rows?

    What's the table Look like?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi Michael
    sounds like you need to try the where clause
    if you alter your sql statement to add
    where [mydate] between cdate("01 Oct 2004") and cdate("31 Dec 2004")

    you probably don't need to use the cdate
    Access shoul;d recognise the date as set for your locale
    so probably

    where [mydate] between "10/1/2004" and "31/12/2004"

    should work
    HTH

  4. #4
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Sorry!

    My table includes job name, date of job, customer signature, safety meeting(checkbox), customer signature(checkbox), drawings(checkbox), job measured(checkbox), etc. etc.
    Lets see... here is roughly what my query looks like:

    safety meeting customer sig drawing job measured etc
    (Table name) (Table name) (Table name) (Table name) (Table name)
    (Sum) (Sum) (Sum) (Sum) (Sum)

    I would like to use the dates from the table to only get the data from October to December of '04

    When I enter where [mydate] between "10/1/2004" and "31/12/2004"

    I get blank cells as my results
    Gotta to do some code

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well A couple of things...You've got your date formats messed up

    Is the "Date" field not a datetime field?

    Also, you need to worry about the time component.

    And the TableName is a bit unsettling...

    This is what I'm thinking...so tell me what you're thinking...
    Attached Thumbnails Attached Thumbnails SampleQuery1.bmp  
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Thank you Brett that seemed to work. I did have the date set up right. That is why I was unclear why it would not work.

    Why does "Between 10/1/04 and 12/31/04" not work in the criteria field?

    Thank you so much,
    Michael
    Gotta to do some code

  7. #7
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Michael,

    Between should work fine. Put this in your criteria:

    Between #10/1/04# and #12/31/04#

    TD

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Actually it doesn't work...You can't SUM on those fields because they don't lend themselves to math operations...

    You need to determine what they are first...and I did the test for both between and > and <=...I believe > and <= will be more effecient..at least it is in SQL Server...the fact that you try to sum the columns misleads you in to thinking that there is something wrong with the dates

    EDIT: Make sure you change the properties of the new column to be General Number

    Here
    Attached Thumbnails Attached Thumbnails CorrectSampleQuery1.bmp  
    Last edited by Brett Kaiser; 01-04-05 at 12:24.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Thank you... That is so cool.

    My results come up as negative numbers. Is there a way to make them positive? I know in Excel you can make them absolute with the abs function ie. =abs(-56) would result in 56

    Thanks,
    Michael
    Gotta to do some code

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you see the IIF(Col1=On,1,0)?

    And I take it back, you can do sum on that column, but like you said it comes out as negative numbers...

    If you use IIF where the Column = On (or Yes or True) you can set the expression to 1.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Access treats No, False, and Off as 0 and Yes, True, and On as -1, therefore you should change your field names to :

    Safety Meeting:-[table name].[safety meeting]

    and do this with your other checkbox fields. Then you can use Sum and lose the minus sign.

    TD

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That is so cool...I did not know that...

    I'd design your table differently though..

    Instead of boolean, I'd use a nullable date column..

    There's added benefit in that you know when each event occured

    Anyway, good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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