By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,910 Members | 1,085 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,910 IT Pros & Developers. It's quick & easy.

Data Conversion Error - Need Fresh Eyes

100+
P: 133
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:

Expand|Select|Wrap|Line Numbers
  1.    rstSummary!fund = strTempFund
  2.  
This below is the procedure.

Any guidance would be appreciated.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Create_tbl_IDT_High_Level()
  2.  
  3.    On Error GoTo Err_Hndlr
  4.  
  5. '*****************************************************************************************
  6. ' This sub routine builds a table
  7. '
  8. '******************************************************************************************
  9.  
  10. strFirstRec = "Yes"
  11.  
  12. ' **** initialize temporary fields, date is set to dummy initial date, it has no meaning
  13.  
  14.  
  15. strTempSeqNumber = 0
  16. strTempIndexOrg = 0
  17. strTempFund = " "
  18. strTempAccountCode = 0
  19. strTempTotalPrice = 0
  20. strTempInd = " "
  21. strTempRuleCode = " "
  22. strTempSalesOrder = " "
  23. strTemp_d_AccumativePrice = 0
  24. strTemp_c_AccumativePrice = 0
  25. Dim tbl_IDT As DAO.TableDef
  26. Dim idxSeqNumber As DAO.Index
  27.  
  28.  
  29.  
  30. '**** create output table
  31. 'Delete temporary table
  32. DoCmd.RunSQL "DROP TABLE tbl_IDT_High_Level;"
  33.  
  34. 'Create temporary table
  35. CurrentDb.Execute ("CREATE TABLE tbl_IDT_High_Level(SeqNumber smallint, IndexOrg Integer, " & _
  36.                                         "Fund VARCHAR(6), " & _
  37.                                         "AccountCode Integer, " & _
  38.                                         "TotalPrice money, " & _
  39.                                         "Ind VARCHAR(1), " & _
  40.                                         "RuleCode VARCHAR(4),  " & _
  41.                                         "SalesOrder VARCHAR(8))")
  42.  
  43.  
  44. ' DoCmd.RunSQL "CREATE INDEX idxSeqNumber ON tbl_IDT (SeqNumber) WITH PRIMARY"
  45.  
  46.  
  47. 'Bind rstTemp to the temporary table
  48. Set rstTemp = CurrentDb.OpenRecordset("sql_IDT_RPT_Step03_Format")
  49. Set rstSummary = CurrentDb.OpenRecordset("tbl_IDT_High_Level")
  50.  
  51.  
  52. rstTemp.MoveFirst
  53.  
  54. Do While rstTemp.EOF = False
  55.  
  56.  
  57.  
  58.     ' ***************************************************
  59.     ' first record flag
  60.     ' ***************************************************
  61.  
  62.     If strFirstRec = "Yes" Then
  63.         strFirstRec = "No"
  64.     End If
  65.         strTempSeqNumber = strTempSeqNumber + 1
  66.  
  67.         strTempIndexOrg = rstTemp!IndexOrg
  68.         strTempFund = rstTemp!fund
  69.         strTempAccountCode = rstTemp!AccountCode
  70.         strTempTotalPrice = rstTemp!TotalPrice
  71.         strTempInd = rstTemp!Ind
  72.         strTempRuleCode = rstTemp!RuleCode
  73.         strTempSalesOrder = rstTemp!SalesOrder
  74.  
  75.  
  76.  
  77.         rstSummary.AddNew
  78.             rstSummary!SeqNumber = strTempSeqNumber
  79.             rstSummary!IndexOrg = strTempIndexOrg
  80.             rstSummary!fund = strTempFund
  81.             rstSummary!AccountCode = strTempAccountCode
  82.             rstSummary!TotalPrice = strTempTotalPrice
  83.             rstSummary!Ind = strTempInd
  84.             rstSummary!RuleCode = strTempRuleCode
  85.             rstSummary!SalesOrder = strTempSalesOrder
  86.         rstSummary.UPDATE
  87.  
  88.         ' Get Accummulative Totals
  89.         If strTempInd = "D" Then
  90.             strTemp_d_AccumativePrice = strTemp_d_AccumativePrice + strTempTotalPrice
  91.         Else
  92.             strTemp_c_AccumativePrice = strTemp_c_AccumativePrice + strTempTotalPrice
  93.         End If
  94.  
  95.             Debug.Print "Start******************: ";
  96.             Debug.Print pvbNewLine
  97.             Debug.Print "strTemp_D_AccumativePrice: "; strTemp_d_AccumativePrice;
  98.             Debug.Print pvbNewLine
  99.             Debug.Print "strTemp_c_AccumativePrice: "; strTemp_c_AccumativePrice;
  100.             Debug.Print "end******************: ";
  101.             Debug.Print pvbNewLine
  102.  
  103.         strTempIndexOrg = 0
  104.         strTempFund = " "
  105.         strTempAccountCode = 0
  106.         strTempTotalPrice = 0
  107.         strTempInd = " "
  108.         strTempRuleCode = " "
  109.         strTempSalesOrder = " "
  110.  
  111.     rstTemp.MoveNext
  112. Loop
  113.  
  114. '***        write accumative recordst record
  115.         If strTemp_d_AccumativePrice > 0 Then
  116.             strTempSeqNumber = strTempSeqNumber + 1
  117.             strTempIndexOrg = Null
  118.             strTempFund = "UK0000"
  119.             strTempAccountCode = 111101
  120.             strTempTotalPrice = strTemp_d_AccumativePrice
  121.             strTempInd = "C"
  122.             strTempRuleCode = "J099"
  123.  
  124.             rstSummary.AddNew
  125.                 rstSummary!SeqNumber = strTempSeqNumber
  126.                 rstSummary!IndexOrg = strTempIndexOrg
  127.                 rstSummary!fund = strTempFund
  128.                 rstSummary!AccountCode = strTempAccountCode
  129.                 rstSummary!TotalPrice = strTempTotalPrice
  130.                 rstSummary!Ind = strTempInd
  131.                 rstSummary!RuleCode = strTempRuleCode
  132.                 rstSummary!SalesOrder = strTempSalesOrder
  133.             rstSummary.UPDATE
  134.         End If
  135.  
  136.         If strTemp_c_AccumativePrice > 0 Then
  137.             strTempSeqNumber = strTempSeqNumber + 1
  138.             strTempIndexOrg = " "
  139.             strTempFund = "UK0000"
  140.             strTempAccountCode = 111101
  141.             strTempTotalPrice = strTemp_c_AccumativePrice
  142.             strTempInd = "D"
  143.             strTempRuleCode = "J099"
  144.  
  145.             rstSummary.AddNew
  146.                 rstSummary!SeqNumber = strTempSeqNumber
  147.                 rstSummary!IndexOrg = strTempIndexOrg
  148.                 rstSummary!fund = strTempFund
  149.                 rstSummary!AccountCode = strTempAccountCode
  150.                 rstSummary!TotalPrice = strTempTotalPrice
  151.                 rstSummary!Ind = strTempInd
  152.                 rstSummary!RuleCode = strTempRuleCode
  153.                 rstSummary!SalesOrder = strTempSalesOrder
  154.             rstSummary.UPDATE
  155.         End If
  156.  
  157. rstTemp.Close
  158. rstSummary.Close
  159. Create_tbl_IDT_High_Level_Exit:
  160.   Exit Sub
  161.  
  162.  
  163. Err_Hndlr:
  164.  
  165.  
  166.     MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_tbl_IDT_High_Level()"
  167. End Sub
  168.  
Feb 15 '11 #1

✓ answered by ADezii

I'm a little confused since you state that rstTemp is Bound to the Temporary Table, but isn't rstSummary?
Expand|Select|Wrap|Line Numbers
  1. 'Bind rstTemp to the temporary table 
  2. Set rstTemp = CurrentDb.OpenRecordset("sql_IDT_RPT_Step03_Format") 
  3. Set rstSummary = CurrentDb.OpenRecordset("tbl_IDT_High_Level") 
What is the definition of:
Expand|Select|Wrap|Line Numbers
  1. sql_IDT_RPT_Step03_Format

Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,623
I'm a little confused since you state that rstTemp is Bound to the Temporary Table, but isn't rstSummary?
Expand|Select|Wrap|Line Numbers
  1. 'Bind rstTemp to the temporary table 
  2. Set rstTemp = CurrentDb.OpenRecordset("sql_IDT_RPT_Step03_Format") 
  3. Set rstSummary = CurrentDb.OpenRecordset("tbl_IDT_High_Level") 
What is the definition of:
Expand|Select|Wrap|Line Numbers
  1. sql_IDT_RPT_Step03_Format
Feb 16 '11 #2

100+
P: 133
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.
Feb 16 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.