Solved

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

Posted on 2009-07-07
6
257 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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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 19

Author Comment

by:darbid73
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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 19

Author Comment

by:darbid73
Comment Utility
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 49

Accepted Solution

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

datDate = CDate(strDate)

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now