Access 2000 and Date Field

[James Stanich]

Good morning.

I have a question:

I have a record of information with a ADATE field (Assigned Date).

I am trying to allow the user to remove this date.

I have tried rst!ADATE=vbnull, but I get 12/31/1899.

I have tried rst!ADATE-vbempty, but I get 12:00:00 am.

What am I missing? I just want to remove the date and have a blank field.

Any suggestions, cuz I am stumped.

[anilgoje]

Have you tried this...

rst!ADATE=" "

This should work.Just try it and see

[James Stanich]

I tried it and get a data type mismatch.

[anilgoje]

Can you give piece of code so that i can also try on it.

[James Stanich]

Dim R As String

R = Left(frmRequestUnComplete.List1.List(AI - 1), 8)

rst.Open "Select * from Request_Log WHERE ReqNumber='" & R & "'" , cnn, adOpenStatic, adLockPessimistic

rst!Complete = False

rst!ADate = vbNull

rst.Update

the ADATE = vbnull is the problem (I get 12/31/1899). Using other databases, that means 0.


Remember this is a filed in Access 2000 (with datatype set to Date/Time).

[anilgoje]

Hi James,
I tried with this and iam able to insert null into the field.You too try and then tell me whether you are getting the same or not

If Not IsNull(rst!ADATE) = True Then rst!ADATE = Null

I will be waiting for your reply

[James Stanich]

What is the value of ADATE in the database? (ie is it empty, or does it have 12/31/1899?) I have tries exactly what you mention but still have the same problem. I can work around it but it still is a pain.

[anilgoje]

I tried inserting different dates into the ADATE field and then run the program and it is clearing all the dates present in it and places a balnk in the backend ie,ACCESS.I don't understand why you are not getting it but iam able to run it successfully.
Are you sure you are using NULL instead of VBNULL???

[Edited by anilgoje on 11-06-2000 at 03:47 PM]

[James Stanich]

You have got to be kidding me...

OK, since I've been dumbfounded, what is the difference between the two?

[anilgoje]

Hi,
Iam not kidding with you man.See the code which i wrote below and just try on it and you will know what is the difference between using vbNull and Null.What i found is that when you are assigning vbNull to ADATE then it is updating that field with 12/31/1899 but if you are using Null then it is clearing that field.I don't know clearly the difference between these two but the result is varying.You can test with this code

Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DateTest\db1.mdb;Persist Security Info=False"
Set rst = New ADODB.Recordset
rst.Open "Select * from newtable", cnn, adOpenStatic, adLockPessimistic

rst!ADATE = Null ' Here when you assign vbNull then you will notice the difference.
rst.Update

[James Stanich]

Thanks for your help. I used NULL and it work like a charm. Thanks again.