clearing dates in VBA

[DirtyB]

I have a loop of code that i'm running where I clear out variables, then set them equal to a value, then update my recordset...pretty standard. When you clear a string variable, I just set it equal to empty string "". How do I clear out a variable of datatype date? I want to set it equal to NOTHING, preferably not null, but like an empty string. I need to reset a date variable each time in my loop and can't figure it out? I've tried;
variabledate = ""
variabledate = ''
variabledate = null
variabledate = ##
to no avail....can someone please help me?

thanks

 

[cmmrfrds]

Datetime is stored as a decimal number, so, try zero. The defaut display for a zero time is 12 AM.

variabledate = 0

 

[DirtyB]

Thanks for the reply. If you set it equal to zero, it inputs a date of '12/30/1899'. I found that setting it equal to the keyword 'Empty' is working for me in case anyone else wanted the answer.

 

[cmmrfrds]

Great, I am glad empty works for you.

FYI.
One correction, it does not "input 12/30/1899". If you set the variable to zero it will DISPLAY "12/30/1899" since that is Access's display representation of zero in the date. The variable itself contains zero.

'-Try this and you will see the variable does contain zero.
dim tempvar as double, variabledate as date
variabledate = 0
'-remove default display for date by making a double
tempvar = variabledate
debug.print tempvar

'-Once zero is stored in an Access datetime field then any display of that field (without changing it back to numeric) will show the default representation of zero, which in Access is "12/30/1899 12:00:00 AM". I prefer NULL to be stored in a date, if no date is present, since that is easier to understand and work with.

 

[DirtyB]

Wow, that's interesting, thanks for the info. Just out of curiousity...I am exporting these tables and importing them into an OracleDB. If I run an export on these dates that are showing as "12/30/1899" will it export that date or a null or a zero to the text file? I'll check it out....thanks.

 

[DirtyB]

AHHH, more problems..maybe someone can help me with this one too?


After this piece of code
If Not IsNull(rs!HEPDate2) Then
dteHEP2 = rs!HEPDate2
Else
dteHEP2 = Empty
End If
if rs!HEPDATE2 is null, it will now be set to "Empty" which is fine.


After this piece of code

If Not IsNull(rs!HVScreenDate) Then
dteHV1 = rs!HVScreenDate
Else
dteHV1 = Empty
End If


dteHV1 gets set to "12:00 AM". What the heck is this? BOTH fields are type date/time with the same properties and formatting. Why in the world would one get set to "Empty" and the other get set to "12:00 AM"??? Someone please help me....Thanks

 

[cmmrfrds]

Sorry, it took a while to get back to you. My cable modem was down for a day.

What you are seeing is just the date variable being set to zero. That is the way the date or datetime variable displays. The key here is "displays" and depends on the front end or tool and the database. A zero date field in Access displays in Access as 12:00 AM. For some reason Access display only the default time and not the default date, but this is purely a display representation the underlying value is zero in the date field.

As I mentioned previously if you move the date variable to a numeric field and then display the value, you will see what underlying value is, since if you display the variable when it is an Access datatype of Date, you will see the default representation.

Dates are stored in Access and other relational databases as numeric values. The datetime value of "12/31/1899 12:00:00 AM" is stored as numeric 0.0 where the integer part are the number on days since midnight 12/31/1899. So, in this case zero. The decimal part is the number of seconds in 24 hours. There are 86400 seconds in a day it is a little convoluted to look at since it is based on 100 instead of the 60 seconds in a minute.

You may not be able to move the direct numeric value over to Oracle since Oracle may have a different starting point instead of 12/31/1899. I am familar with SQL Server and it starts at 1/1/1990 so there is a 1 day difference in starting point between Access and SQL Server. I believe Oracle is probably the same as SQL Server but I don't know.