Count column in form

[zul336]

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

[June7]

Calculate this in a textbox ControlSource with an expression like

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

[zul336]

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

[June7]

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