Solved: dates and null values

[ibgreat]

Hello all,

I have a new problem on the form I have been working on. I have an if...then statement to determine if an unbound text box on a form has a value. If it does have value it runs the ADO statement to update the db. Otherwise nothing should happen. The first question is if the db had a date and I wanted to delete it how would I do get my recordset to recognize a null value to be uploaded to the db?

What is actually happening with any date is that the sql statement looks okay, but when it is uploaded to the database I get a default time value. Not sure what's going on here? Here is the if statement that sets up the sql statement.


'if new record
If blnAddMode = True Then
If Not IsNull(Me.txtDOB) Then
strSQL = "INSERT INTO [tblPerson_D](DOB) " & _
"VALUES " & CDate(Me.txtDOB)
MsgBox ("new form1 = " & strSQL)
End If
'if updating an existing record
Else
If Not IsNull(Me.txtDOB) Then
strSQL = "UPDATE [tblPerson_D] SET " & _
"DOB = " & CDate(Me.txtDOB)
MsgBox ("update form2 = " & strSQL)
End If
End If

'set the command to the current connection - don't think we need this here
Set cmdCommand.ActiveConnection = cnABCdb
'set the insert or update SQL statement to the command text
cmdCommand.CommandText = strSQL
'execute the delete command against the database
cmdCommand.Execute

'while connected to the database, go ahead and
'repopulate the recordset to make sure it contains
'the most current values from the database
Set rsPerson.ActiveConnection = cnABCdb
rsPerson.Requery
Set rsPerson.ActiveConnection = Nothing


The sql statement that is generated looks like:

strSQL = UPDATE [tblPerson_D] SET DOB = 1/1/2001

[stanl]

perhaps it should look like

strSQL = UPDATE [tblPerson_D] SET DOB = #1/1/2001#

or

strSQL = UPDATE [tblPerson_D] SET DOB = cDate('1/1/2001')

.02 Stan

[ibgreat]

perfect, any suggestions on the entering a null value back into the table?

[stanl]

perfect, any suggestions on the entering a null value back into the table?

Not sure what you really mean here. In my experience, you can set up a date/datetime field to accept nulls, but that has usually meant you have pre-parsed any proposed entry and if it is not of the proper type, or is null then you just omit updating the date field. Trying to pass 0, or "" usually results in error or bad data. So I would consider modifying your code to something like.

IF [proposed DOB] is valid Then
execute.[this update]
Else
execute.[that update]
EndIf

[CreganTur]

If that doesn't work (loading a null or empty string value into a field), then you could use a delete query via VBA to delete the value of the field.

When you use VBA variables in SQL strings you have to wrap the variables with specific symbols to make sure the data type is correct. There are no symbols if the data type is a number.
For strings you use single quotes:
SQLString = "SELECT * FROM TableName WHERE FieldName = '" & VBAVariable & "'"
For dates, you wrap them with pound signs (#):
strSQL = "UPDATE [tblPerson_D] SET " & _
"DOB = #" & CDate(Me.txtDOB) & "#"

[ibgreat]

Trying to pass 0, or "" usually results in error or bad data.
This was the problem that I previously encountered. I tried to pass ## as well with the same error. Due to this I created the if...then, as I posted it, to look for the null value and only provide an update if NOT ISNULL() was true.


In my experience, you can set up a date/datetime field to accept nulls
This is the part that confuses me. It seems to contrict the prior statement. Can you clarify???


If that doesn't work (loading a null or empty string value into a field), then you could use a delete query via VBA to delete the value of the field.
I tried this and got an error in the DELETE FROM statement. When I looked up the DELETE FROM statement it said it was only used to delete an entire row in a table (vs. a single column value).

I used tried these anyway and got the error:
strSQL = "DELETE FROM [tblPerson_D] SET (DOB) WHERE [PersonID] = " & rsPerson!PersonID
and
strSQL = "DELETE FROM [tblPerson_D]!DOB WHERE [PersonID] = " & rsPerson!PersonID
Currently the code looks like:

If IsNull(Me.txtDOB) Then
strSQL = "UPDATE [tblPerson_D] " & _
"SET DOB = Nothing " & _
"WHERE [PersonID] = " & rsPerson!PersonID
MsgBox ("update date ISNULL = " & strSQL)
Else
strSQL = "UPDATE [tblPerson_D] SET " & _
"DOB = #" & CDate(Me.txtDOB) & "# " & _
"WHERE [PersonID] = " & rsPerson!PersonID
MsgBox ("update date NOT ISNULL = " & strSQL)
End If


and I get the error, "no value given for one or more required parameters error. At least it is trying to pass the null."

[CreganTur]

DELETE FROM statement it said it was only used to delete an entire row in a table
Yeah, you're right about that one... mea culpa


and I get the error, "no value given for one or more required parameters error. At least it is trying to pass the null."
I'm pretty sure that's because you're using 'nothing' in your SQL statement. Nothing is only used to clear object variables.

I've run a few tests on this. I built an Update Query in SQL Design view that updates a single field to a null value, and it worked when executed from the Queries object of Access.

I took the exact same query and adapted it for use in VBA... but VBA would not accept the SQL string. No matter how I adjusted it, it would not accept a Null value being placed into the string. This is very vexing :po:

Okay... after a little more research I've found that Date/Time formatted fields will not allow you to set a null value into the field from VBA because you can't put a null value between the pound signs. You can accomplish what you want using a DAO connection.

Try this:
DIM db As DAO.Database
Dim strSQL as String
Dim SetVal As Varaint

Set db = CurrentDB

If Is Null(Me.txtDOB) Then
SetVal = "Null"
Else
SetVal = "#" & CDate(Me.txtDOB) & "#"
End If

strSQL = "UPDATE [tblPerson_D] " & _
"SET DOB = " & SetVal & " WHERE [PersonID] = " & rsPerson!PersonID
db.Execute strSQL,dbFailOnError

Set db = Nothing

[ibgreat]

Okay... after a little more research I've found that Date/Time formatted fields will not allow you to set a null value into the field from VBA because you can't put a null value between the pound signs. You can accomplish what you want using a DAO connection.


Perfect!!! I was actually already using an ADO connection. I just had to switch to the "null" value in the field vs. nothing, "", or ##.

Additionally, you unintentially solved another problem I was having with the placement of the if...them within the procedure by using the variant type.

Many, many thanks!!:bow:

Thanks!

The last problem I am having on the form actually also relates to the ADO structure. The new record is added without a problem. The date field is added after