[Access 2000] Datum conversie lukt niet

[Maasluip]

Ik krijg een Excel sheet binnen dat ik in een database moet zetten. Er staat een datumveld in waarin de datum als gewoon 5- of 6-cijferig getal is ingevoerd in dMMyy notatie, dus 20106 voor 2 januari 2006 of 130106 voor 13 januari 2006.
Nu wil ik dat veld omzetten naar een goed datumveld. In eerste instantie heb ik het veld opgebroken in de dd, MM en yy stukjes en er een - tussen gezet, met de volgende formule:

code:

Expr1: Left(Format([DATE],"000000"),2) & "-" & Left(Right([DATE],4),2) & "-" & Right([DATE],2)

offtopic:
waarom werkt mid in Access niet?

Dat werkt bij mij wel lekker (Windows datum formaat op dd-MM-yy), maar bij een collega niet (Windows datum format op M/d/yyyy). In een query werkt het wel, dan krijg je voor 20106 mooi de string "02-01-06" en voor 130106 de string "13-01-06", maar als dat in een Date veld gezet wordt dan wordt "02-01-06" naar "2/1/2006" omgezet (klopt niet, dag en maand omgedraaid), en "13-01-06" wordt "1/6/2013"

Wat ik ook probeer, ik krijg deze conversie met die datuminstelling (M/d/yyyy) niet spits.
Ik heb geprobeerd:
• CDate() rond bovenstaande formule te zetten, zelfde resultaat;
• format(..., "dd-mm-yy") rond bovenstaande formule te zetten, zelfde resultaat;
• CDate([DATE]) geprobeerd, dan wordt het nog wilder, en wordt 20106 als datevalue gezien (= 1/17/1955, 30106 = 6/4/1982)
• format([DATE],"ddmmyy") geprobeerd, zelfde probleem als CDate([DATE]).
• bovenstaande formule Y2K-buggificeren door "20" voor het jaar te plaatsen, dan wordt alles van 10106 tot 120106 (1-12 januari) vertaalt naar 1/1/2006 tot 12/1/2006 (januari-december, 1) en vanaf 130106 gaat het weer goed.

Iemand enig idee hoe ik dit voor elkaar krijg? Cruciaal punt: je weet niets van het datumformaat af, dus je kunt daar ook geen rekening mee houden.

Wat ik me vooral afvraag: waarom accepteert Access het niet als je die tweede optie doet? Dan verplicht je Access toch om een conversie met een bepaald formaat te doen, en wat het output formaat is is dan toch niet van belang?

[ Voor 26% gewijzigd door Maasluip op 24-04-2006 14:38 ]

[Lustucru]

dateserial zou het moeten doen, en mid() werkt afaik gewoon in access:

code:

dateserial(right(var$,2),mid(var$,len(var$)-3,2),left(var$,len(var$)-4))

hoewel hier die left(right()) constructie net zo handig/mooi is

[ Voor 23% gewijzigd door Lustucru op 24-04-2006 15:10 ]

[Maasluip]

Dateserial, geweldig, dat werkt.

Maar die mid:

Date	Mid([date],2,1)
20106	0106
130106	30106

En we weten allebei dat mid(var$,2,1) 1 positie vanaf de 2e positie moet geven, dus 0 en 3 respectievelijk.

Echt, overal werkt dit, ook in VBA, maar zo gauw je het in een query in Access zet gaat het mis.

[ Voor 18% gewijzigd door Maasluip op 24-04-2006 15:21 ]

[Lustucru]

Typisch, daar heb ik nu nog nooit problemen mee gehad. Post je hele query eens?

offtopic:
waar ik wél problemen mee heb gehad, en dus ook allang heb afgeleerd, is in namen van velden, tabellen of variabelen gereseveerde of functienamen te gebruiken.

[Maasluip]

Niesje schreef op maandag 24 april 2006 @ 15:29:
Typisch, daar heb ik nu nog nooit problemen mee gehad. Post je hele query eens?

Houden we het simpel:

code:

SELECT Field1, Mid([Field1],3.2) AS Expr1 FROM Table1;

Is Mid wel ANSI-SQL? In Oracle bestaat het niet, daar gebruik je netjes substr, maar dat kent Access dan weer niet.

[edit]
He? Ik zie nu iets heel geks. Daar staat 3.2!?!
Ik zie nu dat in de Design view netjes 3,2 staat maar in de SQL view 3.2!! Als ik in de SQL view 3,2 neer zet gaat het wel goed.
WTF is Access daar nu weer aan het klootviolen!
Na wat testen zie ik dat Access steeds die . weer terug zet als ik in Design view wat verander.

offtopic:
waar ik wél problemen mee heb gehad, en dus ook allang heb afgeleerd, is in namen van velden, tabellen of variabelen gereseveerde of functienamen te gebruiken.

Ach, laat Access daar nu weinig last van hebben, zolang je maar netjes [] om de veldnamen zet.
Daarbij krijg ik dat Excelsheet ook maar aangeleverd en staat daar "Date" als kolomnaam

[ Voor 26% gewijzigd door Maasluip op 24-04-2006 15:51 ]

[Lustucru]

Dan ben ik toch wel benieuwd wat je in windows hebt gesteld bij lijstscheidingsteken en decimaal teken. Standaard (NL) is dat ';' en ',' en dan moet je in designview ook ';' gebruiken om je argumenten te scheiden.
mid(var$;3,3) designview-->mid(var$,3.2)
mid(var$;3;2) desginview-->mid(var$,3,2)
mid(var$,3,2) designview -->bagger

[Maasluip]

List separator staat bij mij als ',' ingegeven. En decimaal ook als ','. Ik heb het veranderd en nu werkt het wel goed.

Wat nu wel vreemd is is dat in SQL view die ',' blijft staan. Als je in SQL view een ';' ingeeft dan krijg je een syntax error. Dus nog altijd voor wat Access daar allemaal aan het modderen is.

Maar nu werkt het wel allemaal

[ Voor 20% gewijzigd door Maasluip op 25-04-2006 07:52 ]