How to format English date text to dates with foreign regional settings

[boerbende]

Dear reader,
I have a datacollecting system which stores dates in English format as text. Example is
12-DEC-15 14:55:23.0 for December 12th. Besides the record contains a tagname and a value.
12-DEC-15 14:55:23.0 Tag1 20.1
as example

In Access or Excel I use the function format or datevalue or dateserial to get a real date from this string. However, this goes only OK, when the regional settings of the computer are on English. In my case, Norwegian, the format or datevalue do not work with the months MAY (=MAI), OCT (=OKT) and DEC (=DES)
I tried to explain the Norwegian that they abbreviate the months wrongly, but they do not listen
I have solved it in some occasions with a join on a table tbl_month, This works with nested queries in Access, but nested queries gives me problems when calling them from Excel.
Also the format() is difficult, because for example ‘yy’ is not known. Norwegian settings use åå (2 x a with the little dot on the top). I was thinking about using month numbers, but then I need12x nested (iif(‘JAN’,1,iif(‘FEB’,2 .. etc
Does somebody knows a solution for the transformation of this date written as English text field to a real datefield, given that not all the months are written identical between English and other languages

Many thanks
Ben de Boer

 

[Ranman256]

Why are you storing dates as text? A date is the same no matter what country you format it, but it must be a date. (Numeric, not string)

 

[boerbende]

I am not storing myself. If I could change this, my problem would be solved and this question wouldn't have been posted... Sometimes things are just given... fixed...

 

[boerbende]

Found a solution (and tested it)
1) write a function MonthText2Number (monthText as string) as integer
which converts the string to a number in a select Case. Store this function in a module
2) use in a query dateserial(year, monthtext2number(mid(Datetext,4,3)),day) to calculate a real date

Ben

 

[Valery]

Great! Can you provide us with the function and the query? Like in a sample database? I need to convert English dates into French for reports. Don't know how to write a function or use the suggested commands in a query... THANKS