Empty Dates.. Why 12:00:00 AM???

[Zurvy]

Dear All,

I'm trying something quite simple, adding dates into a table with the aid of an action SQL statement (INsert Into) in VBA.

However, if a date is empty, the value 12:00:00 AM is filled in! I really want that if it a date is set to empty, that noting is added to the table... I can use an if statement though, to fix it, but why does it fill in 12:00:00 AM if a date is empty?? Can you fix it? Or is there no other way?

I use Access 2003.

tnx,
Zurvy

 

[mresann]

A date value is actually a double integer, formatted for date and time display. 12:00:00 is equivalent to zero.

Using the Iif statement is common in such circumstances.

 

[Pat Hartman]

Are you placing 0 in the field or null?

 

[Zurvy]

thank you for the reply.

mresann ==> if statement should do the trick. But I have 4 date field. Of which 3 are not obligatory. So I would have to make 7 of if statements with different SQL statements. Or I could conjucate the value section of my SQL statement with a fucntion. But I don't get it, this is just stupid.

Pat Hartman ==> I use Empty value, which is Null.

Do you guys now a better way than a If statement?

tnx,
Zurvy

 

[Pat Hartman]

By "empty value" do you mean zero-length string? This is commonly represented by two quotes - "" - this is NOT a null value.

 

[mresann]

Pat, since it's a date field it can only be null or a number (date format). In any case, Zurvy, you can adjust this expression to use in your query. The pertinent Iif statement is in red:

"UPDATE tblShipments 
SET tblShipments.ShipDate = 
[Color=red][B]Iif (IsDate([DateField]),[DateField],Null)[/B][/color]
WHERE (((tblShipments.OrderID)='B'));"

That will insert a null value into the field if you don't insert a date in the proper format. Also, adjust the WHERE condition to insert the date in the proper place.

 

[R. Hicks]

If the default value for the field is set to 0 .. the value can be 12:00 AM if no entry is made during record entry ...
The default value "should not" be set to a 0 value for this reason ...
There should be no default value ... then the value stored will either be a valid date/time value or a Null ...

RDH

 

[Zurvy]

Than you all.

mresann ==> I tried your way, still no luck. I put this into my SQL statement. May it is different when you are trying to insert a date from a FORM field into the table through the DoCmd.RunSQL. Maybe you can help me if I explain it better.

The date which is selected from a Calendar and it is stored into a variable (dtOrderDate). When the form loads, the variable is assigned to be as a date with the default value of "Empty". If the user deletes or doesn't specify any date, the date is set to Empty. Now, when the user presses a button, a SQL statement is run and the date is put in a table. But when the date is empty, 12:00:00 AM is filled in. I tried to put your IIF stament into it, and no luck...still 12:00:00 AM

Here is my function;
Public Function testAppend()
Dim strSQL As String

strSQL = "INSERT INTO tblOrderInput ([ItemStartDate], [ItemEndDate]) "
strSQL = strSQL & "VALUES (" & "#" & dtStartDate & "#,#" & dtEndDate & "#" & ");"

DoCmd.RunSQL strSQL

End Function

Maybe this will help more....

R. Hicks ==> I checked everyhting, and there is no default valuye specified. SO what is wrong??


Pat Hartman ==> Pat, date filled can only contain dates and null values as mresann explained. I use the value "Empty" because it is easier to evaluate in if statements than the "Null" value. So, i set dates which have no value as Empty in VB...eg... MyDate = Empty



Anybody other suggestons?

 

[Rich]

I've never heard of the Function Empty, it must be a new method but it's quite clear that's the problem, use an update query to change all those empty values to Null.
Why do you think it's easier to evaluate Empty rather than Null?
Either IsNull or IsDate will suffice, surely?

This article also gives further insight as to why 12:00AM is returned
Article ID: Q162534

 

[mresann]

Are you ADDING or UPDATING a current record?

 

[Zurvy]

Let's investigate

Dear Rich and everybody else, tnx for the reply.

I will try a different approach to tackle this issue, and maybe you can find me get over my stupidity!

I made the following function with the things that you guys say should happen, and what actually happens on my VB window....

Public Function TestDate()

'A Date without a particular value
Dim UnSpecifiedDate As Date
Debug.Print UnSpecifiedDate 'Returns the value 12:00:00 AM
Debug.Print IsDate(UnSpecifiedDate) 'Returns True
Debug.Print IsNull(UnSpecifiedDate) 'Returns False
Debug.Print IsEmpty(UnSpecifiedDate) 'Returns False

'A Date with Empty value
Dim EmptyDate As Date
EmptyDate = Empty
Debug.Print EmptyDate 'Returns the value 12:00:00 AM
Debug.Print IsDate(EmptyDate) 'Returns True
Debug.Print IsNull(EmptyDate) 'Returns False
Debug.Print IsEmpty(EmptyDate) 'Returns False !!! WHY??? It's not logical!
Debug.Print EmptyDate = Empty 'Returns True!!! This should be so, but why does
'the pervious line return False??

'A Date with Null value
Dim NullDate As Date
NullDate = Null 'GIVES ERROR! Runtime Error '94' --> Invalid use of Null. Why??
'According to the help file, Null value is only valid for Variant data set.
'How can you then assign a Null value to a variable assigned as a date?
'Or do I just don't understand Logic?

End Function


Could you guys tell e what I do wrong??

 

[Zurvy]

Btw, mresann ==> I'm trying to add a new record. But updating will become part of the form as well... but first I need to fix this issue of 12:00:00 AM emptyr dates!