Good morning,
Hope someone can help me out.
I am trying to insert values into a access table.
The spreadsheet where I am taking values from does contain blank cells. This is throwing out my code to insert the values into my table.
This is my code.
Code
Dim cnt As ADODB.Connection
Dim b As Long
Dim dtmWeekEndingDate As Date
Dim strPayRollNumber As String, strRateType As String
Dim strUnits As Double, curTotalCharge As Currency
Dim stDB As String, stConn As String, stSQL As String
Dim strINSERT As String, strWHERE As String, strSQL As String
Dim Period As String, BN As String, TS As String, Name As String, TotalHours As Double
Dim Branch As String, CostCode As String, Pay1 As Currency, Hours1 As Double
Dim Pay2 As Currency, Hours2 As Double, Pay3 As Currency, Hours3 As Double
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 Date, VATMgtFee As Currency
Dim ReportingTo As String, TotalInvoice As Currency
Dim ans As String
ans = MsgBox("Are you sure you want to add updates into CTRDD?" & vbCrLf & "Changes can not be reversed", vbYesNo)
If ans = vbNo Then
Exit Sub
End If
Application.Cursor = xlWait
Application.DisplayStatusBar = True
Application.StatusBar = "Inserting Records....."
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("A" & b).Formula) > 0
' repeat until first empty cell in column C
' MsgBox (Cells(b, No1Column).Value)
Period = Range("A" & b).Value
BN = Range("b" & b).Value
Branch = Range("c" & b).Value
TS = Range("D" & b).Value
Name = Nz(Range("E" & b).Value, 0)
TotalHours = 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
MgtFee = Range("U" & b).Value
TotalNET = Range("W" & b).Value
SuppliersVAT = Range("X" & b).Value
Agency = Range("AD" & b).Value
JobTitle = Range("AE" & b).Value
TotalInvoice = Range("AC" & b).Value
WeekEnding = Range("AF" & b).Value
ReportingTo = Range("AG" & b).Value
strINSERT = "INSERT INTO tblCGMLAnalyser([Period],[BookingNumber],[Adecco Branch],[T/Sheet No],[Name],[Total Hours],[Cost Code],[Pay Rate 1],[Hours 1],[Pay Rate 2],[Hours 2],[Pay Rate 3],[Hours 3],[Total Pay],[NI],[WTR],[Mngt Fee],[Net Invoice],[Suppliers VAT],[VAT on Management Fee],[Total VAT],[Field25],[Agency],[Job Title],[Week Ending],[Reporting To:])"
strINSERT = strINSERT & " VALUES ( '" & Period & "','" & BN & "'," & Chr$(34) & Branch & Chr$(34) & ",'" & TS & "'," & Chr$(34) & Name & Chr$(34) & ",'" & TotalHours & "','" & CostCode & "','" & Pay1 & "','" & Hours1 & "','" & Pay2 & "','" & Hours2 & "','" & Pay3 & "','" & Hours3 & "','" & TotalPay & "','" & NI & "','" & WTR & "','" & MgtFee & "','" & TotalNET & "','" & SuppliersVAT & "','" & VATMgtFee & "','" & TotalVAT & "','" & TotalInvoice & "'," & Chr$(34) & Agency & Chr$(34) & ",'" & JobTitle & "','" & WeekEnding & "'," & Chr$(34) & ReportingTo & Chr$(34) & ")"
strSQL = strINSERT
Debug.Print b & ". " & strSQL
cnt.Open stConn 'Open connection.
cnt.CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
cnt.Execute (strSQL)
cnt.Close
b = b + 1 ' next row
Loop
Set cnt = Nothing
Application.Cursor = xlDefault
Application.StatusBar = "Ready"
MsgBox ("CGML Updates have now been added")
End SubWhat do I need to do to enable inserting of null values?
I have tried using Nz() but doesn't seem to work
Code
Name = Nz(Range("E" & b).Value, 0)Thanks for your help!