Access query to convert string to general date

[SeanDagger]

Hi.

I am new to programming and would like help on converting a text string to a date in access.

I have imported a spreadsheet that contains a date column as text (ie, 24.02.07). I have imported the data into Access no problem. Now I need to run a query to convert the text into a date (General date type preferable). I have tried the query below but that does not work. Could anyone help me with my problem?

UPDATE ExternalClientExtract SET ExternalClientExtract.[Date] = cdate(ExternalClientExtract.Date)
WHERE ((ExternalClientExtract.Date)="24.02.07");

Thank you for looking at my problem.

Best regards

Sean.

 

[Remou]

If the field is defined as text, CDate will not help. You will need another field defined as Date/Time. Depending on your locale a dot (.) may not be suitable as a date delimiter, so:

[tt]UPDATE ExternalClientExtract SET ExternalClientExtract.NewDate = Replace([TextDate],".","/")
WHERE ExternalClientExtract.TextDate="24.02.07"[/tt]

As an aside, Date is a reserved word, reserved words should not be used to name anything.

 

[SeanDagger]

Thanks Remou

Much appreciated.

Since I posted the question I managed to get it working by doing the following:

CDate(Left(ExternalClientExtract.Date,2) & "/" & Mid(ExternalClientExtract.Date,4,2) & "/" & Right(ExternalClientExtract.Date,2))

Looking at your solution, you have a much better resolution (wish I had thought of that first). The query I used works great (I should change the reserved word though yeah).

Now I have successfully converted to date format, my next hurdle will be when I insert the data into our new SalesForce system. The new systems date format is dd/mm/yyyy hh:mm:ss. Not sure if it will accept my format of 24/02/2007. Or will I have to further convert it?

Is there a parameter within CDate to make it a general date?

Any help much appreciated.

 

[Remou]

Not having a time part should not affect the update but be carefull with formats in criteria expressions. Dates are store internally as numbers, so it is best to use a yyyy/mm/dd format, because the rule is that dates must be unambiguous.

 

[SeanDagger]

Funny you should mention that. I have just been informed that the SalesForce system we are importing into accepts the following format:

yyyy-mm-dd T 00:00:00 000z

So my next move is to now change my SQL query. I will hard code the ' T 00:00:00 000z' bit (as not important for migration data) but could you possibly tell me how to modify my code to switch from dd/mm/yyyy to yyyy-mm-dd?

I can easily replace / to - but the not sure about the resersing for date format.

Thanks again.

 

[SeanDagger]

I think I may have cracked it by using

UPDATE ExternalClientExtract SET ExternalClientExtract.[Date] = "20" & Right(ExternalClientExtract.Date,2) & "-" & Mid(ExternalClientExtract.Date,4,2) & "-" & Left(ExternalClientExtract.Date,2)& " T 00:00:00 000z"
WHERE ((ExternalClientExtract.Date) is not null);

I don't think any data imported will be pre 2000 (1999) so my code will work. If I use CDate, it will automatically use / and not -. So I switched the Left, Mid and Right functions so yyyy-mm-dd will be right way round. Not sure if better way to do it.

 

[Remou]

I think you would be better using Year(ExternalClientExtract.Date), Month(ExternalClientExtract.Date) and Day(ExternalClientExtract.Date) unless date is still text and you are very sure where each part is in the string.

 

[SeanDagger]

Yeah the date is still text at this point and it shouldn't need to be converted to text now. I have been told that the destination application will accept the import as text as long as the formatting is spot on. Which it is.

Thank you for your help. If I run into further problems, I will post additions.

Thanks again.