Currency column - doesn't truly round to two digits.

[Clay]

I have an inventory table that I regularly import data to from a text file.
The data comes to me as a tab delimited column that may contain 10, for ten
dollars, or 9.99, or even 9.987. The column in my table is defined as
currency, with two decimal places. On viewing the table, all appears
correct. Ditto with the various queries associated with it, forms, etc.

The problem arrises when my FrontPage DRW generated page shows the data as
10, 9.99, and 9.987. I can't seem to format the column in FrontPage without
stripping the generated webbot code (which I don't really want to do anyway -
I like being able to use the DRW, as I'm not a real ASP wiz).

I tried creating a query with a CCur function on the offending column and
using that for the input, but my web server didn't like that. I can't edit
the column within the page, because FrontPage insists on losing the changes
because it is code generated by the Data Results Wizard.

I'm kind of stumped.

 

[Barry Gilbert]

When you apply a format to a table field, it doesn't change the value, just
the way it's displayed when you view the table. I think you're on the right
track with a query, but you should look at the Round function
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctround.asp)

Barry

 

[Clay]

Well, that didn't work so good.

SELECT INVENTORY.Stock, INVENTORY.Desc_1, INVENTORY.Desc_2,
Round(INVENTORY.Discount,2) AS Price

Gave me 10, 9.99, 9.89, etc. But that 10 out there looks a little weird.
And that's just in Access. Not on the web yet with it. Gonna try something
else...

Am using my table with correct column formats to export to a text file. It
outputs the genuine data. Then I'm re-populating the original table from the
text file. So far, it seems to be working. More testing to follow. What a
pain in the neck.

Queries that have functions in them are not retrieved by the server
correctly (one row, with an error message in the column with the function).

Ditto any queries that use 'like' in them (they come back with zero rows).

 

[Clay]

Solved. Not very eloquently, but it works.

Adjusted the inventory columns of dubious input to be of currency format.
Access makes 'em look purty, even though the data behind them isn't.

Wrote a query to create the columns I'll need from the inventory table for
the website. Copy it all in the clipboard while looking at it, and paste to
a text file. Used the text file as import to a new table, that has the
currency column listed as text data.

Published to the web, and it looks okay. Will be writing various queries
against this new table for the various breakdown of product.

More than one way to skin a cat.