Results 1 to 5 of 5
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94

    Count If value in Short Date formatted field is <=4

    Hi!

    I have a field in my table named BusinessHours which is formatted as Date/Time - Short Date. I need to be able to count the number of records in my table where the BusinessHours field is <= 4. When I run this query I do get a result however the result is incorrect; it is counting too many records.

    This is what I have currently:
    Code:
    SELECT Count([tbl_HomeOwner Claims].Claim) AS CountOfClaim, Count(IIf([tbl_HomeOwner Claims]![BusinessHours]<=4,[Claim])) AS [Contact Less Than 4], [Contact Less Than 4]/[CountOfClaim] AS [Percent Less Than 5]
    FROM [tbl_HomeOwner Claims];
    
    I have tried quotation marks but I get an Data type mismatch error. In another query I used <= 4 AM to show me the claims that are less than 4 hours and that worked but when I try to use something like that in this aggregate query I get an error.

    Thank you for any help you can provide!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    queries dont need IIF. thats what the criteria is for. Put the <4 in the criteria.

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Hey ranman256! Thank you for responding! I tried that but when I do that I get 0 resutls

    Code:
    SELECT Count([tbl_HomeOwner Claims].BusinessHours) AS CountOfBusinessHours
    FROM [tbl_HomeOwner Claims]
    HAVING (((Count([tbl_HomeOwner Claims].BusinessHours))<=4));
    

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    @JBeets -

    BusinessHours which is formatted as Date/Time - Short Date
    do you know what a date time field is? It is a double where the value before the dp is the number of days since 31/12/1899 (today is 43915). The bit after the dp is the time expressed as number of seconds divided by 86400, the number of seconds in a day.

    so this

    I need to be able to count the number of records in my table where the BusinessHours field is <= 4
    implies that your data is the time only part. 4 hours is 1/8th of a day or 0.1666666666667

    so I suggest your query should be

    Code:
    SELECT Count([tbl_HomeOwner Claims].BusinessHours) AS CountOfBusinessHours
    FROM [tbl_HomeOwner Claims]
    WHERE BusinessHours<0.16666666667
    or you could try

    Code:
    SELECT Count([tbl_HomeOwner Claims].BusinessHours) AS CountOfBusinessHours
    FROM [tbl_HomeOwner Claims]
    WHERE BusinessHours<#4:00 AM#



  5. #5
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    it is worth remembering that although a date/time field can be made to look like a date or time it is stored by ACCESS as a decimal number, an example might be 43537.29166667 where the integer part (43537) is the date, and the decimal part (.29166667) is the time.

    therefore, when your query asked for records where date < 4 you were actually asking for records dated before 4 January 1900, which is why it returned zero records.

    what you really want is all those records where the decimal part < 0.16666667

    to do this, you need to use the Hour() function, so your query should be asking for

    Hour(tbl_HomeOwner Claims.[BusinessHours]) and, as Ranman said, put the <4 in the criteria.

    this will give you something like this:


    SELECT Count(Hour(tbl_HomeOwner Claims.[BusinessHours])) AS TotalHours
    FROM tbl_HomeOwner Claims
    HAVING (Count(Hour(tbl_HomeOwner Claims.[BusinessHours]))<4);


    many thanks,


    Cottonshirt

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Changing short text field to date/time field
    By fishhead in forum Access
    Replies: 5
    Last Post: 03-25-2020, 01:17 AM
  2. Replies: 5
    Last Post: 08-10-2018, 02:20 PM
  3. iif statement - return a formatted date field
    By tennis2600 in forum Queries
    Replies: 2
    Last Post: 05-30-2015, 09:11 AM
  4. How to add Short Date to Field
    By nono5551212 in forum Access
    Replies: 2
    Last Post: 06-06-2014, 10:55 PM
  5. Date Entry in formatted field
    By gg80 in forum Forms
    Replies: 2
    Last Post: 02-10-2012, 06:35 PM

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
  •  
Other Forums: Microsoft Office Forums