How to insert "" string into Date/Time table field? 1

[Katya85S]

I'm pulling data from .inf file to MS Access table.
The application searches through .inf files, finds a key words, indicating, that the data between the key-words needs to be extracted into Access as Date/Time field and extracts this value. Let's say the value is assigned to VBA variable strDate.
I should mention, the data in .inf file is stored in yyyymmdd format, so it looks like 20070201.
To insert it to my datetime field in teh table i have to format it (perhaps, there is a better way, so please advice):

strDate = CDate(DateSerial(Left(strDate , 4), Mid(strDate , 5, 2), Right(strDate , 2)))
strDate = Format(strDate , "mm/dd/yyyy")

From time to time it happens that there is no data between the key-words, and then the extracted value is "".
Then i get an error "Type mismatch" on
strDate = CDate(DateSerial(Left.... Line.
I've tried to avoid this line if strDate="", leaving strDate unformatted for strDate="". I still could not execute insertion. This time an error was "Data type conversion error".
How can i fix it?
Thank you all in advance.

 

[traingamer]

That's because strDate doesn't equal "" (an empty string) it equals null.
You can either check for null or set it equal to

strDate = strDate & ""

Then it could be an empty string.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Katya85S]

Greg, thank you for the replay.
Unfortunately, adding strDate = strDate & "" made no difference to the "result"

 

[traingamer]

Ok, I stand (sit) corrected. Try putting a breakpoint in your code and figure out what strDate is actually equal to before that assignment statement...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Golom]

Maybe you need to do some validation on the data before you attempt the conversion

If Not IsNull(strDate) And IsNumeric(strDate) And Len(strDate) = 8 Then
    strDate = Format(DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2)), "mm/dd/yyyy")

Else
   [COLOR=black cyan]' strDate is not a valid date[/color]
End If

 

[Katya85S]

Greg, i've got that those dates are of this value: "".
I've tried the following IF statements (perhaps you might suggest a better way):

If IsNull(strDate) Then MsgBox ("Date is null")
If IsEmpty(strDate) Then MsgBox "Date is Empty"
If strDate= "" Then MsgBox "Date is ''"

and the message box popped up for "" value.

Golom, strDate, which is empty,. i still need it in my database in that datetime field as Null.
I've tried to assign Null value to the strDate:

if strDate="" then strDate=Null

, hopping, that may be teh Insert query will accept Null for datetime data type, but got an error, saying something like "Improper use of Null"...
Any advices?

 

[ProgramError]

How about checking for the length of the string.You indicated that the date format before conversion is always 4+2+2 characters long and if its anything else then it must be wrong.

if len(strdate) <> 8 then
print a warning
else
covert the date
endif


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[Katya85S]

Guys, my fault i brought some confusions.
I thought using recordset rs, and codding
rs.addnew
rs!DateField=strDate
rs.Update
would have the same ipact on teh application as executing Insert query via command doCmd.RunSQL(strSQL).
So, i was doing my insertsions via rs.AddNew. And now i see, that i should have mentioned it.
I've changed teh code to
doCmd.RunSQL("INSERT INTO tbl(DateField) VALUES('" & strDate & "')"). And it works fine for strDate="".
...Thank you all of you for your inputs, guys!
though... it still would be nice to know how to add "" string to a table via recordset code.

 

[PHV]

"Improper use of Null"
Dim strDate As Variant

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Katya85S]

PH, thanks much! This helped and fixed the problem.
Having strDate as Variant i was able to assign a Null value to it. And recordset had no problem adding a Null value to teh date-time field in a table via

rs.addnew
rs!DateField=strDate
rs.Update

code.
Thank you!

 

[Katya85S]

One more question...
Which way is concidered to be more efficient, through recordset's AddNew_Update, as in
rs.addnew
rs!DateField=strDate
rs.Update
or running sql query, as in
doCmd.RunSQL(strInsert)
?
Thank you all again