INSERTING value from excel to access (1 Viewer)

[spnz]

Good afternoon,

Hope someone can help out.

I have a piece of code that im using to insert values from a spreadsheet in access.

My problem is inserting the HOURS in my code you can see I use 3 different HOURS (Hours 1 Hours 2 Hours 3)

My problem is that when I import the spreadsheet into access it drops any decimals.

e.g on the excel spreadsheet the hours are displayed at 40.5 when I import the spreadsheet it is been shown at 40.

Can anyone see what im doing wrong?

Sub InsertCostCodeChecking()

  
    Dim cnt As ADODB.Connection
    Dim b As Long
  
    Dim strBookingNumber, strReason As String
    Dim stDB As String, stConn As String, stSQL As String
    Dim strINSERT As String, strWHERE As String, strSQL As String
    
    Dim BN As String, AB As String, TS As String, Name As String, TotalHours As Variant
    Dim Branch As String, CostCode As String, Pay1 As Variant, Hours1 As Variant
    Dim Pay2 As Variant, Hours2 As Variant, Pay3 As Variant, Hours3 As Variant
    Dim TotalPay As Currency, NI As Currency, TotalPayNI As Currency, WTR As Currency
    Dim MarkUp As Currency, Expenses As Currency, MgtFee As Currency, TotalNET As Currency
    Dim SuppliersVAT As Currency, MGTFeeVAT As Currency, TotalVAT As Currency
    Dim Agency As String, JobTitle As String, WeekEnding As String, Concatenate As String
    Dim ReportingTo As String, TotalInvoice As Currency
    
    
   
    

    stDB = "G:\CTRDD\Db\CTRDD_be.mdb"
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"
     ' open the database
     
    Set cnt = New ADODB.Connection
       
  
     
    b = 2 ' the start row in the worksheet
     
     
         Do While Len(Range("C" & b).Formula) > 0
         ' repeat until first empty cell in column C
         
    BN = Range("A" & b).Value
    AB = Range("B" & b).Value
    TS = Range("C" & b).Value
    Name = Range("D" & b).Value
    TotalHours = Range("E" & b).Value
    Branch = Range("F" & b).Value
    CostCode = Range("G" & b).Value
    Pay1 = Range("H" & b).Value
    Hours1 = Range("I" & b).Value
    Pay2 = Range("J" & b).Value
    Hours2 = Range("K" & b).Value
    Pay3 = Range("L" & b).Value
    Hours3 = Range("M" & b).Value
    TotalPay = Range("N" & b).Value
    NI = Range("O" & b).Value
    TotalPayNI = Range("P" & b).Value
    WTR = Range("Q" & b).Value
    MarkUp = Range("R" & b).Value
    Expenses = Range("S" & b).Value
    MgtFee = Range("T" & b).Value
    TotalNET = Range("U" & b).Value
    SuppliersVAT = Range("V" & b).Value
    MGTFeeVAT = Range("W" & b).Value
    TotalVAT = Range("X" & b).Value
    TotalInvoice = Range("Y" & b).Value
    Agency = Range("Z" & b).Value
    JobTitle = Range("AA" & b).Value
    WeekEnding = Range("AB" & b).Value
    ReportingTo = Range("AC" & b).Value
    Concatenate = Range("AD" & b).Value
         
        
  
        strINSERT = "INSERT INTO tblCitiChecking ([Booking No],[Adecco Branch],[T/Sheet No],[Name],[Total Hours],[Branch],[Cost Code],[Pay Rate 1],[Hours1],[Pay Rate 2],[Hours 2],[Pay Rate 3],[Hours 3],[Total Pay],[NI],[Total Pay & NI],[WTR],[Agency Mark Up],[Expenses],[MGT Fee],[Total Net Invoice],[Suppliers VAT],[MGT Fee VAT],[Total VAT],[TOTAL INVOICE],[Agency],[Job Title],[Week Ending],[Reporting To],[Concatenate])"
        strINSERT = strINSERT & " VALUES ( '" & BN & "','" & AB & "','" & TS & "'," & Chr$(34) & Name & Chr$(34) & ",'" & TotalHours & "','" & Branch & "','" & CostCode & "','" & Pay1 & "','" & Hours1 & "','" & Pay2 & "','" & Hours2 & "','" & Pay3 & "','" & Hours3 & "','" & TotalPay & "','" & NI & "','" & TotalPayNI & "','" & WTR & "','" & MarkUp & "','" & Expenses & "','" & MgtFee & "','" & TotalNET & "','" & SuppliersVAT & "','" & MGTFeeVAT & "','" & TotalVAT & "','" & TotalInvoice & "'," & Chr$(34) & Agency & Chr$(34) & "," & Chr$(34) & JobTitle & Chr$(34) & ",'" & WeekEnding & "'," & Chr$(34) & ReportingTo & Chr$(34) & ",'" & Concatenate & "')"
        
       
        Debug.Print strINSERT
        Debug.Print "Line number " & b
       
         
            cnt.Open stConn 'Open connection.
            cnt.CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
            cnt.Execute (strINSERT)
            cnt.Close
         b = b + 1 ' next row
    Loop
  
    

     Set cnt = Nothing
     
     Call ShowErrors
End Sub

Thanks for your help

 

[KeithG]

What data type are you using