Update Access Database From Excel, Problem With Inserting Null Date

Schwimms

New Member
Joined
Jan 31, 2008
Messages
49
Hi All!

I have a form that allows me to insert data into my access database. In that form I have a date field and when it is null I get an error for data mismatch. How do I avoid inserting that field if it is null?

I read that access has a strongly typed field setting that stops nulls from being inserted.

Here is my code:

Private Sub CommandButton2_Click()
txt = TextBox4.Text
TextBox4.Text = Replace(txt, "'", "")

Dim Conn1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim AccessConnect As String
On Error GoTo Error1
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=TestFT.mdb;" & _
"DefaultDir=C:\Documents and Settings\chris\Desktop\FT Data;" & _
"Uid=Admin;Pwd=;"

Conn1.ConnectionString = AccessConnect
Conn1.CursorLocation = adUseClient
Conn1.Mode = adModeReadWrite
Conn1.Open

Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "UPDATE PlanningData SET Job = " & UserForm1.TextBox2 & ", Planning_ACD = '" & UserForm1.TextBox3 & "', Planning_Notes = '" & UserForm1.TextBox4 & "' WHERE FA_Number = " & UserForm1.TextBox1 & ""

Cmd1.Execute
Conn1.Close
Conn1.ConnectionString = ""
Range("e6").Select
Exit Sub
Error1:
MsgBox "Please contact Chris with the error description: " & Err.Description
CutCopyMode = False

End Sub
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

amaiz09

New Member
Joined
Oct 8, 2009
Messages
9
it looks like you posted the update statement. do you have the insert statement code?
in the table design the field might be defined to not allow nulls. if that's the case then you have to supply something for the date. Either way i don't think that's the problem because it would probably can't insert null into non-nullable field.

type mismatch error here might be due to a string/date concatenation that references a null value. in which case you can use isnull() or something to that effect.
 

Schwimms

New Member
Joined
Jan 31, 2008
Messages
49
Thanks for the reply, I meant to say it was an update statement. The table in access does allow nulls. What I did do was add an if statement to handle updating the null datevalue and to assign it the value of null.

Private Sub CommandButton2_Click()
txt = TextBox4.Text
TextBox4.Text = Replace(txt, "'", "")
Dim intJobNum As Variant
Dim dtJobDate As Variant

If TextBox2.Text = "" Then
intJobNum = "Null"
Else
intJobNum = TextBox2.Text
End If

If TextBox3.Text = "" Then
dtJobDate = "Null"
Else
dtJobDate = TextBox3.Text
End If

Dim Conn1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim AccessConnect As String
On Error GoTo Error1
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=TestFTTCS.mdb;" & _
"DefaultDir=C:\Documents and Settings\chris\Desktop\FTTCS Data;" & _
"Uid=Admin;Pwd=;"

Conn1.ConnectionString = AccessConnect
Conn1.CursorLocation = adUseClient
Conn1.Mode = adModeReadWrite
Conn1.Open

Cmd1.ActiveConnection = Conn1
If TextBox3.Text = "" Then
Cmd1.CommandText = "UPDATE PlanningData SET Job = " & intJobNum & ", Planning_ACD = " & dtJobDate & ", Planning_Notes = '" & UserForm1.TextBox4 & "' WHERE FA_Number = " & UserForm1.TextBox1 & ""
Else
Cmd1.CommandText = "UPDATE PlanningData SET Job = " & intJobNum & ", Planning_ACD = '" & dtJobDate & "', Planning_Notes = '" & UserForm1.TextBox4 & "' WHERE FA_Number = " & UserForm1.TextBox1 & ""
End If


Cmd1.Execute
Conn1.Close
Conn1.ConnectionString = ""
Exit Sub
Error1:
MsgBox "Please contact Chris with the error description: " & Err.Description
'If Err.Number = -2019984746659# Then
' MsgBox "test"
'End If
End Sub

Although it does create a problem when selecting the data. To select the data you have to add:

If Rs1(YourRecordNumber in this case mine is 1) = "null" Then
rss = ""
Else
rss = Rs1(1)
End If
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Are you sure the problem is the Null value?

I don't see where you are trying to use a date when you constructing the SQL - all the values from the form appear to be text.

Perhaps that's the reason for the Type Mismatch.

Also what is this meant to do?
Code:
If Rs1(YourRecordNumber in this case mine is 1) = "null" Then
   rss = ""
Else
   rss = Rs1(1)
End If
I don't think you can compare a record from recordset to a single value, that could be another cause for a type mismatch.

Excel does have strong data typing, but a lot of things depend on what other properties you set for the field.

eg is a value required in that field
 

Watch MrExcel Video

Forum statistics

Threads
1,123,474
Messages
5,601,880
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top