446,282 Members | 1,736 Online
Need help? Post your question and get tips & solutions from a community of 446,282 IT Pros & Developers. It's quick & easy.

# Arithmatic & lookup columns - weird stuff

 P: n/a The description field looks up values from another table: Description, ProductCost, DeliverCost, OtherCost & SalePrice. AfterUpdate of the description field is: Me!Cost = Me!Description.Column(1)+Me!Description.Column(2)+ Me!Description.Column (3) Me!Price = Me!Description.Column(2) This is supposed to add ProductCost, DeliverCost & OtherCost and input it into a Cost field. There is a value in all three Cost columns (Product, Delivery & Other) whether it be a figure or 0, there are no nulls. On some items it works perfectly, on others it seems to multiply the number by 100 (i.e. Product cost = \$24.00, Deliver = 0, Other = 0, Cost becomes \$2400.00), and on others I get an error (Runtime 2113 - The value you entered isn't valid for this field) Upon testing some of the data, it appears that: If ProductCost (Description.Column(1)) is a whole dollar then the amount is multiplied by 100 but if the amount has cents it is not. If ProductCost is not a whole dollar amount and there is a non-zero amount in column2 (DeliveryCost) I get the error message Any thoughts Dec 9 '06 #1
10 Replies

 P: n/a Are all of your fields that populate the lookup columns currency? Eg are all the Data Type's the same? And have you set the data type of the field you are calculating? Dec 10 '06 #2

 P: n/a Yes, all of the fields in the lookup table and the two fields in the current table are currency. "keri"

 P: n/a On Sat, 09 Dec 2006 23:48:24 GMT, "Jimmy" The description field looks up values from another table: Description,ProductCost, DeliverCost, OtherCost & SalePrice.AfterUpdate of the description field is: Me!Cost =Me!Description.Column(1)+Me!Description.Column(2) +Me!Description.Column (3) Me!Price = Me!Description.Column(2)This is supposed to add ProductCost, DeliverCost & OtherCost and input itinto a Cost field. There is a value in all three Cost columns (Product,Delivery & Other) whether it be a figure or 0, there are no nulls. On someitems it works perfectly, on others it seems to multiply the number by 100(i.e. Product cost = \$24.00, Deliver = 0, Other = 0, Cost becomes \$2400.00),and on others I get an error (Runtime 2113 - The value you entered isn'tvalid for this field)Upon testing some of the data, it appears that:If ProductCost (Description.Column(1)) is a whole dollar then the amount ismultiplied by 100 but if the amount has cents it is not.If ProductCost is not a whole dollar amount and there is a non-zero amountin column2 (DeliveryCost) I get the error messageAny thoughts Dec 10 '06 #4

 P: n/a Tried changing ! to . with no help. Here is the row source for Description: SELECT DISTINCTROW tblExpenseItems.Description, tblExpenseItems.ProductCost, tblExpenseItems.DeliveryCost, tblExpenseItems.OtherCost, tblExpenseItems.SalePrice FROM tblExpenseItems; Bound column is 1, limit to list is No, column count 5 And AfterUpdate code: Me!Cost = Me.Description.Column(1) + Me.Description.Column(2) + Me.Description.Column(3) Do columns start numbering as 0 like tab stops? "Tom van Stiphout"

 P: n/a It appears that what may be happening is that access is concatenating the three columns rather than performing a mathematical calculation. I.e, rather than column1 Added to column2 and so on it is simply meshing the two together, thus \$5.00 AND \$1.00 AND \$1.00 becomes \$511.00 rather than \$7.00. any thoughts on how to fix this?? FWIW, column(0) is Description, a text field. "Jimmy" On Sat, 09 Dec 2006 23:48:24 GMT, "Jimmy"

 P: n/a Problem solved...columns are always treated as strings rather than numbers, thus cannot be added (calculated). Using the Val() function around each field solved the problem. "Jimmy" Tried changing ! to . with no help. Here is the row source forDescription:SELECT DISTINCTROW tblExpenseItems.Description,tblExpenseItems.ProductCost, tblExpenseItems.DeliveryCost,tblExpenseItems.OtherCost, tblExpenseItems.SalePrice FROMtblExpenseItems;Bound column is 1, limit to list is No, column count 5And AfterUpdate code:Me!Cost = Me.Description.Column(1) + Me.Description.Column(2) +Me.Description.Column(3)Do columns start numbering as 0 like tab stops?"Tom van Stiphout" >On Sat, 09 Dec 2006 23:48:24 GMT, "Jimmy"

 P: n/a On Tue, 12 Dec 2006 01:20:22 GMT, "Jimmy" Problem solved...columns are always treated as strings rather than numbers,thus cannot be added (calculated). Using the Val() function around eachfield solved the problem. "Jimmy" It appears that what may be happening is that access is concatenating thethree columns rather than performing a mathematical calculation. I.e,rather than column1 Added to column2 and so on it is simply meshing thetwo together, thus \$5.00 AND \$1.00 AND \$1.00 becomes \$511.00 rather than\$7.00. any thoughts on how to fix this?? FWIW, column(0) is Description, atext field."Jimmy" >Tried changing ! to . with no help. Here is the row source forDescription:SELECT DISTINCTROW tblExpenseItems.Description,tblExpenseItems.ProductCost, tblExpenseItems.DeliveryCost,tblExpenseItems.OtherCost, tblExpenseItems.SalePrice FROMtblExpenseItems;Bound column is 1, limit to list is No, column count 5And AfterUpdate code:Me!Cost = Me.Description.Column(1) + Me.Description.Column(2) +Me.Description.Column(3)Do columns start numbering as 0 like tab stops?"Tom van Stiphout"

 P: n/a Tom van Stiphout + is the addition operator. & is the concatenation operator. -Tom. + is the addition operator.for numeric data + is the concatenation operator.for string data (nulls propagate) & is the concatenation operator.for string and numeric data and null values do not propagate.. -- Bob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Dec 12 '06 #9

 P: n/a On 12 Dec 2006 21:34:32 GMT, Bob Quintal wrote: The picture can be (made) even more complicated if you consider implicit conversion and precedence. Just trying to cover the basics. I didn't know that & does not propagate nulls. Just playing around: ?"1" & vbNullString & "2" 12 ?"1" & vbNull & "2" -Tom. >Tom van Stiphout >+ is the addition operator.& is the concatenation operator.-Tom. + is the addition operator.for numeric data+ is the concatenation operator.for string data (nulls propagate)& is the concatenation operator.for string and numeric data andnull values do not propagate.. --Bob QuintalPA is y I've altered my email address. Dec 13 '06 #10

 P: n/a Tom van Stiphout -Tom. I owe you a buck. :-) but neither vbnull nor vbnullstring (or chr(0)) is a null. try dim x as variant, y as variant, z as variant x = "1" y = null z = "2" debug.print x + y + z,x & y & z, x + y & z , x & y + z > >>Tom van Stiphout >>+ is the addition operator.& is the concatenation operator.-Tom. + is the addition operator.for numeric data+ is the concatenation operator.for string data (nullspropagate) & is the concatenation operator.for string andnumeric data and null values do not propagate.. --Bob QuintalPA is y I've altered my email address. -- Bob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Dec 14 '06 #11

### This discussion thread is closed

Replies have been disabled for this discussion.