Decimal places in a temporary table

[Gordon]

I am using a temporary table to perform calculations before appending
the resultant invoice transaction records to my main table. The
temporary table is created in code like this:
strSQL = "CREATE TABLE tblInvoiceTransactionsTemp
(fldInvoiceTransactionID Double,fldProductID Double, fldIFAID
Double,fldQuantity Integer, fldFreeOfCharge YesNo,"
strSQL = strSQL & "fldEnqID Double, fldInvoiceHeaderID Double,
fldAgreedPrice Currency, fldVATRate Double)"
DoCmd.RunSQL strSQL
This all works fine but the problem is the VAT (tax) rate - the
current rate is 17.5% but when it reaches the temporary table it is
rounded to 18% which then inflates the VAT in the calculations.

The fldVAT rate is held in a lookup table as number format/field size
= single/Fixed format/1 decimal place.

How do I amend the SQL code above so that it produces 17.5 in the
temporary table and not 18 ?
Thanks for any help
Gordon

 

[Tom van Stiphout]

On Sat, 30 Jan 2010 10:14:36 -0800 (PST), Gordon <[email protected]>
wrote:

The code you showed only creates an EMPTY table. You'll have to
scrutinize the Append query that adds data to this table.

Also be sure to make a distinction between the data type (e.g. double)
and the way the value is displayed (e.g. fixed format/1). The latter
has nothing to do with the actual value. I can store 17.5 in a table
and display it as an integer which would show 18. That does not mean
18 is stored in the table.

-Tom.
Microsoft Access MVP

 

[Jeff Boyce]

Gordon

As an alternative, what about the idea of creating a table you'll use to
temporarily hold the data? That way, you could set the data type (e.g.,
"currency") on that field.

Then your code would need to:
1 - empty the old data out
2 - append the new data

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Gordon]

Gordon

As an alternative, what about the idea of creating a table you'll use to
temporarily hold the data?  That way, you could set the data type (e.g.,
"currency") on that field.

Then your code would need to:
  1 - empty the old data out
  2 - append the new data

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentionedin
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.








- Show quoted text -

Thanks for the quick replies, guys. Tom's response made me think my
problem lay in the later portion of my code where I populate the
temporary table with the VAT rate.
I hade dim'd the VAT rate as an integer - changing this to "double"and
the underlying field in the table to which the records from the temp
table were being appended solved my problem.

Thanks again.

Gordon