Access/VB date question 3

[shannanl]

I have a field in a table that is date/time(short date). I have a variable in my program; dteLastmaint that holds the date. The user clicks a command button to update all the fields in this table. If they change the date in a text box then it updates dteLastmaint with the date. My problem is that if they do not enter a date then I need it to enter a null value into that field. This does not work. I guess you cant insert null fields? What could I make this variable so that it will not automatically insert 1/1/1901 or something like that.

Thanks in advance.

Shannan

 

[LostInCode]

Are you updating the record in code, or is this a bound control and possibly you are using a Data Control?

If through code then:

Dim vDate As Variant

vDate = Text1.Text
If Not IsDate(vDate) Then
vDate = NULL
Else
vDate = Format$(vDate, "yyyy-mm-dd")
End If
rs.Fields("TheDateField").Value = vDate
rs.Update

If through a bound control, then you will need to use an event of the StdDataFormat Object.

 

[shannanl]

It is through code. I force the user into entering a certain format so the format$ should not be needed. I think my problem was I had the vDate as date and tried to enter it as a null value and it gave me an error. This helps tremendously.

Thanks,

Shannan

 

[shannanl]

Well I tried this and I still cant get it to work. It will not let me enter a null value into the date field in the database. I am updating like this.

....Update Table Set field1 = '" & strThis & "', datefield = #" & dateThis & "#"

Any suggestions?

 

[RoyVidar]

I think, using SQL, try setting to null. I e using different SQL strings when the field should be set to null and when there's a valid date:

[tt]...Update Table Set field1 = '" & strThis & "', datefield = Null"[/tt]

Roy-Vidar

 

[zemp]

What I do is assign 'vbNull' to the date type variable. This usually puts '12/31/1899' (sometimes '12/30/1899' or '12:00.00 AM') into the database. Then I just handle that when the information is being displayed with a small function. May not be the best solution but it works that way I need it to.

Here is an example of the function I use,

Public Function Display_Date(pDate As Variant) As String
'// Checks the date and prepares it for display.
   Dim l_strDate As String
   
   On Error GoTo ERR_Handler
   
   pDate = Trim(pDate)
   If pDate = "" Then
      l_strDate = ""
   ElseIf IsNull(pDate) Then
      l_strDate = ""
   ElseIf CDate(pDate) < CDate("1/1/1900") Then
      l_strDate = ""
   ElseIf pDate = "12:00.00 AM" Then
      l_strDate = ""
   Else
      If IsDate(pDate) Then
         l_strDate = FormatDateTime(pDate, vbShortDate)
      Else
         l_strDate = ""
      End If
   End If
   Display_Date = l_strDate
   Exit Function
   
ERR_Handler:
   ErrorMessenger Err.Number, Err.Description, "modGeneral.Display_Date", "General Error"
   Display_Date = l_strDate
End Function

Called this way,

Text1.Text = Display_Date(rs![dteLastmaint])

zemp

 

[shannanl]

Zemp,

I will probably use this function to do the same thing. It seems to be the only way I can find to do it. I appreciate the info (once again).

Thanks,

Shannan

 

[zemp]

Just a note, in the error handler I call another procedure ( ErrorMessenger ) to display the error message to the user. You will have to replace this.

zemp

 

[shannanl]

OK, thanks for the help.

Me

 

[LostInCode]

The Nullable property of the table field needs to be set to True in order to add a NULL. Just check it and change if needed.

Then use this:

Dim vDate As Variant

vDate = Text1.Text
If Not IsDate(vDate) Then
vDate = NULL
Else
vDate = Format$(vDate, "\#yyyy-mm-dd\#")
End If

Update Table Set field1 = '" & strThis & "', datefield = " & vDate

 

[zemp]

All my date fields in my SQL server are set to Accept Null values. Even when i assign 'vbNull' to my date type variables and then upodate the database with them I get the 1899 dates.

zemp

 

[shannanl]

Lost,

The only problem I am running into is that I need to keep the field in the database as a date field. If I use
... ='" & strThis & "'... it throws an error. I am using access and I must do it this way ... = #" & strThis & "#... and it does not like inserting a null value this way.

Thanks,

Shannan

 

[LostInCode]

Assign a NULL, and not a vbNULL.

?IsNull(vbNull)
False

vbNull is a special constant which = 1.
So, it cannot identify it as a date or as a NULL.

Also, this will fail:

#Null#
'Null'

so the date markers need to be removed as well.

 

[zemp]

If I try to assign your 'Null' key word to a date type variable as below,

Dim myDate as date

MyDate = Null

Then I get error 94, Invalid use of null. So that doesn't work either.

I wish I knew how to get a null value in to the database for datetime using a date type variable. So far I haven't found one.

zemp

 

[LostInCode]

You are doing it differently than I explained. Please see my code.

But, if you want to use a date variable instead, then do this:

Dim myDate As Date
Dim vDate As Variant

myDate = Text1.Text

If Not IsDate(myDate ) Then
vDate = NULL
Else
vDate = Format$(myDate, "\#yyyy-mm-dd\#")
End If

conn.Execute "Update Table Set field1 = '" & strThis & "', datefield = " & vDate


or, more lengthy:

Dim myDate As Date

myDate = Text1.Text

If Not IsDate(myDate ) Then
conn.Execute "Update Table Set field1 = '" & strThis & "', datefield = NULL"
Else
conn.Execute "Update Table Set field1 = '" & strThis & "', datefield = " & Format$(myDate, "\#yyyy-mm-dd\#")
End If

 

[zemp]

Yes, I am doing it differently, I am using ADO parameters to prevent SQL injection attacks (see faq709-1526).

Ok I have just been playing with this code and I think that I may have solved my problem somewhat.

First if the date is invalid or blank I assign 'vbNull' to the date Type variable. 'vbNull' = 1 and in a date variable it is converted to '12/31/1899'. Since CDate(vbNull)='12/31/1899' then I can check for this before updating the database and switch it for the Null keyword with this

IIf(Mydate = CDate(vbNull), Null, myDate)

I am using ADO parameters to update the database (SQL server 2000). I am not sure how it would work in an SQL statement.

zemp

 

[LostInCode]

Ever try:

?CDate(1)

All this does is create a date which is the Second day of the start of the positive dates.
This is the same as using the vbNull constant.

?CDate(0)
is the first date.

?CDate(38050)
is todays date.

>I am using ADO parameters to prevent SQL injection attacks
You do not have to worry about this for an example like this, when updating just a date field, as ";xxx" is not a valid date and the IsDate() function will fail, and you are also using a date variable.
But the leading question suggests more than just a date field, or numeric field is being updated.

You can do the same thing I mentioned using a Parameter object (insert real a NULL), just as I showed initially with using a recordset Update.

I do all three methods all the time, and if there is not date, the field is updated with a NULL - no date.

I would be also concerned having a date of 1899, unless you are sure no dates less than 1900 will ever be needed or used.
Then I am sure you have also a coditional statement somewhere in your program to prevent dates less than 1900.

Anyways, do as you please.
I'm done with this thread, unless further help is needed.
All the best to you!

 

[shannanl]

Thanks again guys for the help.

Shannan

 

[zemp]

LostInCode, thanks. You have opened my eyes and forced me to think in a new direction. I learned something today. A star for you.

BTW, sorry for any confusion and if I seemed too stuborn to accept what you were saying. It makes more sense now and I have been know to be stuborn, just ask my spouse.

Thanks again.

Shannan, glad I could be of some help.

zemp

 

[LostInCode]

No problem at all.
Maybe you'll get me on a stubborn day (often daily) and teach me something different/new the next time around.
Our poor wives!