Convert text to Date

"doesn't work" doesn't say much.

Do you get an error? If so, what's the error. If you don't get an error,
what happens, and what do you feel should be happening instead?

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


"catharinus" wrote in message
...

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

On 3 jun, 13:01, "Douglas J. Steele"
wrote:
"doesn't work" doesn't say much.

Do you get an error? If so, what's the error. If you don't get an error,
what happens, and what do you feel should be happening instead?

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

"catharinus" wrote in message

...





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") & "#")- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Thanks for your reply Doug,

The query doesn't insert any records in the ascces-database..
There is no error-message.


Thanks in advance

Catharinus van der Werf

How are you calling that SQL statement? What's the code around it?

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


"catharinus" wrote in message
...

The query doesn't insert any records in the ascces-database..
There is no error-message.

On 3 jun, 13:01, "Douglas J. Steele"
wrote:
"doesn't work" doesn't say much.

Do you get an error? If so, what's the error. If you don't get an error,
what happens, and what do you feel should be happening instead?

"catharinus" wrote in message

...
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") & "#")- Tekst uit oorspronkelijk
bericht niet weergeven -

On 3 jun, 15:43, "Douglas J. Steele"
wrote:
How are you calling that SQL statement? What's the code around it?

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

"catharinus" wrote in message

...





The query doesn't insert any records in the ascces-database..
There is no error-message.

On 3 jun, 13:01, "Douglas J. Steele"
wrote:
"doesn't work" doesn't say much.

Do you get an error? If so, what's the error. If you don't get an error,
what happens, and what do you feel should be happening instead?

"catharinus" wrote in message

...
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") & "#")- Tekst uit oorspronkelijk
bericht niet weergeven -- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Sorry Doug,

I have been a few days sick.
Yes, I am calling it.

The cpdatumvanaf: 1/1/2002
the cptotDatum: 21/12/2006
call Conconnection.execute("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") & "#") )

Sorry. Don't know why I didn't notice before that you're trying to insert
into a text file. I believe you can only make read-only connections to text
files.

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


"catharinus" wrote in message
...
On 3 jun, 15:43, "Douglas J. Steele"
wrote:
How are you calling that SQL statement? What's the code around it?

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

"catharinus" wrote in message

...





The query doesn't insert any records in the ascces-database..
There is no error-message.

On 3 jun, 13:01, "Douglas J. Steele"
wrote:
"doesn't work" doesn't say much.

Do you get an error? If so, what's the error. If you don't get an
error,
what happens, and what do you feel should be happening instead?

"catharinus" wrote in message

...
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") & "#")- Tekst uit
oorspronkelijk
bericht niet weergeven -- Tekst uit oorspronkelijk bericht niet
weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Sorry Doug,

I have been a few days sick.
Yes, I am calling it.

The cpdatumvanaf: 1/1/2002
the cptotDatum: 21/12/2006
call Conconnection.execute("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") & "#") )

And I just looked a little closer, and see that you're trying to read a text
file, and update an Access table.

I don't think there's any reason for CDATE(FORMAT(DATUM,'0000\-00\-00')) as
datum in that Select. Either Access is going to recognize the field in the
text file as a date value (in which case why convert it?), or it isn't (in
which case the WHERE clause you're trying to apply to the text file won't
work).

Let's look at this systematically. If you create a recordset that returns
the results of the SELECT statement, do you get the correct data returned
from the text file? If you do, that implies that Access is recognizing the
date in the text file.

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


"Douglas J. Steele" wrote in message
...
Sorry. Don't know why I didn't notice before that you're trying to insert
into a text file. I believe you can only make read-only connections to
text files.

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


"catharinus" wrote in message
...
On 3 jun, 15:43, "Douglas J. Steele"
wrote:
How are you calling that SQL statement? What's the code around it?

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

"catharinus" wrote in message

...





The query doesn't insert any records in the ascces-database..
There is no error-message.

On 3 jun, 13:01, "Douglas J. Steele"
wrote:
"doesn't work" doesn't say much.

Do you get an error? If so, what's the error. If you don't get an
error,
what happens, and what do you feel should be happening instead?

"catharinus" wrote in message

...
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") & "#")- Tekst uit
oorspronkelijk
bericht niet weergeven -- Tekst uit oorspronkelijk bericht niet
weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Sorry Doug,

I have been a few days sick.
Yes, I am calling it.

The cpdatumvanaf: 1/1/2002
the cptotDatum: 21/12/2006
call Conconnection.execute("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") & "#") )

On 9 jun, 13:25, "Douglas J. Steele"
wrote:
Sorry. Don't know why I didn't notice before that you're trying toinsertintoatextfile. I believe you can only make read-only connections totext
files.

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

"catharinus" wrote in message

...



On 3 jun, 15:43, "Douglas J. Steele"
wrote:
How are you calling that SQL statement? What's the code around it?

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

"catharinus" wrote in message

...

The query doesn'tinsertany records in the ascces-database..
There is no error-message.

On 3 jun, 13:01, "Douglas J. Steele"
wrote:
"doesn't work" doesn't say much.

Do you get an error? If so, what's the error. If you don't get an
error,
what happens, and what do you feel should be happening instead?

"catharinus" wrote in message

...
I keep having problems toinsertthe 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:
"INSERTINTO[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") & "#")- Tekst uit
oorspronkelijk
bericht niet weergeven -- Tekst uit oorspronkelijk bericht niet
weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Sorry Doug,

I have been a few days sick.
Yes, I am calling it.

The cpdatumvanaf: 1/1/2002
the cptotDatum: * * *21/12/2006
call Conconnection.execute("INSERTINTO[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") & "#") )- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Oke, thnxs