Convert Text To Date

[Twanne]

Hi,

I've got a field in textformat with a date that looks like this: 2005-01-03 22:43:19.65
Now I need to move this field to another field in date format. So far I tried to use IsDate() that gives 31/12/1899 and CVDate() which returns empty. I need the format displayed by IsDate() but I need the correct one ofcourse :)

Greetz
Twanne

[missinglinq]

You say IsDate() gives you 31/12/1899. This is somewhat confusing, because IsDate() doesn't return a date of any kind, it returns True or False, depending on whether the expression it's asked to evaluate can be interpreted as a date or not! 31/12/1899 is what Access defaults to if you try to stuff a non-date in a date defined field, which I guess is why you're getting it; you're passing "False" to a date field!

Is the string always in this exact format, character for character?

Are you trying to retrieve the date and time or just the date portion?

Is this going to be an ongoing thing, getting "dates" in this fashion, or are you just trying to do a one time conversion?

What format do you want the date in; I seem to recall that your version of Access is in Dutch?

Linq ;0)>

[Twanne]

Hey,
I've let it rest for a while but i'm back now :)

The format for this as always the same indeed (2006-10-03 09:26:00.0). But the thing is I get some data from a centralized db send to me in excell. The centralized system uses this type of dates. Now I can import the date's from excell to access using the import worksheet function (or somthing in that trend) and that works. But, like it should be, it puts the dates in text format. No problem there so far. But how do I get it from that text format into a date formated field??

I'd like to do this in sql itself because I know it is possible. I've found some ways around it in vba but they are to complex and not aplicable to other dates that aren't stored in the same way. There should be some kind of consistency, right...

Yeah I know I'm getting frustrated about dates in access, if I could use php or java is wouldn't be a problem.

Greetz

Twanne

If dates are changing in the blink of an eye, what is time doing then?

[Twanne]

Ok, so far I've tried CVDate(), CDate(), Dateformat(), Datevalue(),... some other to, But nothing seems to work.

My query looks like this:

INSERT INTO Behandelingen ( dkey, eadnr, eersteZendtijd, cnr, verslagNr, klasse, behandeling, vorm, route, dosis, aantalKeer, per, start, stop, opmerking, onderhoud )
SELECT ExportBehandelingen.dkey, ExportBehandelingen.eadnr, (CDate(ExportBehandelingen.eersteZendtijd)) AS Datum, ExportBehandelingen.cnr, ExportBehandelingen.verslagNr, ExportBehandelingen.klasse, ExportBehandelingen.behandeling, ExportBehandelingen.vorm, ExportBehandelingen.route, ExportBehandelingen.dosis, ExportBehandelingen.[#keer], ExportBehandelingen.per, ExportBehandelingen.start, ExportBehandelingen.stop, ExportBehandelingen.opmerking, ExportBehandelingen.onderhoud
FROM ExportBehandelingen
WHERE (((ExportBehandelingen.dkey) Not In (SELECT dkey FROM Behandelingen)) AND ((ExportBehandelingen.verslagNr) Not In (SELECT verslagNr FROM Behandelingen)) AND ((ExportBehandelingen.omit)="i"));

The field ExportBehandelingen.eersteZendtijd is in text format, the field that receives the value is in date format. How do I convert this so It doesn't give an error (#NAME?)
The ExportBehandelingen.eersteZendtijd is still in the same format as I said before.

Greetz
Twanne

While my head is getting empty my brain is getting full.