If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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") & "#") |
#22
|
|||
|
|||
Convert text to Date
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 |
#23
|
|||
|
|||
Convert text to Date
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 - |
#24
|
|||
|
|||
Convert text to Date
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") & "#") ) |
#25
|
|||
|
|||
Convert text to Date
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") & "#") ) |
#26
|
|||
|
|||
Convert text to Date
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") & "#") ) |
#27
|
|||
|
|||
Convert text to Date
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 |
Thread Tools | |
Display Modes | |
|
|