Null values to Date/Time Field

[rajeevs]

Hi
I have been searching over the internet to find an answer for my question, but no luck so far.
I have an access table with a date/time field and the required property set as No.
What I am trying is to import a multi line text file to this db using VB. the code split the data to records and insert to the table. but when there is a null value for the date field it says data conversion error.
I want the code line to modify and say that
if isnull then null else field value.
But it is not happening. If i use the above line with VBNull then it inserts 00:00:00 as the value. But I want the field empty if there is no value. I tried so many things but no luck. Hope someone can help

Thank you and Regards

[TheSmileyCoder]

Maybe you could provide some more details on how you import the data?

Otherwise you might run a update query after the import nulling the field if its equal to 00:00:00.

[rajeevs]

Hi
The code line is below where i tried vbnull but it put 00:00:00

recordset!timefield = IIf(IsNull(recordset!timefield) Or recordset!timefield = "", "Here i want to put null in the table where timefield", recordset!timefield)

[TheSmileyCoder]

Ok, so the import is done by your custom code, and not some bulk import. Important information.

What happens if you simply write:

recordset!timefield = IIf(IsNull(recordset!timefield) Or recordset!timefield = "", Null, recordset!timefield)

[NeoPa]

I'm guessing the error message occurs before that line Rajeev. It's very hard to help when only such a small part of the issue is explained or shared though.

My guess is that you would fix this by importing the data first into a table that has no restrictions on what data is imported then, and only then, worry about converting that data into a form that makes sense in your system.

I'm hoping this makes sense for you. It's hard to know what to answer when the question leaves out so much necessary information.

[twinnyfo]

Rajeevs,

If Smiley's Null does not work, you could also try "", which would have a similar effect. Another option is to use code to determine if the value is Null, just bypass adding a value to your field, which is somewhat clumsy. Your goal should be to do as you are doing, checking the validity of the value, then assigning the value to your field.

[rajeevs]

Thank you all. I figured out the reason - there was another field also taking the same info and i didnot notice that. Now i have corrected that. It is taking null now. Thank you all for the support