Summing Unbound control

[benkingery]

I have a form where I am entering values into several fields. These fields will contain numbers that will need to be inserted into various inventory account tables to show inventory transfers from one allocated location to another.

For example, I have 10 pcs allocated to 4 different channels and I want to move that inventory around as needed. On the form I am creating, I have 4 unbound controls each signifying the amount to transfer. If an amount is to be transferred from one inventory account to another, the net change numbers are placed into the unbound fields. For example, if I want to take channel 1 down by 5 pcs, I enter "-5". If I want to them add those 5 pcs back into channel 4, then I enter "5" on channel 4.

Here is the reason for my question. Since these are unbound forms, I'm getting some wierd stuff when I'm trying to sum up the changes. I am programming in a piece that will not let the user continue to post the changes to the various inventory control accounts unless the SUM of the changes is ZERO. I don't want to create or destroy invnetory, so I'm trying to control it by equal and opposite reactions.

My controls are called "Ctrl1", "Ctrl2", "Ctrl3", "Ctrl4". My sum control is called CtrlSUM, and its records source is "=Nz([Ctrl1],0)+Nz([Ctrl2],0)+Nz([Ctrl3],0)+Nz([Ctrl4],0)). Now, if I enter "1" into Ctrl1, the CtrlSUM displays "1". If I enter "1" into Ctrl1 AND Ctrl2, I get "11". If I enter "1" into Ctrl1 and Ctrl2, and "2" into Ctrl3, then I get "112" in CtrlSUM. Now you can see why I'm scratching my head. Then if I make Ctrl1 null and leave all the other values, it sums up as it should......

I'm very confused. Any help out there on this strange problem?

Thanks,

 

[Trevor G]

You can do this with Cdbl

=cdbl([text1])+cdbl([text2])


And
another thing had to use the nz function to weed out the nulls!
=CInt(nz([PBSN1],0))+CInt(nz([PBSN2],0))+CInt(nz([PBSN3],0))

 

[Galaxiom]

Check the Format property of the textboxes. If they are set to Number they might add instead of concatenating.

BTW the second argument of the Nz function is optional and defaults to zero.
Nz(somevalue,0) is the same as Nz(somevalue)

 

[benkingery]

Cool. Thanks all. I actually just put a "0 +" at the beginning of my formula and it all worked out. Go figure....

 

[Galaxiom]

There are two types of concatenation in Access using the ampersand and (ambiguously, as you discovered) using the addition sign. I think the addition sign treats nulls differently.

The zero at the beginning indicates that you are working with numerical values and gets it off on the right foot.