Determine Regional Settings (date format) before import 1

[Aurillius]

Hi there,
MS Access (2003/2007)
Windows XP

I've looked through the forums but can't find an exact example of this problem.
I have a text file kickout from a European designed system which displays date in the format "DD/MM/YY". In order to compliment most of the systems (setup as "MM/DD/YY"), I set the vba reader to take the middle, left and then right before updating the table. Unfortunately there are some systems that actually have their regional settings to "DD/MM/YY".
How do I either:
a: detect the regional settings to determine the format
b: isolate the month, day, year so that the table field (type date) nows how to handle the date

Expiry = Mid(TempLine, 168, 10) 'position of date
Expiry = Mid(Expiry, 4, 2) & "/" & Mid(Expiry, 1, 2) & "/" & Mid(Expiry, 7, 4) 'current method to handle regional setting of "MM/DD/YY"

Thanks
Aurillius

 

[SkipVought]

Hi,

A DATE is a DATE, no matter the regional DISPLAY settings.

debug.print [YourDateField],Year([YourDateField]),Month([YourDateField]),Day([YourDateField])

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Aurillius]

Hi Skip,
Consider a text file that has 08/02/11 in the line feed. If I just capture that position for 8 characters, Access will assume that it is August 2, 2011 if regional settings are "MM/DD/YY". For this file, it is February 2 and needs to recognize that.
Aurillius

 

[PHV]

Use an unambiguous format:
Expiry = Mid(Expiry, 7, 4) & "-" & Mid(Expiry, 4, 2) & "-" & Mid(Expiry, 1, 2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aurillius]

That will work.

Thank you!

Aurillius