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

 

[Pat Hartman]

This is what the query would look like if your table were properly normalized.

Select Count(*) As TimeCount
From YourTable
Where TimeField = #4:00 PM#;

Without a properly normalized table, you'll need to write a VBA procedure that loops through the columns and evaluates each of them or a more complex query.

Select Sum(IIf(time1 = #4:00 PM#, 1, 0)) + Sum(IIf(time2 = #4:00 PM#,1,0)) + Sum(IIf(time3 = #4:00 PM#,1,0)) + .... As timeCount
From YourTable;