total many fields by function

[thetuyen]

I'm Vietnamese and I'm not good in english. Please to sympathize with me.
I create a table with 45 fields in it: ID, month, year, t1,t2,t3...t40. The fields t1, t2.. t40 will have value(format is text): 0.5P or 1 or 0.5 or Null . I want to create total of that with convention : 0.5p = 0.5; 1=1, 0.5 = 0.5, null =0, orther value= 0. Then I have SQL like:

 SELECT
IIf([t1]="1",1,IIf([t1]="0.5" or [t1]="0.5P", 0.5,0)) + 
IIf([t2]="1",1,IIf([t2]="0.5" or [t2]="0.5P", 0.5,0)) + 
.....
IIf([t40]="1",1,IIf([t40]="0.5" or [t40]="0.5P", 0.5,0)) +
AS Totalfiels
....

It's very long and I thinh that it's stupid. And I try to write a function for it by VBA, But I can't. hu hu.... I'm studying access and I'm not intelligent. Please to help me create that function. Thanh you very very much.

[NTC]

do not have fields t1-t40. make a second table with 3 columns (Ttable) first column is autonumber primary key. 2nd column is cross reference to first column foreign key. 3rd column is T value.

make a data entry form - and then insert new Ttable as subform.

you have a decision; you can convert the value (i.e. 0.5p to .5) in the form at first data entry. Or you can store the original value 0.5p and convert it during queries/reporting. Now there is only 1 T field (not 40) so it will be easier to write solution.

[thetuyen]

do not have fields t1-t40. make a second table with 3 columns (Ttable) first column is autonumber primary key. 2nd column is cross reference to first column foreign key. 3rd column is T value.

make a data entry form - and then insert new Ttable as subform.

you have a decision; you can convert the value (i.e. 0.5p to .5) in the form at first data entry. Or you can store the original value 0.5p and convert it during queries/reporting. Now there is only 1 T field (not 40) so it will be easier to write solution.

Thank you very much.