Using recordset to copy decimal type from one table to another

[dile]

I am trying to use a recordset to copy the value in a Decimal type
field from a "temporary" table to a Decimal type field in a "permanent"
table, using VBA. I have to import records from Excel which might
include duplicates of records already in the table, and need to check
that the record is not a duplicate before I insert it into the
permanent table.

I need later calculations to be precise (it is a financial
application), and need to not have rounding errors, so am using the
Decimal type in the table fields.

When I run my "insert into" SQL statement, I get the error message
"Run-time error '13': Type Mismatch".

My abbreviated code is:

Dim dblAmount As Variant
Dim curAmount As Currency

Dim sqlInsert, sqlInsertFlds As String

' get variables from recordset
dblAmount = rstTemp.Fields("Amount")
curAmount = CCur(dblAmount)


sqlInsertFlds = "Amount"

sqlInsert = "INSERT INTO ImportVouchers (" + sqlInsertFlds + ")
VALUES (" + dblAmount + ")"

' run the insert statement
DoCmd.RunSQL (sqlInsert)


I have tried to convert the variant variable to currency using
CCur(dblAmount), inserting the currency variable curAmount (both as
above and getting its value directly from the recordset field), and to
insert the value as a variant; none of these have worked.

Does anyone have any advice?

Thanks.

 

[TC]

Debug.print the value of sqlInsert (immediately before you do the
runSQL), and copy & paste the output back here.

TC (MVP Access)
http://tc2.atspace.com

 

[wuyouren]

??:???????

 

[dile]

Debug.print the value of sqlInsert (immediately before you do the
runSQL), and copy & paste the output back here.

TC (MVP Access)
http://tc2.atspace.com

It doesn't get there - that is the statement it errors out on.

I did a Debug.Print on the sqlInsertFlds variable with this result:

sqlInsertFlds = Amount

I also changed the sqlInsert variable to:

sqlInsert = "INSERT INTO ImportVouchers (Amount) VALUES (" +
CCur(dblAmount) + ")"

and got the same error message, still stopping at the statement that
assigns the value to sqlInsert.

 

[dile]

I am trying to use a recordset to copy the value in a Decimal type
field from a "temporary" table to a Decimal type field in a "permanent"
table, using VBA. I have to import records from Excel which might
include duplicates of records already in the table, and need to check
that the record is not a duplicate before I insert it into the
permanent table.

I need later calculations to be precise (it is a financial
application), and need to not have rounding errors, so am using the
Decimal type in the table fields.

When I run my "insert into" SQL statement, I get the error message
"Run-time error '13': Type Mismatch".

My abbreviated code is:

Dim dblAmount As Variant
Dim curAmount As Currency

Dim sqlInsert, sqlInsertFlds As String

' get variables from recordset
dblAmount = rstTemp.Fields("Amount")
curAmount = CCur(dblAmount)


sqlInsertFlds = "Amount"

sqlInsert = "INSERT INTO ImportVouchers (" + sqlInsertFlds + ")
VALUES (" + dblAmount + ")"

' run the insert statement
DoCmd.RunSQL (sqlInsert)


I have tried to convert the variant variable to currency using
CCur(dblAmount), inserting the currency variable curAmount (both as
above and getting its value directly from the recordset field), and to
insert the value as a variant; none of these have worked.

Does anyone have any advice?

Thanks.

For anyone else who has this problem:

The answer was to use the Currency type for the table fields. This is
not one of the "number" types, but is in the list with "Text", "Memo",
"Date/Time", etc.

Then, the insert query had to format the variable as:

Format(curAmount, "#,##0.00")

even though curAmount is already declared as a Currency type.

Format() was also necessary to get the dates inserted:
Format(dtmAcctDate, "mm/dd/yyyy")

The entire insert statement was:

sqlInsertFlds = "[Business Unit], [Class-Funding], Dept, Account,
[Sub-Account], [Acctg Date], PO, [More Info], [Warrant Number], Name,
Source, [Vendor ID], [Voucher ID], [Date], [Budget Status], [Post
Status], [Bud Ref], [Payment Status], [Amount]"

sqlInsert = "INSERT INTO ImportVouchers ( " + sqlInsertFlds + " )
VALUES ('" strBusUnit + "', '" + strClassFund + "', '" + strDept + "',
'" strAccount + "', '" + strSubAccount + "', #" + Format(dtmAcctDate,
"mm/dd/yyyy") + "#, '" + strPO + "', '" + strMoreInfo + "', '" +
strWarrantNumber + "', '" + strName + "', '" + strSource + "', '" +
strVendorID + "', '" + strVoucherID + "', #" + Format(dtmDate,
"mm/dd/yyyy") + "#, '" + strBudgetStatus + "', '" + strPostStatus + "',
'" + strBudRef + "', '" + strPaymentStatus + "', " + Format(curAmount,
"#,##0.00") + " )"

' run the insert statement
DoCmd.RunSQL (sqlInsert)