Data Conversion Error - Need Fresh Eyes

[dowlingm815]

Hi,

In the following code, i keep getting a data conversion error at the fund field. In the code below, it takes data from a query and creates a table. It is a six digit alpha field within the query and within the created table. it bombs out on this line when writing the field:

   rstSummary!fund = strTempFund
 

This below is the procedure.

Any guidance would be appreciated.

Private Sub Create_tbl_IDT_High_Level()
 
   On Error GoTo Err_Hndlr
 
'*****************************************************************************************
' This sub routine builds a table
'
'******************************************************************************************
 
strFirstRec = "Yes"
 
' **** initialize temporary fields, date is set to dummy initial date, it has no meaning
 
 
strTempSeqNumber = 0
strTempIndexOrg = 0
strTempFund = " "
strTempAccountCode = 0
strTempTotalPrice = 0
strTempInd = " "
strTempRuleCode = " "
strTempSalesOrder = " "
strTemp_d_AccumativePrice = 0
strTemp_c_AccumativePrice = 0
Dim tbl_IDT As DAO.TableDef
Dim idxSeqNumber As DAO.Index
 
 
 
'**** create output table
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tbl_IDT_High_Level;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tbl_IDT_High_Level(SeqNumber smallint, IndexOrg Integer, " & _
                                        "Fund VARCHAR(6), " & _
                                        "AccountCode Integer, " & _
                                        "TotalPrice money, " & _
                                        "Ind VARCHAR(1), " & _
                                        "RuleCode VARCHAR(4),  " & _
                                        "SalesOrder VARCHAR(8))")
 
 
' DoCmd.RunSQL "CREATE INDEX idxSeqNumber ON tbl_IDT (SeqNumber) WITH PRIMARY"
 
 
'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset("sql_IDT_RPT_Step03_Format")
Set rstSummary = CurrentDb.OpenRecordset("tbl_IDT_High_Level")
 
 
rstTemp.MoveFirst
 
Do While rstTemp.EOF = False
 
 
 
    ' ***************************************************
    ' first record flag
    ' ***************************************************
 
    If strFirstRec = "Yes" Then
        strFirstRec = "No"
    End If
        strTempSeqNumber = strTempSeqNumber + 1
 
        strTempIndexOrg = rstTemp!IndexOrg
        strTempFund = rstTemp!fund
        strTempAccountCode = rstTemp!AccountCode
        strTempTotalPrice = rstTemp!TotalPrice
        strTempInd = rstTemp!Ind
        strTempRuleCode = rstTemp!RuleCode
        strTempSalesOrder = rstTemp!SalesOrder
 
 
 
        rstSummary.AddNew
            rstSummary!SeqNumber = strTempSeqNumber
            rstSummary!IndexOrg = strTempIndexOrg
            rstSummary!fund = strTempFund
            rstSummary!AccountCode = strTempAccountCode
            rstSummary!TotalPrice = strTempTotalPrice
            rstSummary!Ind = strTempInd
            rstSummary!RuleCode = strTempRuleCode
            rstSummary!SalesOrder = strTempSalesOrder
        rstSummary.UPDATE
 
        ' Get Accummulative Totals
        If strTempInd = "D" Then
            strTemp_d_AccumativePrice = strTemp_d_AccumativePrice + strTempTotalPrice
        Else
            strTemp_c_AccumativePrice = strTemp_c_AccumativePrice + strTempTotalPrice
        End If
 
            Debug.Print "Start******************: ";
            Debug.Print pvbNewLine
            Debug.Print "strTemp_D_AccumativePrice: "; strTemp_d_AccumativePrice;
            Debug.Print pvbNewLine
            Debug.Print "strTemp_c_AccumativePrice: "; strTemp_c_AccumativePrice;
            Debug.Print "end******************: ";
            Debug.Print pvbNewLine
 
        strTempIndexOrg = 0
        strTempFund = " "
        strTempAccountCode = 0
        strTempTotalPrice = 0
        strTempInd = " "
        strTempRuleCode = " "
        strTempSalesOrder = " "
 
    rstTemp.MoveNext
Loop
 
'***        write accumative recordst record
        If strTemp_d_AccumativePrice > 0 Then
            strTempSeqNumber = strTempSeqNumber + 1
            strTempIndexOrg = Null
            strTempFund = "UK0000"
            strTempAccountCode = 111101
            strTempTotalPrice = strTemp_d_AccumativePrice
            strTempInd = "C"
            strTempRuleCode = "J099"
 
            rstSummary.AddNew
                rstSummary!SeqNumber = strTempSeqNumber
                rstSummary!IndexOrg = strTempIndexOrg
                rstSummary!fund = strTempFund
                rstSummary!AccountCode = strTempAccountCode
                rstSummary!TotalPrice = strTempTotalPrice
                rstSummary!Ind = strTempInd
                rstSummary!RuleCode = strTempRuleCode
                rstSummary!SalesOrder = strTempSalesOrder
            rstSummary.UPDATE
        End If
 
        If strTemp_c_AccumativePrice > 0 Then
            strTempSeqNumber = strTempSeqNumber + 1
            strTempIndexOrg = " "
            strTempFund = "UK0000"
            strTempAccountCode = 111101
            strTempTotalPrice = strTemp_c_AccumativePrice
            strTempInd = "D"
            strTempRuleCode = "J099"
 
            rstSummary.AddNew
                rstSummary!SeqNumber = strTempSeqNumber
                rstSummary!IndexOrg = strTempIndexOrg
                rstSummary!fund = strTempFund
                rstSummary!AccountCode = strTempAccountCode
                rstSummary!TotalPrice = strTempTotalPrice
                rstSummary!Ind = strTempInd
                rstSummary!RuleCode = strTempRuleCode
                rstSummary!SalesOrder = strTempSalesOrder
            rstSummary.UPDATE
        End If
 
rstTemp.Close
rstSummary.Close
Create_tbl_IDT_High_Level_Exit:
  Exit Sub
 
 
Err_Hndlr:
 
 
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_tbl_IDT_High_Level()"
End Sub
 

[ADezii]

I'm a little confused since you state that rstTemp is Bound to the Temporary Table, but isn't rstSummary?

'Bind rstTemp to the temporary table 
Set rstTemp = CurrentDb.OpenRecordset("sql_IDT_RPT_Step03_Format") 
Set rstSummary = CurrentDb.OpenRecordset("tbl_IDT_High_Level") 

What is the definition of:

sql_IDT_RPT_Step03_Format

[dowlingm815]

Good morning,

rstTemp isn't a temp table, i forgot to delete that comment. Hot dog, :D, got it thank you...fund was converted to binary.