Solved

International Date Problem

Posted on 2003-12-10
13
326 Views
Last Modified: 2010-05-03
I am Developing one Package for international usage. My Package uses
DATE datatype in lot of fields in databases.

In the Coding, I am following #mmm-dd-yyyy# format, which is a
recommended format for Internationalization.

But, I have to form this format, based on the selected values from the
combo boxes. It gives an Error "Runtime Error 13" - "Datatype Mismastch"

My code is...

Dim StartDate as Date
StartDate = CDate("#" & cbo_Month.Text & "-" & cbo_Day.Text & "-" &
cbo_Year.Text & "#")

will leads to "Data type mismatch" error.

Could anybody able to do something for this problem.
I need this very immediately please.

Thanks.

Yours,
Mahadevan.
0
Comment
Question by:devan_alagar
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
Hi devan_alagar,

You don't need the # characters in the CDate function call. Use;

StartDate = CDate(cbo_Month.Text & "-" & cbo_Day.Text & "-" &
cbo_Year.Text)

Instead. You can add # around the text version of this when using it as a parameter or value in a query to access though.

Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
I don't think you want the "#" characters in your cdate code.  I belive vb needs to see month/day/year not #month/day/year# when doing a cdate.
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
Comment Utility
Just take out the #'s.

Dim StartDate As Date
StartDate = CDate(cbo_Month.Text & "-" & cbo_Day.Text & "-" & cbo_year.Text)

Idle_Mind
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
Comment Utility
Sorry, guess I need to hit Refresh more often before I post. =)
0
 

Author Comment

by:devan_alagar
Comment Utility
Thanks for your(s) precious reply.

Now I am using without the # sign. It works without any error messages.

But, Problem is, I am transfering the Data from one laptop to another laptop (non-networked systems) through a TXT file.

In one Laptop (eg. Win 2000 English OS) is Exporting the Data. In this Laptop, the Date will be in mm-dd-yyyy format. so that, the TXT file will have 12-10-2003, which means the date of 10th Dec, 2003.

In another Laptop (eg. Win Nt German OS) is now Importing that TXT file. In this Laptop, the Date will be in dd-mm-yyyy format. so that the Value read from the TXT file will be 12-10-2003, which means the date of 12th Oct, 2003.

But I want to have 10-12-2003, which means the date of 10th Dec, 2003. This one is the CORRECT DATE.

When I am using the # sign in the Format function, gives an Error.
When I am NOT using the # sign in the Format function, goes to WRONG Date.

I think, I can't define the DATA Transfer flow. Because, the Software was installed in 6 Languages of different OS's.

Also the Data Transfer will go to all the users.

I think, you may get my problem. If not please feel free to ask.

This is the Problem of last 6 months. One way is I may split the DATE fields into 3 Numeric like Date_DD, Date_MM, Date_YY. But this leads to a blendy of work, since a lot of Date Fields are used in lot of Databases.

Please it will be helpful to me if anybody can able to get some ideas.

Thanks for all of you

Yours,
Mahadevan.
0
 
LVL 42

Accepted Solution

by:
frodoman earned 50 total points
Comment Utility
Instead of splitting the date into 3 fields, can you instead add a single field that will indicate the date format?  Then your code can format the characters in the appropriate order.  Still a lot of work but maybe not as much.

Unfortunately you're probably going to have to do something along these lines because using .txt to hold your data simply doesn't offer anything better.  If you can use some sort of database instead that can hold a date type field this would solve the problem with minimal work.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 85

Expert Comment

by:Mike Tomlinson
Comment Utility
If you wrote both versions of the app then you could write out to your text file this:

m12d10y2003

and then when your program reads the file it will know exactly which part is which.

Idle_Mind
0
 

Author Comment

by:devan_alagar
Comment Utility
I will try with the 1st option and let u know the feedback.

What about using XML files instead of TXT files. Is there any special functions available for my requirements.

Thanks.

Yours.
Mahadevan.
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
I don't see xml being much help because you're still going to have to either split the date into pieces or add an indicator to show date type.  Whether you tag this with plain text or wrap it inside an xml tag the end result is basically the same thing.
0
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 200 total points
Comment Utility
Use yyyy-mm-dd in all cases, this will avoid ambiguity for intternational dates. It is by far the simplest way of treating dates when using them in a SQL statement to return the correct data. Access will correctly interpret this to the correct month and day.
0
 
LVL 10

Expert Comment

by:ADSaunders
Comment Utility
Why not transfer the date in your text file as for example "12th Oct 2003"? in any timezone, cDate should be able to interpret this as a date, and display in a format appropriate to the local time format.

.. Alan
0
 

Author Comment

by:devan_alagar
Comment Utility
Thanks to all for your ideas.

Mr.Timcottee's suggestion, "yyyy-mm-dd" works well, with a minimum of coding changes.
Also Mr.Frodoman has some some better ideas.

So, I will split the points to both of them.

Yours,
Mahadevan.
0
 

Author Comment

by:devan_alagar
Comment Utility
Hi,

Pls. interchange the mode of acceptance.

Assisted Answer from TimCottee -> Accepted Answer (1st).
Accepted Answer from frodoman -> Assisted Answer (2nd).

By mistake, i have given the acceptance to FRODOMAN.

Mr. TIMCOTTEE's ideas works well and now my problem was solved.

A hearty thanks to Mr. TimCottee......

Yours,
Mahadevan
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

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