dealing with dates in VBA (MSAccess) not being saved in a table

I have the following problem  


1. I have a Date variable on a UK english date are dd/mm/yyyy.
   2. I write this date to a text file - my .ini file.
   3. the date would look like this "17/11/2008"
   4. Then I change my box to German with German dates dd.mm.yyyyy
   5. I have a date variable
   6. I read from my ini "17/11/2008"
   7. try to pass it into the date variable it will not accept it.

the problem gets worse as I could have a US date or a Japanese date as well. So how should I do this?

Is the only way to change my date variable to a string and then save it in "MYformat" then when getting it again take my format and turn it into a date.
LVL 20
darbid73Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

therealmongooseCommented:
You could store the date as a number in your ini file:
clng(dteDateVariable)
(today is date number 40001)
Then read your date back in and convert it to a date again:
strDate = input from inifile
dtedate = clng(strDate)
That way the value of the date is unaffected by the local date settings...
Or you could create a bespoke database date property and do away with the ini file altogether....
0
Gustav BrockCIOCommented:
Store your date strings formatted as ISO dates:

2008-11-17

And use Format to write that to the ini file:

strDate = Format(datYourDate, "yyyy\-mm\-dd")

/gustav
0
darbid73Author Commented:
gustav - I use your suggestion to write to tables or in queries.

But How do i get the date back?  For example in the .ini would be 2009\07\07

if strdate is declared as a date I cannot say strdate = ini_date?


0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
You will have to modify the way you write your ini file.

strDate is not Date/time but String. Don't know what ini_date is.
And the date string should be 2009-07-07, no backslashes.

/gustav
0
darbid73Author Commented:
Ok sorry I am not typing what is in my brain.

Lets imagine that the XP Regional Settings of a user is German with a date system of dd/mm/yyyy

Thus to avoid any problems I understand you when I write to the ini file  I can write to it by giving it my wanted format of 2009-07-07.

But then imagine the user reads this ini file.  He gets back the information from the ini as a string 2009-07-07 now how do I give this value to a date variable?  His system is not going to like this format.

The long way would be to set up a select case for the countries I am dealing with eg US/GB/JP/CN/DE all have different date systems and then with the format function give this string to a date variable in the format that it understands.  Or can you think of an easier way.
0
Gustav BrockCIOCommented:
He will. Just convert the string to a date:

datDate = CDate(strDate)

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.