Amerikaanse datum omzetten in Europese

[rvdsch]

Hoi allemaal,

Ik importeer vanuit Excel een blad met gegevens in een tabel. In de tabel staat een veld [S date]. [S date] heeft de notatie op d\-m\-yyyy staan, het gegevenstype van het veld is datum/tijd.

Nu is het probleem dat de gegevens in Excel niet goed staan ze staan er met een Amerikaanse datum in. De tabel importeert een Amerikaanse datum maw in mijn veld [S date] staan de gegevens als mm/dd/yyyy. Ik wil graag in [S date] alle dagen en maanden omdraaien naar een europese notatie toe.

Hoe kan ik dit doen?

Gr,

Rob

 

[OctaFish]

Dat gaat heel erg lastig worden als de datums verkeerd zijn ingelezen in een nieuw datumveld; was 8-6-2011 nu 8 juni, of 6 augustus? Je kunt dus niet zonder meer alle dagen en maanden omkeren. Als je de datum inleest in een tekstveld, is het niet zo lastig, want dan weet je zeker dat de maanden vooraan staan, gevolgd door de dagen. Dan kun je met DateSerial een nieuwe datum opbouwen. Dus dat is het eerste dat je uit moet zoeken.

Wat ik zelf doe, maar ik weet niet of dat bij jou mogelijk is, is de datum in Excel omzetten naar een getal. In Access werk ik dat getal met CDate weer om naar een datum. Gegarandeerd de juiste datum.

 

[rvdsch]

Dankjewel. De data staan verkeerd aangeleverd in Excel in een Amerikaanse formaat, dat wil zeggen degene die de data invoert gebruikt een Amerikaanse format. Access leest de data op zich goed in maw Access neemt over wat in Excel staat. De gegevens worden in Access ingelezen als datum maar kan ik het bijvoorbeeld ook numeriek inlezen de nummers transponeren en het dan opslaan als datum?

 

[OctaFish]

Als de data al is ingelezen ben je al te laat, want de datums zijn dan al gelezen als datum, met de omzetten als gevolg. Je moet er dus voor zorgen dat je bij het importeren de datums uit excel in een tekstkolom inleest, als je ze in Excel niet kunt converteren naar een getal of tekst.

 

[rvdsch]

Het is de bedoeling dat de Excel files gaan worden aangepast maar voorlopig gaat dat nog niet gebeuren. Ik wil het graag in Access doen want als het dan werkt hoef ik het maar een keer te doen. Ik kan in Access de veldtypes in de tabel aanpassen. Ik heb een notatie van dd\mm\yyyy afgedwongen en het werkt zolang dd groter is dan 12. Ik heb dus een halve oplossing
Is het mogelijk dat ik het nog op een andere manier de rest goed doe?

De excel file wordt zonder opmaak aangeleverd ik kan het dus in Access opslaan als tekst, numeriek of datum.

Dus stel dat ik een tabel heb met een tekstveld waar in staat:
02/04/2006 en 05/19/2007

dan wil ik dat het word omgezet word naar
04-02-2006 en 19-05-2007

en op geslagen als datum

 

[OctaFish]

De datumnotatie doet niks met en zegt jammer genoeg niks over de opgeslagen datum. Je halve oplossing is dan ook geen halve oplossing, want je hebt nu geen flauw idee meer over de datums waarvan de dag kleiner is dan 12. En dat is nu net het probleem. Je moet het dus echt via de import regelen, en dan zou ik, als je in het excel bestand niks kan/mag worden aangepast, de datumkolom dus eerst importeren in een tekstveld. Ik zou daarvoor dan wel een importspecificatie maken, en het bestand importeren als CSV bestand. Op basis van het geïmporteerde bestand kun je van de datumtekst mm/dd/jjjj weer een datumveld maken in de goed opmaak.

 

[rvdsch]

ik pas liever niets in Excel aan. Ik kan wel een extra tabel creeeren met een extra veld waar de datum wel goed in staat.

Hoe zou ik uberhaupt in Access de aanpassing kunnen doen? Stel ik heb een tabel met een tekstveld van 08/14/2006 hoe kan ik dat dan omzetten in 14-08-2006. Het is geen probleem om een extra veld te maken.

Dankjewel voor het meedenken:thumb:

 

[OctaFish]

Met deze formule maak je een juiste datum van het tekstveld:

NieuweDatum: DateSerial(Mid([Datum];InStrRev([Datum];"/")+1;Len([Datum]));Left([Datum];InStr(1;[Datum];"/")-1);Mid([Datum];InStr(1;[Datum];"/")+1;InStrRev([Datum];"/")-(InStr(1;[Datum];"/")+1)))

Ik heb 'm wat veiliger gemaakt dan vermoedelijk noodzakelijk is, want je kunt op zich wel op basis van vaste posities werken, maar voor het geval er eens een voorloopnul ontbreekt gebruik ik toch liever zoekfuncties om de juiste plek van de scheidingstekens op te zoeken (de functies Instr en InstrRev). Hiermee zoek je de positie op van het scheidingsteken, en je neemt dan de waarde tot het volgende (in geval van de dag).
Met een bijwerkquery zet je de nieuwe datum in je eigenlijke datumveld.

UPDATE Datumtest SET Datumtest.Datum_Nieuw = DateSerial(Mid([Datum],InStrRev([Datum],"/")+1,Len([Datum])),Left([Datum],InStr(1,[Datum],"/")-1),Mid([Datum],InStr(1,[Datum],"/")+1,InStrRev([Datum],"/")-(InStr(1,[Datum],"/")+1)));

Je hoeft overigens je excel bestand niet aan te passen; alleen opslaan als csv bestand is al genoeg.

 

[rvdsch]

Michel Superbedankt:thumb: Dit is precies wat ik wilde. Het enige wat ik nog zou willen weten. Is het mogelijk om het gegevenstype van het veld in de oorspronkelijke tabel nadat ik de updatequery heb gedraaid weer om te zetten van een text-format naar een datum/tijd format?? Kan ik gewoon in de tabel in de ontwerpweergave nadat de update query heeft gedraaid het format aanpassen van text naar datum/tijd?

Dit is echt super iig :thumb:

 

[OctaFish]

Dat kan wel, maar dan kloppen je datums niet meer... Tenzij ik je verkeerd begrijp, en je eerst de bijwerkquery los hebt gelaten op het datum(tekst)veld, zodat je daar de correcte datums hebt staan; dan moet het wel kunnen. Overigens moet je er dan wel 100% zeker van zijn dat er geen verkeerde waarden in de tabel staan, zoals een vergeten tekstveld, want de kans bestaat dat je veld dan leeg wordt gepoetst. Daarom doe ik dit soort conversies altijd met een extra kolom, zodat ik de situatie altijd kan herstellen. Maar het kan dus wel; Access ziet a.h.w. dat je datums in de kolom hebt staan, en zal ze als zodanig tonen als je het veld omzet naar een datumveld.

 

[rvdsch]

Ik gebruik nu een aparte tabel waarin een aparte kolom is toegevoegd met de correcte datum. Hardstikke bedankt voor je hulp:thumb:

 

[OctaFish]

Volgens mij kan dit draadje dan op Opgelost...

 

[HJD2012]

Datum conversie

Uiteindelijk lukte mij hetzelfde door eerste in control pannel de regio op Engels (USA) te zetten en dan leest Excel wel amerikaanse datumvelden correct in. Dan weer terug op Dutch en conversie is gereed.
Minder elegant maar wel simpel!