Cleaning Date Variables

[redFred]

Advice I could use,

my problem, I am trying to clear variables Dimensioned as Date

My module code is updating a access db from another access db. I have many date fields that are sometimes empty in the From db. However when the update is done I am getting erroneous data in the To db in the fields that are supposed to be empty. I did a MsgBox to obtain what is in the variable with a return of: 12:00:00AM.

The field data type in each of the db’s is Date/Time.

I think my question is: how can I clear the date fields so I either get the correct data (Dates) or no data (empty) when there is no data.

The following is a snap shot of my code when the StrucStartD should be empty.


Dim StrucStartD As Date

Form_frmMain.ToStrucDate.SetFocus
If IsNull(Form_frmMain.ToStrucDate) Then
Else
StrucStartD = Form_frmMain.ToStrucDate.Text
End If

MsgBox StrucStartD ‘this returns: “12:00:00AM”

‘Open the databse to be updated

Dim acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String

strDbName = "J:\EHENgase\Repair_Development\HDW-Cases\QCPC_Database\CACTUS Delay Tracking DB.mdb"
Set acc = New Access.Application

With rst
.AddNew
![StrSigDate] = StrucStartD ‘ this returns: “1/0/1900”
.Update
.Move 0, .LastModified
End With

rst.Close
acc.Quit
Set rst = Nothing
'Set appAccess = Nothing
Set db = Nothing
MsgBox "Done"

 

[RuralGuy]

Here's a link that should help you understand the DateTime field a little better.
http://support.microsoft.com/default.aspx/kb/q130514/
Both of the values you are seeing indicate the field has a zero in it. Once a numeric field (which is what a DateTime field is) has been initialized you are pretty much stuck with a number.

 

[redFred]

Cleaning Date Variables-0 represents December 30, 1899

This is the work around that I came up with for this situation, pretty simple but thought maybe someone could benefit & for future ref.

Dim StrucStartD As Date

Form_frmMain.ToStrucDate.SetFocus
If IsNull(Form_frmMain.ToStrucDate) Then
Else
StrucStartD = Form_frmMain.ToStrucDate.Text
End If

MsgBox StrucStartD ‘this returns: “12:00:00AM”

‘Open the databse to be updated

Dim acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String

strDbName = "J:\EHENgase\Repair_Development\HDW-Cases\QCPC_Database\CACTUS Delay Tracking DB.mdb"
Set acc = New Access.Application

With rst
.AddNew

‘ this code is a fix for date data ; a value of 0 represents December 30, 1899
' just skip the update if null
' it should be noted that the default value has been set to null in the table

If IsNull(Form_frmMain.ToStrucDate) Then
Else
MsgBox StrucStartD
![StrSigDate] = StrucStartD
End If

.Update
.Move 0, .LastModified
End With

rst.Close
acc.Quit
Set rst = Nothing
'Set appAccess = Nothing
Set db = Nothing
MsgBox "Done"