Convert text to Date

We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I convert it
to a regular date field before it goes into my database?

Thanks,
Jasper

If the starting value is numerical:

DateSerial( value \ 10000, (value MOD 10000)\100, value MOD 100 )


If the starting value is a string (always 8 characters):

DateSerial( Left(value, 4), Mid(value, 5, 2), Right(value, 2) )




Hoping it may help,
Vanderghast, Access MVP



"Jasper Recto" wrote in message
...
We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I convert
it to a regular date field before it goes into my database?

Thanks,
Jasper

On Tue, 13 May 2008 10:32:44 -0400, Jasper Recto wrote:

We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I convert it
to a regular date field before it goes into my database?

Thanks,
Jasper

To convert an 8 character string to a Date datatype value you can use
the DateSerial function. Look it up in VBA help.

I would import the data as a string.
After it's in your database, add a new field DateTime datatype to the
table, then run an update query:

Update YourTable Set YourTable.NewDateField =
DateSerial(Left([OldFieldName],4),Mid([OldFieldName],5,2),Right([OldFieldName],2))

Then delete to old field from the table (or not).


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Yes. When you're importing, click on the Advanced button in the bottom
left-hand corner of the wizard. Set the field's Data Type to Date/Time, set
the Date Order to YMD, set the Date Delimiter to nothing and check the Four
Digit Year checkbox.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jasper Recto" wrote in message
...
We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I convert
it to a regular date field before it goes into my database?

Thanks,
Jasper

Since the incoming value is yyyymmdd, there's an even easier way that will
work for numeric or text:

CDate(Format(value, "0000\-00\-00"))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
If the starting value is numerical:

DateSerial( value \ 10000, (value MOD 10000)\100, value MOD 100 )


If the starting value is a string (always 8 characters):

DateSerial( Left(value, 4), Mid(value, 5, 2), Right(value, 2) )




Hoping it may help,
Vanderghast, Access MVP



"Jasper Recto" wrote in message
...
We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I convert
it to a regular date field before it goes into my database?

Thanks,
Jasper

Indeed. :-)


Vanderghast, Access MVP


"Douglas J. Steele" wrote in message
...
Since the incoming value is yyyymmdd, there's an even easier way that will
work for numeric or text:

CDate(Format(value, "0000\-00\-00"))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
If the starting value is numerical:

DateSerial( value \ 10000, (value MOD 10000)\100, value MOD 100 )


If the starting value is a string (always 8 characters):

DateSerial( Left(value, 4), Mid(value, 5, 2), Right(value, 2) )




Hoping it may help,
Vanderghast, Access MVP



"Jasper Recto" wrote in message
...
We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I
convert it to a regular date field before it goes into my database?

Thanks,
Jasper

Douglas,

The CDate works great in a query.

Do you know if it's possible to convert data from a TransferText using this
format?

I have a command button that prompts for a file. It than imports a file
into an existing database using an import specifications.

Is there a way to convert that text into the date format BEFORE it goes into
the database table?

Thanks!

Jasper
"Douglas J. Steele" wrote in message
...
Since the incoming value is yyyymmdd, there's an even easier way that will
work for numeric or text:

CDate(Format(value, "0000\-00\-00"))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
If the starting value is numerical:

DateSerial( value \ 10000, (value MOD 10000)\100, value MOD 100 )


If the starting value is a string (always 8 characters):

DateSerial( Left(value, 4), Mid(value, 5, 2), Right(value, 2) )




Hoping it may help,
Vanderghast, Access MVP



"Jasper Recto" wrote in message
...
We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I
convert it to a regular date field before it goes into my database?

Thanks,
Jasper

You need to create an Import Specification (which you can do using the
approach I mentioned in my other post in this thread), and then use that
Import Specification with the TransferText method.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jasper Recto" wrote in message
...
Douglas,

The CDate works great in a query.

Do you know if it's possible to convert data from a TransferText using
this format?

I have a command button that prompts for a file. It than imports a file
into an existing database using an import specifications.

Is there a way to convert that text into the date format BEFORE it goes
into the database table?

Thanks!

Jasper
"Douglas J. Steele" wrote in message
...
Since the incoming value is yyyymmdd, there's an even easier way that
will work for numeric or text:

CDate(Format(value, "0000\-00\-00"))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
If the starting value is numerical:

DateSerial( value \ 10000, (value MOD 10000)\100, value MOD 100 )


If the starting value is a string (always 8 characters):

DateSerial( Left(value, 4), Mid(value, 5, 2), Right(value, 2) )




Hoping it may help,
Vanderghast, Access MVP



"Jasper Recto" wrote in message
...
We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I
convert it to a regular date field before it goes into my database?

Thanks,
Jasper

Awesome!!!

Thanks,
Jasper
"Douglas J. Steele" wrote in message
...
You need to create an Import Specification (which you can do using the
approach I mentioned in my other post in this thread), and then use that
Import Specification with the TransferText method.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jasper Recto" wrote in message
...
Douglas,

The CDate works great in a query.

Do you know if it's possible to convert data from a TransferText using
this format?

I have a command button that prompts for a file. It than imports a file
into an existing database using an import specifications.

Is there a way to convert that text into the date format BEFORE it goes
into the database table?

Thanks!

Jasper
"Douglas J. Steele" wrote in message
...
Since the incoming value is yyyymmdd, there's an even easier way that
will work for numeric or text:

CDate(Format(value, "0000\-00\-00"))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
If the starting value is numerical:

DateSerial( value \ 10000, (value MOD 10000)\100, value MOD 100 )


If the starting value is a string (always 8 characters):

DateSerial( Left(value, 4), Mid(value, 5, 2), Right(value, 2) )




Hoping it may help,
Vanderghast, Access MVP



"Jasper Recto" wrote in message
...
We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I
convert it to a regular date field before it goes into my database?

Thanks,
Jasper

Hello to you all,

I tried the statement in my query to insert a text file in an accces
database, but it failed. Don't know why. Please help:
The statement:

Call conConnection.Execute("INSERT INTO [TABELB] (DATUM) SELECT
CDATE(DATUM,0000\-00\-00) AS DATUM FROM [text;HDR=NO;DATABASE=" & App.Path &
"\telebank\gtz].[table4.asc]")


catharinus van der werf



"Douglas J. Steele" wrote:

Yes. When you're importing, click on the Advanced button in the bottom
left-hand corner of the wizard. Set the field's Data Type to Date/Time, set
the Date Order to YMD, set the Date Delimiter to nothing and check the Four
Digit Year checkbox.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jasper Recto" wrote in message
...
We have dates that are in this format:

20080513

This is the date for 5/13/2008

If I import data from a text file that has this format, how can I convert
it to a regular date field before it goes into my database?

Thanks,
Jasper