Unbound text boxes not calculating properly in equation for Total field

[gopherking]

I am a bit stumped on this one since I have the same scenario working in other forms, but they aren't working on this one. Ok, the scenario:

I have a form with 12 unbound text boxes for each month with a Total text box at the end to calculate what a user updates within each of the Month fields. Each month text field is named to the corresponding month it represents (Ex. JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC), which makes equations and recordset updates easier to manage without trying to remember which Text Box # was for September. In the Total text box, I have the following equation in the Control Source property:

=Nz([JAN],0)+Nz([FEB],0)+Nz([MAR],0)+Nz([APR],0)+Nz([MAY],0)+Nz([JUN],0)+Nz([JUL],0)+Nz([AUG],0)+Nz([SEP],0)+Nz([OCT],0)+Nz([NOV],0)+Nz([DEC],0)

Basically, the above equation looks at each field and if it is null, then it assumes that the value should be zero (0) in order for the calculation to work.

Now, as I mentioned above, I have this same setup on other forms and they are all working with no problems. I have validated there are no differences between the text boxes on this new form and others, but for some reason, this one is giving me issues.

So, now what I am seeing occur in the Total text box as I enter in numbers for each month:

Scenario 1: If I add a "1" to each month text box, the expected total should be "12", BUT as I begin entering numbers starting with JAN, the Total text box does the following:

JAN - 1 Total = 1
FEB - 1 Total = 11
MAR - 1 Total = 111
APR - 1 Total = 1111
...
DEC - 1 Total = 111111111111

Scenario 2: I decided to start with FEB and leave JAN blank until last, below are the results:

FEB - 1 Total = 1
MAR - 1 Total = 2
APR - 1 Total = 3
...
DEC - 1 Total = 11


JAN - 1 Total = 111111111111

After the second scenario, I tested to see if this issue only occurred as long as all month fields contained a number, but in the end it seems as though this issue is solely caused by the JAN and FEB text boxes when they both contain numbers. So, I can have all months with numbers except for FEB or JAN and the Total Text box will work as expected. As soon as I add a number into both of these months, the Total text box transforms to the "#" symbols.

I have already tried:

• Removing and creating new text boxes for JAN and FEB - Still same issue
• Renaming the JAN and FEB text fields to Text1 and Text2 - Still same issue

From the above scenario's it looks like as long as only JAN or FEB is updated with numbers, then the calculation considers all entries as Integers, but as soon as both JAN and FEB are included the calculation considers each text box as a String.

Can anyone help shed some light on why this may be happening or a possible solution? Any assistanced is greatly appreciated as I am quickly losing my hair!! [Insert Hair Pulling emoticon here]

[RuralGuy]

Are all 0f the Month fields Numeric? It looks like some may be text.

[JoeM]

That behavior seems to indicate that it is being treated as Text, not Numeric. I am guessing that you have not formatted your text boxes as a number (if you leave the Format property of each Text Box blank, it is treated as Text and not Numeric).


Edit: Oops! I am a very slow typist and was testing out my theory as I was typing up this response, so I did not see Allan's reply.

[gopherking]

... I hate it when it is something so simple!!! OK, so all I had to do was set the format on the JAN and FEB fields to General Number and all is well (Thanks Joe and Allan)!

BUT,

This doesn't explain why in the World of Access would these fields on other forms not need to have their Format set and are able to calculate with no problems what-so-ever!?!

So, I am going to take this issue and store it with the conundrum of not knowing how many licks it will take to get tot he center of a Tootsie Roll Pop!!

Thanks for saving my hair guys!

[RuralGuy]

Glad we could help.