Sum of text field- change to numeric??

T

The Mecca

I have a query of Month to Date information for weeks 1-5. Unfortunantly they
are text fields- so I am unable to sum them together. Can anyone help me?
Maybe I need to find a way to change the data type to numeric?
 
T

The Mecca

i have the values as follows
Week1.total
week2.total
week3.total
week4.total
week5.total

MTD_total:SUM(val(week1.total)+val(week2.total)+val(week3.total)+val(week4.total)+val(week5.total))

But now it is telling me that there is a data mismatch. So I try without the
val() and it doesnt give me an error- but shows nothing in the calculated
fields.

So now I'm wondering- because if I have nothing populating totals for weeks
3, 4 and 5... will it not calculate????

KARL DEWEY said:
Val([YourTextField])

The Mecca said:
I have a query of Month to Date information for weeks 1-5. Unfortunantly they
are text fields- so I am unable to sum them together. Can anyone help me?
Maybe I need to find a way to change the data type to numeric?
Click to expand...
Click to expand...
 
L

Lynn Trapp

Try wrapping each of your values in a NZ function:

Nz(val(week1.total))

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



The Mecca said:
i have the values as follows
Week1.total
week2.total
week3.total
week4.total
week5.total

MTD_total:SUM(val(week1.total)+val(week2.total)+val(week3.total)+val(week4.total)+val(week5.total))

But now it is telling me that there is a data mismatch. So I try without
the
val() and it doesnt give me an error- but shows nothing in the calculated
fields.

So now I'm wondering- because if I have nothing populating totals for
weeks
3, 4 and 5... will it not calculate????

KARL DEWEY said:
Val([YourTextField])

The Mecca said:
I have a query of Month to Date information for weeks 1-5.
Unfortunantly they
are text fields- so I am unable to sum them together. Can anyone help
me?
Maybe I need to find a way to change the data type to numeric?
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Ido not understand your DB structure. Do you have five separate tables, one
for each week?

The Mecca said:
i have the values as follows
Week1.total
week2.total
week3.total
week4.total
week5.total

MTD_total:SUM(val(week1.total)+val(week2.total)+val(week3.total)+val(week4.total)+val(week5.total))

But now it is telling me that there is a data mismatch. So I try without the
val() and it doesnt give me an error- but shows nothing in the calculated
fields.

So now I'm wondering- because if I have nothing populating totals for weeks
3, 4 and 5... will it not calculate????

KARL DEWEY said:
Val([YourTextField])

The Mecca said:
I have a query of Month to Date information for weeks 1-5. Unfortunantly they
are text fields- so I am unable to sum them together. Can anyone help me?
Maybe I need to find a way to change the data type to numeric?
Click to expand...
Click to expand...
Click to expand...
 
Top