Convert text to Date

Your syntax is incorrect for the CDate function. Try

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

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


"catharinus van der werf"
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



"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

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



"Douglas J. Steele" wrote:

Your syntax is incorrect for the CDate function. Try

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

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


"catharinus van der werf"
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



"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

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

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


"catharinus van der werf"
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.

Catharinus van der Werf



"Douglas J. Steele" wrote:

Your syntax is incorrect for the CDate function. Try

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

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


"catharinus van der werf"

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



"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

On 21 mei, 14:05, catharinus van der werf
wrote:
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
"Douglas J. Steele" wrote:
Your syntax is incorrect for the CDate function. Try

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

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

"catharinus van der werf"
wrote in ....
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

On 21 mei, 14:24, "Douglas J. Steele"
wrote:
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"
wrote in ...



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


"Douglas J. Steele" wrote:

Your syntax is incorrect for the CDate function. Try

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

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

"catharinusvan der werf"

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


"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
theDateOrder to YMD, set theDateDelimiter 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 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

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

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


"catharinus" wrote in message
...

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

On 27 mei, 15:49, "Douglas J. Steele"
wrote:
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!)

"catharinus" wrote in message

...

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


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

On 27 mei, 16:42, catharinus wrote:
On 27 mei, 15:49, "Douglas J. Steele"





wrote:
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!)

"catharinus" wrote in message

...

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


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

On 27 mei, 16:42, catharinus wrote:
On 27 mei, 15:49, "Douglas J. Steele"





wrote:
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!)

"catharinus" wrote in message

...

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


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

On 27 mei, 17:26, catharinus wrote:
On 27 mei, wrote:





On 27 mei, 15:49, "Douglas J. Steele"

wrote:
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!)

"catharinus" wrote in message

...

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


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
- 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,

Catharinusvan der Werf
- 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