Solved

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

Posted on 2009-07-07
6
267 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:darbid73
  • 3
  • 2
6 Comments
 
LVL 10

Assisted Solution

by:therealmongoose
therealmongoose earned 200 total points
ID: 24796044
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 24796202
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
 
LVL 20

Author Comment

by:darbid73
ID: 24802497
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Gustav Brock
ID: 24802635
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
 
LVL 20

Author Comment

by:darbid73
ID: 24802769
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
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 300 total points
ID: 24802875
He will. Just convert the string to a date:

datDate = CDate(strDate)

/gustav
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question