Convert text to Date

[Jasper Recto]

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

 

[Michel Walsh]

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

 

[fredg]

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).

 

[Douglas J. Steele]

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.

 

[Douglas J. Steele]

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"))

 

[Michel Walsh]

Indeed.


Vanderghast, Access MVP

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"))

 

[Jasper Recto]

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]

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.

 

[Jasper Recto]

Awesome!!!

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.

 

[catharinus van der werf]

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
(e-mail address removed)

 

[Douglas J. Steele]

Your syntax is incorrect for the CDate function. Try

CDate(Format(DATUM,0000\-00\-00))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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
(e-mail address removed)

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.

 

[catharinus van der werf]

He Douglas
thank you.
the format-statement did it,though you probarly forgot the quote-sign
I think the statement should be: CDATE(FORMAT(DATUM,'0000\-00\-00'))

Thanks a lot, you're the best and quickest.

Catharinus van der Werf
(e-mail address removed)

Your syntax is incorrect for the CDate function. Try

CDate(Format(DATUM,0000\-00\-00))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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
(e-mail address removed)

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

(no e-mails, please!)


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 J. Steele]

Yes, you're right. I forgot the quotes. The perils of copy-and-paste!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

He Douglas
thank you.
the format-statement did it,though you probarly forgot the quote-sign
I think the statement should be: CDATE(FORMAT(DATUM,'0000\-00\-00'))

Thanks a lot, you're the best and quickest.

Catharinus van der Werf
(e-mail address removed)

Your syntax is incorrect for the CDate function. Try

CDate(Format(DATUM,0000\-00\-00))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"catharinus van der werf"
<[email protected]>
wrote in message

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
(e-mail address removed)


:

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

(no e-mails, please!)


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

 

[catharinus]

He Douglas
thank you.
the format-statement did it,though you probarly forgot the quote-sign
I think the statement should be: CDATE(FORMAT(DATUM,'0000\-00\-00'))

Thanks a lot, you're the best and quickest.

Catharinus van der Werf

Your syntax is incorrect for the CDate function. Try

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
(e-mail address removed)
:
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

(no e-mails, please!)
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

 

[catharinus]

Yes, you're right. I forgot the quotes. The perils of copy-and-paste!

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

"catharinusvan der werf" <[email protected]>
wrote in message

He Douglas
thank you.
the format-statement did it,though you probarly forgot the quote-sign
I think the statement should be: CDATE(FORMAT(DATUM,'0000\-00\-00'))

Thanks a lot, you're the best and quickest.

Catharinusvan der Werf
(e-mail address removed)

Your syntax is incorrect for the CDate function. Try
CDate(Format(DATUM,0000\-00\-00))
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
"catharinusvan der werf"
<[email protected]>
wrote in message
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]")
catharinusvan der werf
(e-mail address removed)
:
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
theDateOrder to YMD, set theDateDelimiter to nothing and check the
Four
Digit Year checkbox.
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
We have dates that are in this format:
20080513
This is thedatefor 5/13/2008
If I import data from a text file that has this format, how can I
convert
it to a regulardatefield before it goes into my database?
Thanks,
Jasper- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hello Doug,

once aqain, a new problem.

Sometimes a got this message: data type mismatch in criteria
expreskon.
The field in the access table is of type Data/Time, so what can be
wrong.
In one access database, there are no problems, in another I got this
message.

Please help me.

Catharinus van der Werf
(e-mail address removed)

 

[Douglas J. Steele]

Are you certain that there's a value for the date/time field: that it isn't
Null?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Doug,

once aqain, a new problem.

Sometimes a got this message: data type mismatch in criteria
expreskon.
The field in the access table is of type Data/Time, so what can be
wrong.
In one access database, there are no problems, in another I got this
message.

Please help me.

Catharinus van der Werf
(e-mail address removed)

 

[catharinus]

Are you certain that there's a value for thedate/time field: that it isn't
Null?

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



Hello Doug,

once aqain, a new problem.

Sometimes a got this message: data type mismatch in criteria
expreskon.
The field in the access table is of type Data/Time, so what can be
wrong.
In one access database, there are no problems, in another I got this
message.

Please help me.

Catharinusvan der Werf
(e-mail address removed)

Hello Doug,
yes, there are always values. In fact, I use the same text-file to
import in to different access-database. But only in one of these
databases, I get this message.
Very strange.

Catharinus van der Werf
(e-mail address removed)

 

[catharinus]

Hello Doug,
yes, there are always values. In fact, I use the same text-file to
import in to different access-database. But only in one of these
databases, I get this message.
Very strange.

Catharinusvan der Werf
(e-mail address removed)- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hello Doug,

deep shame, the problem was caused by not setting the column-width in
the text-file that I imported in the access-database. Thanks for your
help anyway

Catharinus van der Werf
(e-mail address removed)

 

[catharinus]

Hello Doug,
yes, there are always values. In fact, I use the same text-file to
import in to different access-database. But only in one of these
databases, I get this message.
Very strange.

Catharinusvan der Werf
(e-mail address removed)- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hello Doug,

I found it. The problem was caused by not setting the columnwidth of
the columns in the imported textfile correcty .

Thanks for your help anyway,

Catharinus van der Werf
(e-mail address removed)

 

[catharinus]

Hello Doug,

I found it. The problem was caused by not setting the columnwidth of
the columns in the imported textfile correcty .

Thanks for your help anyway,

Catharinusvan der Werf
(e-mail address removed)- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hello Doug,

I keep having problems to insert the correct records. I want to insrt
records
dated after for example 20-12-2007,. I tried to use the next
statement, but that doesn't work:
"INSERT INTO [TABELB] (PERIODE,Datum) SELECT 0 as
periode,CDATE(FORMAT(DATUM,'0000\-00\-00')) as datum) FROM
[text;HDR=NO;DATABASE=" & App.Path & "\telebank\Postbank].[table4.asc]
WHERE DATUM between #" & Format(cpDatumVanaf, "mm/dd/yyyy") & "# and
#" & Format(cpTotDatum, "mm/dd/yyyy") & "#")

Please help
Catharinus van der Werf
(e-mail address removed)