Counting specific fields within a table/query

[Spyhunter]

I have 32 fields and each has the ability for 3 different types of input (S,H,EL)
I want to count each field based on the table for the type, so if field1=S and field 2=S then TotalS would be 2 just for that table.

I need to do this to all 31 fields with all 3 selections for each.

I can do this with IF logic using a counter for adding but I really want to do this the proper way

 

[vbaInet]

If you want to count for each table then you should create a Totals query for each table.

Having 32 fields with the same number of selections sounds like a structure that is not normalized.

 

[Spyhunter]

i know i know but i have to work with what i have thus far.
i have it working using VBA but can't seem to get it to add up 0.5, it always rounds it '1'. Whats the VBA for doing this at its normal level with no rounding please (outputs to unbound field on form)

 

[vbaInet]

Use CDbl() to cast it to a double type. Doubles have decimals.

 

[Spyhunter]

i have tried that it appears that the unbound field on the form is the problem, maybe?

 

[boblarson]

i have it working using VBA but can't seem to get it to add up 0.5, it always rounds it '1'.

What is the code you are using? You aren't declaring something as Integer or Long Integer are you? Those don't have decimals and assigning something to that will have the behaviour you describe.

 

[Spyhunter]

i have it working with your coding above now.

thank you very much.

just out of interest whats the line of code to search by x 'months' from today - so if I want to search a date field by all dates for the past 6 months only.

thanks.

 

[vbaInet]

DateDiff()