הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Thursday, July 19, 2007 1:24 PM
Hello everyone
I have a date field that could have a value or not. When I try to erase the value - it does it on the form but I can not save it back into Access table in database. I did a search on the issue but didn't find an answer other then switching a field to a string. Please advise! Thanks, Alla
All replies (13)
Tuesday, July 24, 2007 3:28 AM ✅Answered
Alla2552,
1. If you have a form with a date field in it and you want to save null or blank information in that field then you can use:
RecordSet!DateField = null
2. If you use RecordSet!DateField = vbNull then your date field will be set to 12/31/1899 in Access.
3. And if you use RecordSet!DateField = vbNullString you will get an error because that table field is a date, not a string.
RecordSet!DateField = "" also errors because this is a date field, not a string.
4. Coding to look for a valid date is a good habit to get into because it allows you to handle the error gracefully, instead of having your program bomb.
Hope that can help you with the problem.
Monday, August 27, 2007 12:27 PM ✅Answered
Actually, I mentioned the ADO reference because of the earlier post by Bruno Yu regarding the use of the recordset to set a null value:
1. If you have a form with a date field in it and you want to save null or blank information in that field then you can use:
RecordSet!DateField = null
It's been a while since I used the older ADO to connect to Access (I do almost everything in SQL Server now), so I can't say for sure if it would work. My thought was that by creating an instance of the recordset it may be possible to tap into that null-setting functionality... or maybe not. I'm sure there is someone else who could provide that answer.
Thursday, August 23, 2007 12:50 PM
Thank you for your answer. The problem is that VB Studio 2005 - Visual Basic doesn't support Null or vbNull. When I try to save it into Access - I'm using command:
cmd.CommandText = "Update tblMainEIS Set Hiredate = '" & CDate(whire) & "' where EmpNum = '" & wemp & "' "
cmd.ExecuteScalar()
If whire has no value (null) - I can't save it. Before saving I'm checking if the date has valid date value and run the command only if it does. The problem is that if I had date in this field previously (pulled record from Access) and would like to wipe it out - I can't do it. Please help!
Thursday, August 23, 2007 12:53 PM
What type of variable is whire?
Thursday, August 23, 2007 12:57 PM
whire is a string; HireDate field in Access is a Date field. Thanks!
Thursday, August 23, 2007 1:05 PM
Have you tried:
If whire = "" Then
whire = DBNull.Value
End If
Thursday, August 23, 2007 1:31 PM
Tried it before - doesn't work. I'm getting data type mismatch error - since it's a date field in Access.
Thursday, August 23, 2007 1:36 PM
Yea i know it works in SQL, didn't know about Access.
Thursday, August 23, 2007 5:58 PM
Why don't you just put in some silly date (1/1/1001) and then when you show the dates in a grid (or whatever) add logic to the grid that shows a blank.
Thursday, August 23, 2007 6:46 PM
Thanks - can't do it. I have multiple fields as a date fields - for example Termination date. It will mean to everybody else that the person is termed when it's not. For example - some reports are triggered by this field - if it has a value. So I do need to have either valid date in the field or have Null value in it and need ability to clear date field - if someone entered date by mistake. Thanks
Friday, August 24, 2007 1:30 AM
To som e degree I agree with Swade's logic in this. If you cannot place a null value in an Access datetime field (at least if you want to use a .NET interface) then you may have to change the logic that allows that field be the determinant for whether or not a person has been terminated. You could just as easily have a bit field that performs the same task and then have the "TermDate" field allow 1/1/1900 as a default empty value. At least in the user interface you could display a blank when necessary so the user doesn't have to be confused.
Or you could just use an ADO reference and pull in the recordset functionality.
Friday, August 24, 2007 1:46 PM
Thanks. I have 8 date fields total that would need those changes - if I decide to go this way. As I mentioned before - some of those fields will trigger updating some other tables - if there are values in those fields. I'm not sure that I'd want to re-write the processing because of it. It'd probably be more efficient just to switch all date fields to strings in the table in Access and I won't have Null problem anymore (but will have to re-do Crystal reports that use date fields). I thought that there must be an alternative way of doing it.
You did mention ADO reference and using recordset functionality - can you explain it a little more? Will it solve Null - Date problem? Are there any examples on the web? Thanks, Alla
Monday, August 27, 2007 1:21 PM
Thanks. I believe it'll be again the problem with VB not accepting Null value. I'm guessing that if nobody else experienced this problem or have not found an answer - I'll have to switch to string field.