Hiya, I have a fairly basic database that I use for customer information more than anything else. It also calculates VAT and total invoice value for me (I have done this in forms rather than queries). However, I tried to add a basic calculation based on figures that I have already entered as I go along (purchased price + VAT + Carriage) and for some reason the sum will not add up. It will subtract, times and divide but won't add. In fact when I type the expression =[CostOfPurchasedUnitEx]+[CostOfPurchasedUnitVAT]+[CostOfPurchasedCarriage] and lets say the sum is actually 110+22+8 it enters the value 110228 and then only on the first record. However, if I change this to 110 -22-8 it works! I have scoured the forums for a similar problem but cant find anything even close. By the way it doesn't work in queries either. I know that it is something I have done because it did work just fine.....any advice / reprimands gratefully accepted.
My add expression doesn't add!
- Thread starter GeorgiF
- Start date
Brianwarnock
Retired
- Local time
- Today, 23:52
- Joined
- Jun 2, 2003
- Messages
- 12,685
It is using the + as a concatanation function, is does this if any of the fields are defined as text.
Brian
Brian
- Local time
- Today, 23:52
- Joined
- Jul 9, 2003
- Messages
- 17,587
The problem that you are experiencing is that the plus character “+” serves two purposes, it is used for adding number values together 1 + 1 = 2 and it is also used for joining strings together “String1” + “String2” = “String1String2” this is called concatenating or concentrating strings.
It is seeing the information you provide as “text” and not number values.
The solution is to make sure that you convert the text into a numeretic value before you do the addition. You can do this with a conversion function like CDbl or CInt. You will need to choose the one most suited to the result you want.
expression =CDbl([CostOfPurchasedUnitEx])+ CDbl ([CostOfPurchasedUnitVAT])+ CDbl ([CostOfPurchasedCarriage])
It is seeing the information you provide as “text” and not number values.
The solution is to make sure that you convert the text into a numeretic value before you do the addition. You can do this with a conversion function like CDbl or CInt. You will need to choose the one most suited to the result you want.
expression =CDbl([CostOfPurchasedUnitEx])+ CDbl ([CostOfPurchasedUnitVAT])+ CDbl ([CostOfPurchasedCarriage])
Brianwarnock
Retired
- Local time
- Today, 23:52
- Joined
- Jun 2, 2003
- Messages
- 12,685
Well I guess my post was just invisible.
Brian
Brian
I know you've got this solved already but are these three fields, i.e. [CostOfPurchasedUnitEx], [CostOfPurchasedUnitVAT] and [CostOfPurchasedCarriage], defined as Number or Text fields or are they names of unbound textboxes?
If they are unbound textboxes, setting the Format property to General Number may help instead of having to explicitly cast to double.
By the way, if you CDBl() you will also need to handle Nulls with Nz():
If they are unbound textboxes, setting the Format property to General Number may help instead of having to explicitly cast to double.
By the way, if you CDBl() you will also need to handle Nulls with Nz():
Code:
CDbl(Nz([CostOfPurchasedUnitEx], 0)) + CDbl(Nz([CostOfPurchasedUnitVAT], 0)) + CDbl(Nz([CostOfPurchasedCarriage], 0))
Last edited:
Brianwarnock
Retired
- Local time
- Today, 23:52
- Joined
- Jun 2, 2003
- Messages
- 12,685
Sorry VBA I deleted my last post as I didn't want to cause any aggro with Gizmo especially as I'm not likely to be around much for a while, just maybe pop on now and again, after Wednesday when the wife's radiotherapy treatment starts.
Brian
Brian
Hiya, They were (are) defined fields and I did get an error message. I changed the format to currency and that resolved it (I think) for new records but previous ones where there is a null value have the message still. I will try again tomorrow as I have had enough of databases for tonight! Thanks again everyone for being so patient...
