Results 1 to 4 of 4
  1. #1
    zul336 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Dec 2011
    Posts
    6

    Count column in form

    Hi All,


    I have created a form which contains a sub form from column range 1 to 10.


    They will have different times or dates posted in them.


    I will be interested in knowing that how can I count all the columns which got lets sat time 04:00 listed in them


    below is a small example:


    Column1 Column2 Column3 Column4 Column5 Column6 Column7
    Field1 04:00 07:00 09:00 04:00 05:00 04:00 04:00
    Field2 05:00 06:00 04:00 06:00 08:00 04:00 07:00
    Field3 04:00 05:00 06:00 08:00 04:00 06:00 04:00




    The result I am seeking is




    Total 4:00 are 9 in a text box field or any format


    Your help will be very appreciated


    Regards




    Zulfi

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,639
    Calculate this in a textbox ControlSource with an expression like

    Iif(Column1="04:00",1,0) + Iif(Column2="04:00",1,0) + ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    zul336 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Dec 2011
    Posts
    6
    Quote Originally Posted by June7 View Post
    Calculate this in a textbox ControlSource with an expression like

    Iif(Column1="04:00",1,0) + Iif(Column2="04:00",1,0) + ...

    Hiya, Thanks for your help.

    Will you be able to tell me what will be the code if field type is Time.

    Regards

    Zulfi

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,639
    The example data you show looks like text. Access has a Date/Time field type which has date and time components even if the field is formatted to show only part of the date and/or time. So even if you input only the time, there is a default date part, something like 1/1/1899 (I forget the year). I am not sure about time part if you input only the date part. Then there is the AM/PM part which you don't show in the example. All of this must be considered in expressions comparing date/time data.

    Examples:

    Format(#1/1/1899 4:17:00 AM#, "hh:nn") evaluates to "04:17"
    Format(#1/1/1899 4:17:00 PM#, "hh:nn") evaluates to "16:17"

    Format(#1/1/1899 4:17:00 AM#, "h:nn") evaluates to "4:17"
    Format(#1/1/1899 4:17:00 PM#, "h:nn") evaluates to "16:17"

    Format(#1/1/1899#, "m/d/yyyy hh:nn") evaluates to "1/1/1899 00:00"

    Review http://support.microsoft.com/kb/210276
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. 3 values in 1 Column - Get Count of Each Value
    By Ghoztrider in forum Queries
    Replies: 1
    Last Post: 01-09-2012, 03:26 PM
  2. Replies: 1
    Last Post: 12-08-2011, 08:03 AM
  3. Replies: 1
    Last Post: 04-15-2010, 02:07 AM
  4. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  5. Form Record Count
    By Texaine in forum Forms
    Replies: 0
    Last Post: 10-19-2006, 09:07 AM

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