We help IT Professionals succeed at work.

Dates

showme
showme asked
on
265 Views
Last Modified: 2010-08-05
I have a comma delimited text file with double quotation marks surrounding text.  Within each line is a field for dates but they are designated as text(,"01/01/1998",).  Ultimately I need to import these into Access but prior to this I modify the files using a VB program which reads, modifies, and outputs the data.  During this modification process, I have been using CDate() to convert the text date (,"01/01/1998",)into a date data type (,#1998-01-01#,) but when importing into Access, I get an error.  Is there another function or statement I should use to convert the text date into a date data type that Access will recognize?

Thanks in advance, showme

P.S. more points may be awarded pending difficulty
Comment
Watch Question

When you use CDate()in vb are you using it like this?

Dim dl As String
 dl = "01/01/1998"

MsgBox (CDate(dl))  This will give you 1/1/98 as a date.  I don't see the problem.  If I am missing something let me know.


SPECIALIST



Commented:
I find that for these problems using the 01-JAN-1998 format gives the programs least chance to forget which country they are in.

I am going to research this. In the meantime you should re-open this to other experts

Specialist
I still am confused.  I pasted this #1998-01-01# into a querygrid, it still gives me 1/1/98 as a date.  when you import is your table set up as a date field?

Author

Commented:
Specialist,
Thank you for looking into this, I appreciate it.  By the way, try creating a text file an simply put #1998/01/01# (the result of using Cdate()on the text date)in it.  Next try importing it into Access specifying that the above date is a date/time data type.  This should produce the error that I am encountering.  Granted, this whole problem may disappear if I were to directly put the data into an Access table instead of outputting a modified text file and then importing into Access, but since I have the problem I may as well try to figure it out.

Thanks again, showme
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Specialist,
Thanks again for the help.  It seems rather odd that Access is unable to read date literals from a text file but them's the breaks I guess.

Regards, showme
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.