Data Type Conversion Error

chaostheory

Registered User.
Local time
Today, 10:36
Joined
Sep 30, 2008
Messages
69
I am relatively new, ok totally new to access and VBA but i pick up quickly. Im having a problem putting information into access from excel. I basically have a spreadsheet that i have doing various things and formatting data to the way it needs to be to be put into the table. Some of the fields are empty some are populated etc. I did this for another spreadsheet and it works beautifully but for some reason on this one, set up the same with with DAO reference, and identical code (just more fields), it is not working its hanging up on one specific line each time. Can you pleassse take a look at it and give me some insight!!

The red line of code is the line it hangs on, and it hangs on the second pass. The field has a number on the first pass and on the second pass that field is blank. Data type of the cell is number to 2 decimal places, same thing for the field in access, number with 2 dec places. I dont get why there would be a datatype conversion error..when they are the same.

Code:
Sub FromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("Z:\_AC_LBP\Database\03 Converted AC Tracking - Raw Data.mdb")
    ' open the database
    Set rs = db.OpenRecordset("Bare Characterization", dbOpenTable)
    ' get all records in a table
    Sheets("Sheet1").Select
    r = 2 ' the start row in the worksheet
    Do While Len(Range("B" & r).Formula) > 0
    ' repeat until first empty cell in column B
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("Piece ID") = Range("A" & r).Value
            .Fields("Skid") = Range("B" & r).Value
            .Fields("If Reject, Defect") = Range("C" & r).Value
            .Fields("Version of Char Targets") = Range("D" & r).Value
            .Fields("Comments") = Range("E" & r).Value
            .Fields("Ultrasound Results") = Range("F" & r).Value
            .Fields("Light Box Date") = Range("G" & r).Value
            .Fields("Light Box Unit") = Range("H" & r).Value
            .Fields("Light Box Set-up") = Range("I" & r).Value
            .Fields("Light Box Qnt Leaks Next to Skin") = Range("J" & r).Value
            .Fields("Light Box Qnt Leaks NOT Next to Skin") = Range("K" & r).Value
            .Fields("All Light Box Leaks Repaired") = Range("L" & r).Value
            .Fields("Adj Plug Depth F1-1 cnt (mm)") = Range("M" & r).Value
            .Fields("Adj Plug Depth F1-2 (mm)") = Range("N" & r).Value
            .Fields("Adj Plug Depth F1-3 (mm)") = Range("O" & r).Value
            .Fields("Adj Plug Depth F1-4 (mm)") = Range("P" & r).Value
            .Fields("Adj Plug Depth F1-5 (mm)") = Range("Q" & r).Value
            .Fields("Adj Plug Depth F2-1 cnt (mm)") = Range("R" & r).Value
            .Fields("Adj Plug Depth F2-2 (mm)") = Range("S" & r).Value
            .Fields("Adj Plug Depth F2-3 (mm)") = Range("T" & r).Value
            .Fields("Adj Plug Depth F2-4 (mm)") = Range("U" & r).Value
            .Fields("Adj Plug Depth F2-5 (mm)") = Range("V" & r).Value
            .Fields("Plug Strength") = Range("W" & r).Value
            .Fields("SuperFlow Date") = Range("X" & r).Value
            .Fields("SuperFlow Unit") = Range("Y" & r).Value
            .Fields("SuperFlow Set-up") = Range("Z" & r).Value
        [COLOR=red]    .Fields("BP at 150cfm (In H2O)") = Range("AA" & r).Value[/COLOR]
            .Fields("BP at 177cfm (In H2O)") = Range("AB" & r).Value
            .Fields("BP at 300cfm (In H2O)") = Range("AC" & r).Value
            .Fields("BP at 353cfm (In H2O)") = Range("AD" & r).Value
            .Fields("BP at 450cfm (In H2O)") = Range("AE" & r).Value
            .Fields("BP at 600cfm (In H2O)") = Range("AF" & r).Value
            .Fields("BP at 706cfm (In H2O)") = Range("AG" & r).Value
            .Fields("BP at 750cfm (In H2O)") = Range("AH" & r).Value
            .Fields("BP at 900cfm (In H2O)") = Range("AI" & r).Value
            .Fields("Laser Gauge Date") = Range("AJ" & r).Value
            .Fields("Contour Number") = Range("AK" & r).Value
            .Fields("Nominal Height (mm)") = Range("AL" & r).Value
            .Fields("Laser Gauge Software") = Range("AM" & r).Value
            .Fields("Laser Gauge ID") = Range("AN" & r).Value
            .Fields("3D Cylindricity (mm)") = Range("AO" & r).Value
            .Fields("Tube Gage (mm)") = Range("AP" & r).Value
            .Fields("Template (mm)") = Range("AQ" & r).Value
            .Fields("Contour Avg Axis Major") = Range("AR" & r).Value
            .Fields("Contour Avg Axis Minor") = Range("AS" & r).Value
            .Fields("3D Tubegage (mm)") = Range("AU" & r).Value
            .Fields("3D Max Diameter (mm)") = Range("AV" & r).Value
            .Fields("Piece Bow (mm)") = Range("AW" & r).Value
            .Fields("Average Height Deviation (mm)") = Range("AX" & r).Value
            .Fields("Perpendicularity 360") = Range("AY" & r).Value
            .Fields("Perp Major Axis") = Range("AZ" & r).Value
            .Fields("Perp Minor Axis") = Range("BA" & r).Value
            .Fields("Shape (mm)") = Range("BB" & r).Value
            .Fields("Average Diameter (mm)") = Range("BC" & r).Value
            .Fields("Out of Round (mm)") = Range("BD" & r).Value
            .Fields("Parallelism MMC (mm)") = Range("BE" & r).Value
            .Fields("Parallelism MM (mm)") = Range("BF" & r).Value
            .Fields("Parallelism TIR (mm)") = Range("BG" & r).Value
            .Fields("Parallelism E-C-E (mm)") = Range("BH" & r).Value
            .Fields("Parallelism C-E (mm)") = Range("BI" & r).Value
            .Fields("Parallelism A-E (mm)") = Range("BJ" & r).Value
            .Fields("Weight Before Neb (g)") = Range("BK" & r).Value
            .Fields("Weight After Neb (g)") = Range("BL" & r).Value
            .Fields("Neb Date") = Range("BM" & r).Value
            .Fields("Neb Time") = Range("BN" & r).Value
            .Fields("Neb Unit") = Range("BO" & r).Value
            .Fields("Neb Set-up") = Range("BP" & r).Value
            .Fields("Neb Leaks Observed") = Range("BQ" & r).Value
            .Fields("Max Number of Adj Leaks") = Range("BR" & r).Value
            .Fields("Visual Inspection Results") = Range("BS" & r).Value
            .Fields("Order ID") = Range("BT" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    msgbox (r - 2 & " rows of data were added to the Bare Characterization table")
End Sub
 
If the field is blank then it will be a NULL. This will give a mistype error. Try looking up the NZ function in Access Help.
 
i have the fields being populated with scripts as well. If there is no data from the previous sheet, then excel populates "". I also tried " " with a space between to input a space so it wasnt empty, even then i still get a data conversion error. What baffles me is there are other empty fields that go in just fine.
 
Chaos, thanks for your reply. "" will give an empty string that is not the same as a number and will give a mistype as well. I am also baffled by why the other empty fields are gong through OK. Are they also numbers if they are not empty?
 
No i checked most of them are text....

But what i dont get is even if i use " " with a space, the cell itself is still formatted to number with 2 decimal same as the acecss. Why would a space matter in access eyes as they are still the same data type. And if that is the problem what can i put in there to make it work, so that it can be blank.

Copying and pasting the row into access works fine from excel, useing the vba script to do the exact same thing, results in data conversion error.
 
No i checked most of them are text....

But what i dont get is even if i use " " with a space, the cell itself is still formatted to number with 2 decimal same as the acecss. Why would a space matter in access eyes as they are still the same data type. And if that is the problem what can i put in there to make it work, so that it can be blank.

Copying and pasting the row into access works fine from excel, useing the vba script to do the exact same thing, results in data conversion error.
Access knows that " " is not a number. Thats why you get the type conversion error. You could try something like
Code:
[COLOR=#ff0000] .Fields("BP at 150cfm (In H2O)") = CInt(Range("AA" & r).Value)[/COLOR]
[COLOR=#ff0000]
[/COLOR]
or possibly
Code:
[/COLOR]
[COLOR=#ff0000]If Range("AA" & r).Value = " " then[/COLOR]
[COLOR=#ff0000]  .Fields("BP at 150cfm (In H2O)") = 0[/COLOR]
[COLOR=#ff0000]Else[/COLOR]
[COLOR=#ff0000]  .Fields("BP at 150cfm (In H2O)") = Range("AA" & r).Value [/COLOR]
[COLOR=#ff0000]End if[/COLOR]
[COLOR=#ff0000]

Good luck
 
Thank you for the response and tips. Your suggestion would have worked except it would have doubled the length of my code. After meeting with my supervisor for a few minutes we came up with condensed code which ill post just to help anyone who ever runs into a similar problem.

Essentially i read the column names into an array, then created a double loop one for rows one for columns, and in the in loop if the value of the cell equal to nothing, then it skipped that cell, and jumped to the next column in the database too leaving it blank, which allowed access to derive the value of null rather than trying to transfer a null from excel to access. Make sense?

Heres the code:
Code:
Sub FromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
Dim db As Database, rs As Recordset, r As Long, c As Long
Dim FieldName(0 To 100) As String 'the array
Dim countCol As Integer, countRow As Integer
 
    Sheets("Sheet1").Select
    'Selects the correct sheet
 
    countRow = Application.WorksheetFunction.CountA(Range("A:A"))
    countRow = countRow - 1
    For x = 1 To countRow
       If Range("A1").Offset(x, 0).Value = "" Then
       countRow = countRow - 1
       End If
    Next x
    'counts the number of rows
 
    countCol = ActiveSheet.UsedRange.Columns.count
    'counts the number of columns
 
    For c = 0 To countCol
        FieldName(c) = Range("A1").Offset(0, c).Value
    Next c
    'Loads the array with all the column headers in row 1
 
    Set db = OpenDatabase("Z:\_AC_LBP\Database\03 Converted AC Tracking - Raw Data.mdb")
    ' open the database
 
    Set rs = db.OpenRecordset("Bare Characterization", dbOpenTable)
    ' get all records in a table
    For r = 1 To countRow
    'Since were using offset, we want to start on r = 1 which will start on row 2
    'because r = 0 would start on row 1
 
        rs.AddNew
        ' create a new record
 
[COLOR=red]        For c = 0 To countCol[/COLOR]
[COLOR=red]            If Range("A1").Offset(r, c).Value <> "" Then[/COLOR]
[COLOR=red]            rs.Fields(FieldName(c)) = Range("A1").Offset(r, c).Value[/COLOR]
[COLOR=red]            End If[/COLOR]
[COLOR=red]        Next c[/COLOR]
        'If the field is not equal to nothing, meaning it has something in it
        'then the value in the field is moved to the database, otherwise the
        'field is skipped
 
        rs.Update
        'updates the current record when all columns are filled in for one record
    Next r
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    msgbox (r & " rows of data were added to the Bare Characterization table")
End Sub
 

Users who are viewing this thread

Back
Top Bottom