Solved

International Date Problem

Posted on 2003-12-10
13
327 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
ID: 9912283
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
ID: 9912295
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
ID: 9912315
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
ID: 9912321
Sorry, guess I need to hit Refresh more often before I post. =)
0
 

Author Comment

by:devan_alagar
ID: 9912459
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
ID: 9912514
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 9912560
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
ID: 9912587
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
ID: 9912621
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
ID: 9919070
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
ID: 9919963
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
ID: 9933168
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
ID: 9948546
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

863 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

20 Experts available now in Live!

Get 1:1 Help Now