Arithmatic & lookup columns - weird stuff

[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 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

[keri]

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?

[Jimmy]

Yes, all of the fields in the lookup table and the two fields in the current
table are currency.
"keri" <ke*********@diageo.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...

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?

[Tom van Stiphout]

On Sat, 09 Dec 2006 23:48:24 GMT, "Jimmy" <no**@none.comwrote:

Try this:
Me.Cost =
Me.Description.Column(1)+Me.Description.Column(2)+ Me.Description.Column
(3)
Me.Price = Me.Description.Column(2)

I'm assuming Description is a 4-column dropdown?

-Tom.

>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

[Jimmy]

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" <no*************@cox.netwrote in message
news:6n********************************@4ax.com...

On Sat, 09 Dec 2006 23:48:24 GMT, "Jimmy" <no**@none.comwrote:

Try this:
Me.Cost =
Me.Description.Column(1)+Me.Description.Column(2)+ Me.Description.Column
(3)
Me.Price = Me.Description.Column(2)

I'm assuming Description is a 4-column dropdown?

-Tom.

[Jimmy]

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" <no**@none.comwrote in message
news:Os*******************@fe01.news.easynews.com. ..

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" <no*************@cox.netwrote in message
news:6n********************************@4ax.com...

>On Sat, 09 Dec 2006 23:48:24 GMT, "Jimmy" <no**@none.comwrote:

Try this:
Me.Cost =
Me.Description.Column(1)+Me.Description.Column(2) +Me.Description.Column
(3)
Me.Price = Me.Description.Column(2)

I'm assuming Description is a 4-column dropdown?

-Tom.

[Jimmy]

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" <no**@none.comwrote in message
news:s8*********************@fe04.news.easynews.co m...

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" <no**@none.comwrote in message
news:Os*******************@fe01.news.easynews.com. ..

>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" <no*************@cox.netwrote in message
news:6n********************************@4ax.com.. .

>>On Sat, 09 Dec 2006 23:48:24 GMT, "Jimmy" <no**@none.comwrote:

Try this:
Me.Cost =
Me.Description.Column(1)+Me.Description.Column(2 )+Me.Description.Column
(3)
Me.Price = Me.Description.Column(2)

I'm assuming Description is a 4-column dropdown?

-Tom.

[Tom van Stiphout]

On Tue, 12 Dec 2006 01:20:22 GMT, "Jimmy" <no**@none.comwrote:

+ is the addition operator.
& is the concatenation operator.

-Tom.

>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" <no**@none.comwrote in message
news:s8*********************@fe04.news.easynews.c om...

>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" <no**@none.comwrote in message
news:Os*******************@fe01.news.easynews.com ...

>>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" <no*************@cox.netwrote in message
news:6n********************************@4ax.com. ..
On Sat, 09 Dec 2006 23:48:24 GMT, "Jimmy" <no**@none.comwrote:

Try this:
Me.Cost =
Me.Description.Column(1)+Me.Description.Column( 2)+Me.Description.Column
(3)
Me.Price = Me.Description.Column(2)

I'm assuming Description is a 4-column dropdown?

-Tom.

[Bob Quintal]

Tom van Stiphout <no*************@cox.netwrote in
news:su********************************@4ax.com:

>
+ 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

[Tom van Stiphout]

On 12 Dec 2006 21:34:32 GMT, Bob Quintal <rq******@sPAmpatico.ca>
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"
<first place a bet, then try for yourself, then send me your money
:-)>

-Tom.

>Tom van Stiphout <no*************@cox.netwrote in
news:su********************************@4ax.com :

>>
+ 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.

[Bob Quintal]

Tom van Stiphout <no*************@cox.netwrote in
news:tk********************************@4ax.com:

On 12 Dec 2006 21:34:32 GMT, Bob Quintal
<rq******@sPAmpatico.cawrote:

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"
<first place a bet, then try for yourself, then send me your
money
:-)>

-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 <no*************@cox.netwrote in
news:su********************************@4ax.co m:

>>>
+ 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.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com