Date time field ms access 2000 doesn't zero length string via vb

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

--------------------------------------------------------------------------------

Hi Everyone,
I am creating an application in ms access 2000. I have form with unbound
fields and I wrote a vb routine to post the data on the form to their
respective fields in the corresponding table. That table has two fields of
type DateTime: EndDate and EndTime. As information for those two fields are
not mandatory, I wrote the following code that will basically put a zero
length string in both fields if no value had been entered in both textboxes
on the form:
txbEndDate = IIf(IsNull(txbEndDate), "", txbEndDate)
txbEndTime = IIf(IsNull(txbEndTime), "", txbEndTime)
The following line code assign the values of the text boxes to the fields:
rst!EndDate=txbEndDate
rst!EndTime=txbEndTime
Whe I run the code, I get the error message :"Multiple steps operation
generated errors. Check each status value." Basically that means the date
field cannot accepted the zero length string. Is there any turn around?
Thanks to all of you for your help.
 
Obviously you are not using input masks for your text boxes. I would suggest
you do so to avoid getting other errors from incorrect entry. In design
view, click on the control, select properties, and put your cursor in the
Input Mask property. Press F1. It will guide you through selecting the
appropriate mask. Then you wont have to worry about getting any data type
errors.
 
txbEndDate = IIf(IsNull(txbEndDate), "", txbEndDate)
txbEndTime = IIf(IsNull(txbEndTime), "", txbEndTime)
The following line code assign the values of the text boxes to the fields:
rst!EndDate=txbEndDate
rst!EndTime=txbEndTime
Whe I run the code, I get the error message :"Multiple steps operation
generated errors. Check each status value." Basically that means the date
field cannot accepted the zero length string.
Click to expand...

Correct. A Date/Time field is a number, and not a text string.

You can use a Variant and set it to NULL, but you cannot set a
date/time to a string value, even an empty string.

John W. Vinson[MVP]
 
As you have discovered, and as John has confirmed elsewhere in this thread,
a Date/Time field can not contain a string, empty or otherwise. You have
three choices that I can see ...

1) Store the Null values
2) Use a Text field
3) Store a 'magic' date, e.g. 1 January 100 or 31 December 9999, the minimum
and maximum possible values for a Jet Date/Time field and also for the VBA
Date data type.

Of the three, I would strongly recommend that you use the first. If you go
with the latter, think carefully about whether you need to share the data
with other database systems - SQL Server, for example, has different minimum
and maximum values, and will not accept a date of 1 January 100.
 
Back
Top