473,322 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Null values to Date/Time Field

171 100+
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
Oct 12 '12 #1

✓ answered by TheSmileyCoder

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

What happens if you simply write:
Expand|Select|Wrap|Line Numbers
  1. recordset!timefield = IIf(IsNull(recordset!timefield) Or recordset!timefield = "", Null, recordset!timefield)

6 9038
TheSmileyCoder
2,322 Expert Mod 2GB
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.
Oct 12 '12 #2
rajeevs
171 100+
Hi
The code line is below where i tried vbnull but it put 00:00:00
Expand|Select|Wrap|Line Numbers
  1. recordset!timefield = IIf(IsNull(recordset!timefield) Or recordset!timefield = "", "Here i want to put null in the table where timefield", recordset!timefield)
Oct 12 '12 #3
TheSmileyCoder
2,322 Expert Mod 2GB
Ok, so the import is done by your custom code, and not some bulk import. Important information.

What happens if you simply write:
Expand|Select|Wrap|Line Numbers
  1. recordset!timefield = IIf(IsNull(recordset!timefield) Or recordset!timefield = "", Null, recordset!timefield)
Oct 12 '12 #4
NeoPa
32,556 Expert Mod 16PB
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.
Oct 12 '12 #5
twinnyfo
3,653 Expert Mod 2GB
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.
Oct 12 '12 #6
rajeevs
171 100+
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
Oct 17 '12 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: jason | last post by:
I need to deposit and empty dummy value into a date/time field within Access using INSERT. However, I pick up a data type error if I attempt to insert a NULL value for this particular date time...
2
by: JP SIngh | last post by:
Can someone please suggest the correct way to store the date & time in SQL Server? I want to store the date and time a record was created. What should the field type be in SQL Server? How can...
9
by: MLH | last post by:
I have a database (datatrek.mdb) with a table named DATA. The table has a date/time field with default value = Now(). It has 100 records in it entered over a 50-minute period. I would like the...
2
by: kaka | last post by:
I'm runnig Redhat 7.3 with postgresql 7.3.4 builded from sources and upgraded recently. I receive this error ( never received before upgrade ) and I don' t know what to do. Error while executing...
3
by: matturbanowski | last post by:
Hi, I have a date/time field in a SQL2000 database, and what I would like to do is to filter on a specific part of the field, for example the time or hour. Supposing I have a set of data for...
9
by: Kyote | last post by:
I have a textbox that is databound to a table in an access database. The field only contains month/day/year but my databound textbox is also showing a time. Is there any way to prevent it from...
3
by: anandmms | last post by:
Hello friends, I am having a table Personal_Detail, and in that table i am having some fields like name, id, phone_number etc... and also with two blob fields photo1, photo2(allow null). my...
13
by: dizzydangler | last post by:
Just a quick question...I'm running an MS Access 2007 db that tracks appointments in a single table. Date and time are entered as separate fields in short date (mm/dd/yyyy) and short time (hh:mm)...
1
by: neelsfer | last post by:
Is there a way of making a Date/time field in a table a "null value"? I would like to have "long time" as a null value. 05/12/2010 12:38:31 PM. Reason - to add up all the fields with "null values"...
0
by: dowlingm815 | last post by:
I currently importing a csv file with an import specification declaring the field values. one field is a date/time field. when the csv file is imported, it clears the date field with null values....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.