Does anyone know how to empty a date table field yet? 3

[steve728]

I want to replace a date table field with " / / " (Null).

Thanks in advance,

STeve

 

[johndweidauer]

try "", if the double quote does not work, check to see if Null values are not accepted in the field.

 

[steve728]

I get a type mismatch error. It seems Null values
are not allowed in a date type of field/memvar.
When I initialize a new memvar as a date, the value
within = 12:00:00 AM.

Any other ideas???

Thanks so far..

Steve

 

[TonyJollans]

Hi Steve,

A field in a table which has type Date can be null (unless set up as a required field) - but null is not " / / ", and a date field cannot be set to something which is not a date, although if you really tried I dare say you could manage to output it any way you wanted.

A variable of type Date cannot be null and will be initialised to zero (= midnight, day 1). If you want a variable to be nullable you must declare it as a Variant.

So what do you want to do? Set a date field to null, or make a null date (or a zero date) appear as " / / ", or both?

Enjoy,
Tony

 

[willir]

I seem to recall reading somewhere that the date / time field is actually a floating point number with special rules. For example, decimal numbers are time, and integer values are days.

So non-numeric values are going to generate errors. So you can not use the typical dtMyDate = "".

So to zap a date, try

dtDate = Nothing
or
dtDate = Null

instead.

A few more things...

Is the " / / " a mask that has been put on the form?

You can use a different format such as medium date.

Dates are encapuslated with "#", example, #08/12/2003#, not quotes.

If you want to be real tricky, I suppose you can use an unbound text field and convert it to a date, but you would have to put the code in to handle this.

Richard

 

[willir]

Whoops - I was taking my sutpid pills.

Correct syntax is

dtDate = empty

not null or nothing.

Sorry about the error.

Richard

 

[WhiteZiggy]

Also,

" / / " is nothing more than the format.

Whenever you are ready to view it you set the variable as such Format([Date],"Short Date&quot and it comes out 7/12/03. Look in the help file with Access for more formats you can use.

This is possible when using the field in queries,forms, and reports.

Hope this helps,
WZ

 

[steve728]

Thank you for ALL your help! I only used the " / / "
to express what I ulimately want. It's my old Foxpro background showing up. The closest I got to my object
is with the mdate = empty. I just wanted to use the common:
if isnull(mdate) then
whatever code...
End If

The mdate = empty still results in: 12:00:00 AM

I guess the best I can do is:
If mdate = #12:00:00 AM# then
whatever code...
End If

That's cool. I just though there might be something closer
to what I expected.

Thanks again for ALL your help. I can always count on receiving help from my cyber Buddies.

Steve

 

[Rolliee]

Steve,

a Null date is equal to Dec 31, 1899 according to our ACCESS programmers. Try doing 'date + 1' on a null date. This is awkward but unavoidable as for as I have been able to find out.

Rollie E

 

[steve728]

This is good to know. Thank you! I really appreciate this site! I have submitted a donation and will continue to do
so as time goes on. I haven't used your site for a while now. I make sure to printout and save all the help I receive from you folks in a valuable binder.

Later,

Steve

 

[Rolliee]

also tip the tipsters with a star if it is of value.

 

[TonyJollans]

Hi Rollie,

It would appear that your Access programmers don't understand nulls. A Null Date is a contradiction in terms. Null means "doesn't exist" and if something doesn't exist it is not a date or anything else.

Any expression involving Null is, itself, Null. So Null + 1 is Null. Variables, however, are never null by default (and Date variables can never be null anyway).

Also, a zero date (and, arguably, an empty date variable) is equivalent to 30/12/1899.

Enjoy,
Tony

 

[WhiteZiggy]

Tony,

Aye, but Null <> Null . Unknown does not equal Unknown.

But Null & &quot;*&quot; = Null & &quot;*&quot;...

WZ

 

[TonyJollans]

Nice observations

 

[Chrisbi]

When I want to test for null or empty or whatever on date, I use:
If Len(datefield)<1 or isnull(datefield)
(code for actions if date is not entered)
Else
(code or actions if date is entered)
End if
I'm sure there's a better way but this seems to work & avoids that null-empty-etc. confusion that I never seem to get right!

 

[steve728]

Thank you Chrisbi. Excellent idea!

Steve

 

[TonyJollans]

Hi Chrisbi,

If datefield is null, Len(datefield) will also be null. You will get away with code like that in a query but it is far better to use ..

If Len(Nz(datefield)) < 1

Enjoy,
Tony

 

[BillPower]

Hi All,

I've suggested this a few times on TT, paste this into a Module:

Function IsNothing(varV As Variant) As Integer
IsNothing = False
Select Case varType(varV)
Case vbEmpty
IsNothing = True
Case vbNull
IsNothing = True
Case vbString
If Len(varV) = 0 Then
IsNothing = True
End If
Case Else
IsNothing = False
End Select
End Function

To use:

If IsNothing(datefield) then

Bill