Quick Formula Issue!

[RORS002]

I've created a database with forms, tables, etc....and I have one little issue I'm hoping someone can help me with...

I have four fields that I want the fifth field to sum together...

Field A = 1
Field B = 2
Field C = 3
Field D = 4
Field E = =Sum("Field A"+"Field B"+"Field C"+"Field D")
or =+[Field A]+[Field B]+[Field C]+[Field D]

The problem is...the second example..(=+[Field A...) just smackes the numbers together..it doesn't add them.. i.e. 1234

And the other one (=Sum("Field A...) gives me nothing at all...any advice?!

Much appreciated!

Thanks,

Rachel

 

[pbaldy]

The second would be the correct syntax. If it's concatenating instead of adding, it thinks the values are text for some reason. You can address that, or force the issue with the appropriate conversion function (CLng, CDbl, etc):

=CLng([Text1]) + CLng([Text2]) ...

 

[RORS002]

You are absolutely amazing!!!!! It works!!!! Thank you, thank you, thank you!!!

 

[pbaldy]

LOL! No problem, Rachel.

 

[Vergy39]

The second would be the correct syntax. If it's concatenating instead of adding, it thinks the values are text for some reason. You can address that, or force the issue with the appropriate conversion function (CLng, CDbl, etc):

=CLng([Text1]) + CLng([Text2]) ...

This would certainly be helpful to me as well, but not sure what field property you are entering this formual. Please let me know if you have time. Is it Format, Validation Rule, Input Mask?

Thanks
David V.

 

[boblarson]

This would certainly be helpful to me as well, but not sure what field property you are entering this formual. Please let me know if you have time. Is it Format, Validation Rule, Input Mask?

Thanks
David V.

It would be in the text box's CONTROL SOURCE.

 

[RORS002]

I'm putting it into the form itself..you can see the attached....although, I find that I would like to see it in currency style..not sure how to do that yet either!

 

[Vergy39]

It would be in the text box's CONTROL SOURCE.

Thanks. I thought she was adding this to a field on a table, not a form. Do you know if there is a way to do this on a table? I have a table that I would like to sum the number of benefit days and FMLA days. I cna do it with a query, but woul dlike it to sum on the table.

Thanks
David V.

 

[boblarson]

I cna do it with a query, but woul dlike it to sum on the table.

And it should be done in a query, not stored in the table (per normalization rules AND referential integrity as there is no way to ensure that the data gets updated in the table should someone change a field directly in the table). You don't need it in the table, just calculate with a query when you need it. You can use a query in 99.9% of the places you use a table.

 

[Vergy39]

And it should be done in a query, not stored in the table (per normalization rules AND referential integrity as there is no way to ensure that the data gets updated in the table should someone change a field directly in the table). You don't need it in the table, just calculate with a query when you need it. You can use a query in 99.9% of the places you use a table.

Actually, I am having trouble getting the totals that I want in a query. Here is an example.

Date Received Completed FBCS Completed
12/1/09 2 1 0
12/2/09 3 1 1
12/3/09 1 2 0
12/4/09 2 0 2

Totals 8 4 3

This is what I am looking for, but the query lists totals by the date, not the weelk. Any suggestions?

David V

 

[boblarson]

You would need to create a field for week and get rid of the date field and then use the aggregate button (

)

So create a field in the query for week:

DateWeekatePart("ww",[FieldNameHere])

And then that should total by that as long as that has the GROUP BY set for that field and you do NOT include the date field.

 

[Vergy39]

You would need to create a field for week and get rid of the date field and then use the aggregate button (

)

So create a field in the query for week:

DateWeekatePart("ww",[FieldNameHere])

And then that should total by that as long as that has the GROUP BY set for that field and you do NOT include the date field.

Thanks for the suggestion, but still not working. I may misunderstand what the smiley face is. Is it DateWeekDatePart("ww",[Sum of TPreceived])?

 

[boblarson]

Sorry I should have noticed the smiley due to the code I used:


DateWeekatePart("ww",[FieldNameHere])