?
Solved

display date as dd/mm/yyyy irrespective of regional settings

Posted on 2005-04-15
37
Medium Priority
?
1,627 Views
Last Modified: 2012-06-21
hi

I need to write a VB function which takes a date and displays it as dd/mm/yyyy . The input date can be in ANY format such as dd-MON-yyyy, mm/dd/yyyy ... but the output string should always be dd/mm/yyyy irrespective of the input format and the regional settings.

How can I do this ?
0
Comment
Question by:rajesh_khater
  • 10
  • 6
  • 6
  • +8
36 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 13789516
Try:

    Dim dDate As Date
    Dim theDate As String

    dDate = CDate(yourDate)
    theDate = DatePart("d", dDate) & "/" & DatePart("m", dDate) & "/" & DatePart("yyyy", dDate)


Hope this helps.

     
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13789542
Much easier

Private Function GetDate(d As Date) As String
    GetDate = Format(d, "dd/mm/yyyy")
End Function

0
 
LVL 7

Expert Comment

by:Smallint
ID: 13789612
To be exact...

Private Function GetDate(d As Date) As String
    GetDate = Format(d, "dd\/mm\/yyyy")
End Function

Date separator depends on regional settings.

Cheers
0
Independent Software Vendors: 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 26

Expert Comment

by:EDDYKT
ID: 13789725
Have you thought about to use DT picker?
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 13789805
[This comment is for information not for points]

This is not quite that easy, because if you get the string "03/04/05", and you don't know what the input locale was, how can you tell what is day, month and year? It could be 3 April 2005, 4 March 2005 etc.

Similarly month names in different languages are different and only your locale's specific language is recognised by CDate.

This is why non-locale based date formats exist like ISO 8601. W3C have the following notes on the subject:
http://www.w3.org/TR/NOTE-datetime

I personally use these formats for writing dates in XML or CSV (text) files. Otherwise, convert to a date/time using the client locale on input and only ever convert back to a string for display on the display-user's locale. Internally and for transfer the date/time should always be held as a date/time datatype.
0
 
LVL 19

Expert Comment

by:Shauli
ID: 13790034
Private Sub Command1_Click()
MsgBox MakeEUDate("Apr-15-2005")
End Sub

Public Function MakeEUDate(ByVal fnDate As Date) As String
Dim myDate As Date
'first, bring all dates to US format, to bypass the locale, using datevakue function.
myDate = DateValue(fnDate)
'then you can format it to EU date and get the result as string.
MakeEUDate = Format(myDate, "dd/mm/yyyy")
End Function

S
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 13790947
Shauli, that code won't work in a locale which uses a non-US locale

>>'first, bring all dates to US format, to bypass the locale, using datevakue function.
Datevalue does not bypass the locale.

    MakeEUDate("04-05-2005")

on my machine (English - United Kingdom Loocale) returns "04/05/2005"

    MakeEUDate("04-15-2005")

returns "15/04/2005".
0
 
LVL 22

Expert Comment

by:danaseaman
ID: 13792977
If the date is generated from your machine you can use the code below, otherwise you need to supply the correct date order. Don't expect it to know month names other than your locale though.

'Demo to force dd/mm/yyyy:

Private Sub Form_Load()
   Dim sDate As String
   sDate = FormatDateTime(Now, vbShortDate)
   MsgBox Format$(Text2Date(sDate), "dd/mm/yyyy")
End Sub

'Module Code
Option Explicit

Private Const McstrValidSeparators As String = "/. ,~_-"

Public Enum DateOrderEnum
   doDefault 'Your locale setting
   doMDY     'Month-Day-Year (U.S.)
   doDMY     'Day-Month-Year (EU, S.A.)
   doYMD     'Year-Month-Day (Japan)
End Enum
#If False Then  'PreserveEnumCase
   Private doMDY, doDMY, doYMD
#End If

Public Function Text2Date(ByVal pStrText As String, Optional datOrder As DateOrderEnum = doDefault) As Date
   Dim LlngIndex        As Long
   Dim sSplit()         As String
   Dim iMo              As Integer
   Dim iYr              As Integer
   Dim iDy              As Integer
   Dim MstrDateSeparator As String
   
   On Error GoTo ErrorHandler

   MstrDateSeparator = "/"
   'Force separator to be "/"
   For LlngIndex = 1 To Len(pStrText)
      If Mid$(pStrText, LlngIndex, 1) Like "[" & McstrValidSeparators & "]" Then
         Mid$(pStrText, LlngIndex, 1) = MstrDateSeparator
      End If
   Next
   
   'Get the parts
   sSplit = Split(pStrText, MstrDateSeparator)
   
   If datOrder = doDefault Then
      Text2Date = CDate(pStrText)
   Else 'Calculate integer components
      Select Case datOrder
         Case doDMY
            iDy = CInt(sSplit(0))
            iMo = ResolveMonth(sSplit(1), doDMY)
            iYr = CInt(sSplit(2))
         Case doMDY
            iMo = ResolveMonth(sSplit(0), doMDY)
            iDy = CInt(sSplit(1))
            iYr = CInt(sSplit(2))
         Case doYMD
            iYr = CInt(sSplit(0))
            iMo = ResolveMonth(sSplit(1), doYMD)
            iDy = CInt(sSplit(2))
      End Select
     
      'Assemble the parts
      Text2Date = DateSerial(iYr, iMo, iDy)
   End If

   Exit Function
ErrorHandler:
   With Err
      .Raise .Number, .Source, .Description, .HelpFile, .HelpContext
   End With

End Function

Private Function ResolveMonth(ByVal sMo As String, lEntryOrder As DateOrderEnum) As Integer
   Dim sDat             As String
   If IsNumeric(sMo) Then
      ResolveMonth = CInt(sMo)
   Else
      Select Case lEntryOrder
         Case doDMY
            sDat = "13/" & sMo & "/2000"
         Case doMDY
            sDat = sMo & "/13/2000"
         Case doYMD
            sDat = "2000/" & sMo & "/13"
      End Select
      ResolveMonth = DatePart("m", CDate(sDat))
   End If
End Function


0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13797794
You can use select case to convert it into month names ie :
Dim Month As Date
Month = Month(Now)
Dim strMonth As String

Select Case Month
Case 1:
strMonth = "January"
Case 2:
strMonth = "Febuary"

etc etc

all the way to

Case 12:
strMonth = "December"
End Select

Something like that :)
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 13799654
I've been through all these date problems, particularly with sqlserver that only stores dates in us format. What a surprise.

Now, I ALWAYS use a medium date format for display & input.  ie dd/mmm/yyyy    04/Feb/2005
no way of getting that mixed up and the DateValue works every time.

or you must specify the input format. I just gets to difficult to please everyone, wherever they live or what their background.

lets not get started on AM/PM, 24hr time, Zulu time etc.

0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 13805770
>> I've been through all these date problems, particularly with sqlserver that only stores dates in us format. What a surprise.
SQL server does not use any locale to save date-times. It stores them as real numbers. The Integer part is the date in days from a zero date (I forget what the zero date is) and the time part is the decimal (as a proportion of 24 hours).

To enter dates safely in SQL Server you should use the international date format (YYYY-MM-DD) or Date Times (YYYY-MM-DD HH:MM:SS). This works whatever the locale is set to on the SQL server machine and is well documented .

>> Now, I ALWAYS use a medium date format for display & input.  ie dd/mmm/yyyy    04/Feb/2005
This will not necessarily work if you store a date in a string on machine with a different language locale setting.

e.g. 27 August 1970 on my English UK machine stored with dd/mmm/yyyy format is 27/Aug/2005. On a machine with a Malay Locale the same format will give 27/Ogo/1970.

That is why international date formatting standards (for storing/passing dates in strings) exist and are in general use. There is no need to attempt to reinvent this stuff.

Please see my earlier post on the International Standards Organisation ISO 8601 and its adoption by W3C.

0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13805932
This is just a thought, not sure if it has been suggested or not but couldnt you have 2 or more functions ??

One to detect regional settings and pass a parameter to the 2nd function

second function to know what regional setting it is on and convert the time / date based on the parameter passed to it and from there you can output the date / time as required ?

For example :

Public Function One()
'code to detect regional settings
Then pass detected region as a value ie , 1 to represent the USA, 2 for the U.K, etc etc which then gets passed to the second function
End Function

Public Function Two()
Select Case rgRegion
Case 1:
'Code here to convert from USA time / date zone to U.K time / date zone
Case 2:
'Code here to convert from U.K time / date zone to USA time / date zone
End Select
End Function

Something along those lines maybe ? I am not sure of exact coding
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 13806967
gecko_au2003

It would work, but it would require a lot of coding. The easier option would be:

Function one - run on machine 1 locale
Convert locale format date to international format date.

Function 2 run on machine 2  locale
Convert international format date to locale datetime.

Here is the code that I use to do this

Public Function VBDate2XMLDate(ByVal VBDate As Date) As String
'***************************************************************************************
'
' Description : VBDate2XMLDate
'               Converts a VB Date data type to a W3C DateTime format
'               The W3C Date format is based on ISO 8601.
'
'
' Parameters :
'
'   VBDate      The date to format
'
' Return Values :
'
'   String      The formatted datetime
'
' Change History :
'
'   Created by AJD 05/04/2004
'
'***************************************************************************************

    Const strPROCEDURE As String = MstrMODULE & ".VBDate2XMLDate" ' The procedure name.
    On Error GoTo ErrorHandler

    VBDate2XMLDate = Format$(VBDate, "YYYY-MM-DD\THH:NN:SS")

    Exit Function
ErrorHandler:
'Error handler code [Generated 05/04/04 Using LCPEditToolbox Ver 1.2 build 87]
'This code block must be the last code in the procedure.
    With Err
        If .Source = App.Title Then
            .Source = App.Title & "::" & strPROCEDURE
        End If

        .Raise .Number, .Source, .Description, .HelpFile, .HelpContext
    End With

End Function

Public Function XMLDate2VBDate(ByVal xmlDate As String) As Date
'***************************************************************************************
'
' Description : XMLDate2VBDate
'               Takes a W3C datetime string and returns a VB date representation of the
'               given value.
'               The W3C Date format is based on ISO 8601.
' Parameters :
'
'   xmlDate     The string representation of the datetime to convert
'
' Return Values :
'
'   Date        The converted datetime
'
' Change History :
'
'   Created by AJD 05/04/2004
'
'***************************************************************************************

    Dim strDateTime() As String
    Dim strDateParts() As String
    Dim strTimeParts() As String

    Const strPROCEDURE As String = MstrMODULE & ".XMLDate2VBDate" ' The procedure name.
    On Error GoTo ErrorHandler

    strDateTime = Split(xmlDate, "T")
    strDateParts = Split(strDateTime(0), "-")
    strTimeParts = Split(strDateTime(1), ":")

    XMLDate2VBDate = DateSerial(CInt(strDateParts(0)), _
                     CInt(strDateParts(1)), CInt(strDateParts(2))) + _
                     TimeSerial(CInt(strTimeParts(0)), _
                     CInt(strTimeParts(1)), _
                     CInt(strTimeParts(2)))

    Exit Function
ErrorHandler:
'Error handler code [Generated 05/04/04 Using LCPEditToolbox Ver 1.2 build 87]
'This code block must be the last code in the procedure.
    With Err
        If .Source = App.Title Then
            .Source = App.Title & "::" & strPROCEDURE
        End If

        .Raise .Number, .Source, .Description, .HelpFile, .HelpContext
    End With

End Function
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13807020
That is pretty much what I was reffering to except I didnt know what the coding would be obviously, otherwise I would of posted it :)
0
 
LVL 6

Expert Comment

by:GPrentice00
ID: 14394005
I am not recommending a reward to any particular expert, because of the nature of this problem - everyone jumping in with code that may or may not work on various systems, while in fact, there is no possible solution in general -- without some constraint on the INPUT, there is no way of determining the intent of the input.  Problem/Question definition is flawed fundamentally and does not permit there to be 'an answer'

However, all the experts did do a good job of providing various date code ideas, and raised good points that future searchers should consider, this question is definitely rich in information relative to what needs to be considered.
0
 
LVL 6

Expert Comment

by:GPrentice00
ID: 14394029
although the dt picker idea is the most valid for the input, but doesn't address the foolishness of the concept of the any-input format...
As for the constant output of the fixed format, that easy part is covered here.

So, fight for the points if you must
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14402802
Hi GPrentice00

You said: "in fact, there is no possible solution in general -- without some constraint on the INPUT, there is no way of determining the intent of the input.  Problem/Question definition is flawed fundamentally and does not permit there to be 'an answer'"

My understanding of date data types was that internally they are stored as numbers, at least in VB. So no matter using whichever input locale the date was entered, it will be stored as a number representing the number of milliseconds elapsed since midnight Jan 1, 0000 (or some other base date)

I am assuming that the date is entered and passed to the VB function on the same machine. The input locale for the date will be determined by the user's regional settings. So if the user enters a date like 3/4/2005, it will mean either 3-Apr-2005 or 4-Mar-2005 depending on regional settings.

But the VB function need not be bothered about whether to interpret 3/4/2005 as 3-Apr-2005 or 4-Mar-2005 , coz it will receive a date value which will represent no. of milliseconds elapsed. Internal representation of 3/4/2005 will be distinct and will leave no scope for ambiguity.

So theoretically, it should be able to construct an output string in any desired format.

Correct me if I am wrong.

Thanks,
Rajesh.

0
 
LVL 22

Expert Comment

by:danaseaman
ID: 14402835
I think that the question was not clearly stated: "The input date can be in ANY format such as dd-MON-yyyy, mm/dd/yyyy". Here we assumed that you were in fact suppplying us with a string. For this case there is no guaranteed solution without knowing the sequence of d, m, y. If in fact your input is a VbDate all you need to do is use Format$(myDate, "dd/mm/yyyy"). A VbDate is nothing more than a VbDouble in disguise and can be transported across international boundries with NO errors.
0
 
LVL 6

Expert Comment

by:GPrentice00
ID: 14403909
Given this new information then, there is only one answer which really accurately comes close, and that is the very first answer, from
 carl_tawn
who breaks the date down into the undisputed portions and puts the slashes in as string components.

The format() functions with the slahes - they are not guaranteed to work - it is possible to have the reginonal settings configured without a slash-based display format of this type, in which case VB says to the system "It seems to be date-type-short-2" or something like that, which the system will then say "Well, date-type-short-2 in my records is actually MM.DD.YYYY" so here you go"

I spent way tooo long on that bug long ago, I speak from hair-pulling experience

0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14406754
Using Format$ or DatePart() may not give accurate results.

The problem lies with CDate.

If you use a textbox for input, you have to convert the String to Date.
But CDate converts to date not necessarily based on regional settings. It will convert to date any string value which is valid as a date in any acceptable format.

For example, if you have dd/MM/yyyy in your regional settings and you enter 11/22/2005 in a text box,
the date should be invalid as per dd/MM/yyyy.

But CDate will still convert it and interpret the string as mm/dd/yyyy.
Moreoever, if you enter 2005/11/15, it will interpret it as yyyy/mm/dd

But surprisingly, if you enter 2005/15/11, CDate DOES NOT interpret it as yyyy/mm/dd. It gives a Type mismatch error.

I think CDate is the culprit here, not Format or DatePart coz CDate interprets date in the wrong format in the first place !

Once the date is stored wrongly by CDate, any string conversion function are likely to give an incorrect result !



0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14406757
Oops..

Read my statement:
"But surprisingly, if you enter 2005/15/11, CDate DOES NOT interpret it as yyyy/mm/dd. It gives a Type mismatch error."

as

"But surprisingly, if you enter 2005/15/11, CDate DOES NOT interpret it as yyyy/dd/mm. It gives a Type mismatch error."

0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14406772
VB documentation says:

CDate recognizes date formats according to the locale setting of your system. The correct order of day, month, and year may not be determined if it is provided in a format other than one of the recognized date settings. In addition, a long date format is not recognized if it also contains the day-of-the-week string.

Isnt there a method to convert a string to a date conrrectly and validly, in the first place?
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14406781
If you use date literals for testing purpose, you come across another quirk:

msgbox Format(#1/11/2005#, "dd\/MM\/yyyy")

In date literals, date is ALWAYS interpreted as mm/dd/yyyy, even if your locale setting is dd/mm/yyyy

The above code will display 11/01/2005
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14406783
Increasing points
0
 
LVL 22

Expert Comment

by:danaseaman
ID: 14406797
There is NO guaranteed way to do this without knowing the date order. "04" could be Year 2004, Month of April, or 4th day of month. The code I posted above Public Function Text2Date(ByVal pStrText As String, Optional datOrder As DateOrderEnum = doDefault) As Date assumes you know the order or you can let it use doDefault which assumes the user is typing in using the same order he has set in regional Configurations. Providing Dates via a string should never be used because of this issue. Use a DtPicker and you will get correct results.
0
 
LVL 6

Expert Comment

by:GPrentice00
ID: 14406961
This is exactly the point - you can not convert a STRING input without having SOME CONSTRAINT on the input.

You appeared to toss to the side the problem of the input in your first most recent post, accepting that you have a date variable in your system now somehow, and then drew the focus to:

"theoretically, it should be able to construct an output string in any desired format."

If you have SOMEHOW managed to get a date variable populated correctly (using a date-picker, constructing it with the DateSerial() function, adding time values with DateAdd(), or importing from a database date field -- then you have an internally stored date, and now you can indeed theoretically and PRACTICALLY output the format in any method you want - even if you want the output format to be

MM(DDDD)@YYYY

by formatting each individual date component ( datepart() commands ..) and constructing a string with them.

---

If CDate() isn't doing the job you expect on the system, interpreting the string the user enters "because the user knows what locale they're in and will use the right one" then thats your simplest case scenario of what SHOULD work properly - and thus proof you really should focus on getting that date some other way, with some internalized constraints put on it - such as the datepicker provides.

But worth bearing in mind as well, even if you think you could have it working -- users don't always match the user region.  If I were to move across the world, and immediately buy a computer, and not change the region settings, my brain would probably still try entering the date in a format that makes sense to my experience, and I'd forget that the mm and dd parts are 'switched' in the new locale relative to my previous locale.

if cdate(text) is NOT doing the job for you to convert to the date--- then nothing will, because all the other system options are based on that same technology, and something is fundamentally wrong with your string representation as far as the system is concerned.  

And, even when you think you have it working, try doing a loop from 1 to 13 on the date conversion on the month parameter and hold day constant at 5.
You can run into situations where the computer will automatically SWITCH instead of tossing an error, and the pattern goes
Jan 5
Feb 5
Mar 5
Apr 5
May 5
Jun 5
Jul 5
Aug 5
Sep 5
Oct 5
Nov 5
Dec 5
May 13

Even though it should have crapped out, sometimes date conversion with string formats that you are trying to force them to match will flip around like this.  

0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 14408175
>>If you use date literals for testing purpose, you come across another quirk:...
>>msgbox Format(#1/11/2005#, "dd\/MM\/yyyy")
Have you read the VB documentation? This is exactly as it meant to work.


>>Increasing points

There's no point. The question is impossible. You cannot allow your user to enter anything and then just know what they mean. This would require a mind reading computer. When you have one of these, you won't need a textbox any more.

There are ways of transferring dates as strings across locales (above I pointed to the internationally recognised format used by the International Standards Organisation and supported as XML Standard (and very similar to the one supported by SQL Server). But all of them require the date to be laid out in a particular way. It doesn't really matter what date format you use when it comes down to it as long as two parties communicating agree on the format to be used. It is in effect partof the contract which is your communication protocol.

If you have VB on both ends, you could just send the VB date as a numeric string <code>Cstr(Cdbl(MyDate))</code> as someone  else said up there ^.
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14411215
fds_fatboy:
"There's no point. The question is impossible. You cannot allow your user to enter anything and then just know what they mean. This would require a mind reading computer. When you have one of these, you won't need a textbox any more."

GPrentice00:
"This is exactly the point - you can not convert a STRING input without having SOME CONSTRAINT on the input."

Let me ask you the question another way .. How can you make sure that the date value user enters in a textbox is a valid date, as per dd/MM/yyyy

I DONT want to use a DatePicker, so please rule that out in any future posts.

danaseaman:
"The code I posted above Public Function Text2Date(ByVal pStrText As String, Optional datOrder As DateOrderEnum = doDefault) As Date assumes you know the order or you can let it use doDefault which assumes the user is typing in using the same order he has set in regional Configurations"

Even if the user is typing the date using the same order he has set in Regional settings, and you call your function with datOrder = doDefault, the code is NOT guaranteed to work, if the user makes a typing mistake.

Suppose the user has set dd/MM/yyyy in regional settings, and he is typing in that order only, but makes a typing mistake and types: 11/13/2005 instead of say, 11/3/2005. In your code, the code will not give an error but instead interpret the date as 13-Nov-2005. This is the problem with CDate.

I want to know how to write a function which can be made to do the work of CDate more reliably.

I am talking of a function with the signature:
       Public Function StringToDate(value as String, format as String) as Date

The function should raise an error if value is not a correct date value in the specified format.
This function should work irrespective of regional settings, coz the format of the date is passed as a parameter to the function.

So my question is: HOW to write the above function StringToDate ?

Now, you might think that I am asking a question DIFFERENT from my original question. But this question has come up in the discussion of original question only. Because I realized that the problem is not with the function that converts a Date to String using any method like Format() or DatePart().

The problem lies in the function that converts the user input string to a Date variable in the first place. Because VB's CDate() is unreliable !!

If you are getting input from the user , he will enter a string value only, which has to be converted to a Date data type by our program. So my seemingly NEW question is related to the original question, because unless we have a function that converts a string to a date validly, we cannot talk of a function that converts the date variable to a string in ANY desired format. There is no way to test the 2nd function (DateToString) with user-given input unless we have the first function (StringToDate)

I HOPE that I have been able to make my point clear.

0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14411323
danaseaman:

Your code will not work for the following values:
     Msgbox Format(Text2Date("11/13/2005", doDMY), "dd\/MM\/yyyy")

Instead of giving error, the output is: 11/01/2006
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14411383
GPrentice00:
"Even though it should have crapped out, sometimes date conversion with string formats that you are trying to force them to match will flip around like this. "

This is EXACTLY the problem and my question. How can you stop this flipping around of month and day component ? There is no-inbuilt way in VB to do this. But surely there must be a logic to write a function which does not flip mm and dd, but will give an error if the value is not within a valid range.
0
 
LVL 22

Expert Comment

by:danaseaman
ID: 14411556
Your code will not work for the following values:
     Msgbox Format(Text2Date("11/13/2005", doDMY), "dd\/MM\/yyyy")

Instead of giving error, the output is: 11/01/2006

13 Months will wrap to January of the next year so the Function works as designed.

Of course the function could be modified to throw an error under following circumstances:
   1. Month <1 or >12
   2. Day <1 or >LastDayofMonth
   3. Year <100 or >9999  

If that is the case then perhaps the VbDate that is to be returned will now become a ByRef Parameter and the Function will return a Boolean to indicate if the conversion was a success or not.

Again the Function relies on you providing the date order and from previous posts there is NO possible solution for knowing if 04/01 is "April 01" or "04 Jan" without this information.
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 14411622
>>Let me ask you the question another way .. How can you make sure that the date value user enters in a textbox is a valid date, as per dd/MM/yyyy


Why don't you use 3 textbox so that you can limit user to enter value

or use maskeditbox
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 14412266
danaseaman:
"Again the Function relies on you providing the date order and from previous posts there is NO possible solution for knowing if 04/01 is "April 01" or "04 Jan" without this information."

You havent read my post carefully.
The function signature accepts the date order as a parameter:
    Public Function StringToDate(value as String, format as String) as Date
0
 
LVL 22

Accepted Solution

by:
danaseaman earned 1500 total points
ID: 14413081
I think this revised function will do what you requested. Given a user supplied date string and format string return a VbDate. In addition if bValidate is True(by default)then function returns True if user input is legal or False if not. bValidate will allow you to flag dates like "11/13/2005" ("dd/mm/yyyy") as invalid

'In Form
'-------
Option Explicit

Private Sub Form_Load()
   Dim MyDate As Date
   Debug.Print StringToDate("31-12-2002", "dd-mm-yyyy", MyDate), MyDate
   Debug.Print StringToDate("32-12-2002", "dd/\mm/\yyyy", MyDate), MyDate
   Debug.Print StringToDate("4-1-2002", "dd-mm-yyyy", MyDate), MyDate
   Debug.Print StringToDate("4-1-2002", "mm-dd-yyyy", MyDate), MyDate
   Debug.Print StringToDate("1-4-2002", "mm/dd/yyyy", MyDate), MyDate
   Debug.Print StringToDate("1-4-2002", "dd.mm.yyyy", MyDate), MyDate
End Sub

'In Module
'---------
Option Explicit

Public Function StringToDate(ByVal pStrText As String, _
   ByVal sFormat As String, ByRef NewDate As Date, _
   Optional ByVal bValidate As Boolean = True) As Boolean

   Dim datOrder            As Long
   Dim Index               As Long
   Dim sSplit()            As String
   Dim iMo                 As Integer
   Dim iYr                 As Integer
   Dim iDy                 As Integer
   Dim i                   As Long
   Dim MstrDateSeparator   As String
   Const McstrValidSeparators As String = "\/. ,~_-"
   
   On Error GoTo ErrorHandler

   MstrDateSeparator = "/"
   'Force separator to be "/"
   For Index = 1 To Len(sFormat)
      If Mid$(sFormat, Index, 1) Like "[" & McstrValidSeparators & "]" Then
         Mid$(sFormat, Index, 1) = MstrDateSeparator
      End If
   Next

   'datOrder = GetDateOrder(sFormat)
   sFormat = LCase$(sFormat)
   For i = 1 To Len(sFormat)
      Select Case Mid$(sFormat, i, 1)
         Case "d"
            datOrder = 1
            Exit For
         Case "m"
            datOrder = 2
            Exit For
         Case "y"
            datOrder = 3
            Exit For
      End Select
   Next

   MstrDateSeparator = "/"
   'Force separator to be "/"
   For Index = 1 To Len(pStrText)
      If Mid$(pStrText, Index, 1) Like "[" & McstrValidSeparators & "]" Then
         Mid$(pStrText, Index, 1) = MstrDateSeparator
      End If
   Next

   'Get the parts
   sSplit = Split(pStrText, MstrDateSeparator)

      Select Case datOrder
         Case 1
            iDy = CInt(sSplit(0))
            iMo = CInt(sSplit(1))
            iYr = CInt(sSplit(2))
         Case 2
            iMo = CInt(sSplit(0))
            iDy = CInt(sSplit(1))
            iYr = CInt(sSplit(2))
         Case 3
            iYr = CInt(sSplit(0))
            iMo = CInt(sSplit(1))
            iDy = CInt(sSplit(2))
      End Select

      'Assemble the parts
      NewDate = DateSerial(iYr, iMo, iDy)
      'See if they are valid inputs
      If bValidate Then 'Are elements within bounds
         If (iYr >= 100 And iYr <= 9999) Then
            If (iMo > 0 And iMo < 13) Then
               If (iDy > 0 And iDy <= Day(DateSerial(iYr, iMo + 1, 0))) Then
                  StringToDate = True
               End If
            End If
         End If
      Else
         StringToDate = True 'No validation
      End If

   Exit Function
ErrorHandler:
   With Err
      .Raise .Number, .Source, .Description, .HelpFile, .HelpContext
   End With
   
End Function



0
 
LVL 22

Expert Comment

by:danaseaman
ID: 14448839
I revised the Function so that it easier to use and has more error checking. It now returns the VbDate. If you request validation and it is not a valid date then the function will return 0.

'In Form
Option Explicit

Private Sub Form_Load()
   'Note: If return is 0 then input was not a valid date
   Debug.Print StringToDate("31-12-2002", "dd-mm-yyyy")
   Debug.Print StringToDate("32-12-2002", "dd/\mm/\yyyy")
   Debug.Print StringToDate("4-1-2002", "dd-mm-yyyy")
   Debug.Print StringToDate("4-1-2002", "mm-dd-yyyy")
   Debug.Print StringToDate("1-4-2002", "mm/dd/yyyy")
   Debug.Print StringToDate("1-4-2002", "dd.mm.yyyy")
   
End Sub

'In Module
'Purpose: Given a string date and string format return
'a VbDate, Optionally validating the input and returning
'0 if element(s) are not in bounds.
Public Function StringToDate(ByVal pStrText As String, _
   ByVal sFormat As String, _
   Optional ByVal bValidate As Boolean = True) As Date

   Dim datOrder         As Long
   Dim Index            As Long
   Dim sSplit()         As String
   Dim iMo              As Integer
   Dim iYr              As Integer
   Dim iDy              As Integer
   Dim i                As Long
   Dim MstrDateSeparator As String
   Const McstrValidSeparators As String = "\/. ,~_-"

   On Error GoTo ErrorHandler

   MstrDateSeparator = "/"
   'Force separator to be "/"
   For Index = 1 To Len(sFormat)
      If Mid$(sFormat, Index, 1) Like "[" & McstrValidSeparators & "]" Then
         Mid$(sFormat, Index, 1) = MstrDateSeparator
      End If
   Next

   'datOrder = GetDateOrder(sFormat)
   sFormat = LCase$(sFormat)
   For i = 1 To Len(sFormat)
      Select Case Mid$(sFormat, i, 1)
         Case "d"
            datOrder = 1
            Exit For
         Case "m"
            datOrder = 2
            Exit For
         Case "y"
            datOrder = 3
            Exit For
      End Select
   Next

   MstrDateSeparator = "/"
   'Force separator to be "/"
   For Index = 1 To Len(pStrText)
      If Mid$(pStrText, Index, 1) Like "[" & McstrValidSeparators & "]" Then
         Mid$(pStrText, Index, 1) = MstrDateSeparator
      End If
   Next

   'Get the parts
   If InStr(pStrText, MstrDateSeparator) = 0 Then
      Debug.Print "Unable to split pStrText into components. Missing separator(s)."
      Exit Function
   Else
      sSplit = Split(pStrText, MstrDateSeparator)
      If UBound(sSplit) <> 2 Then
         Debug.Print "Incorrect number of components in pStrText."
         Exit Function
      End If
   End If
   'Populate iDy,iMo,iYr
   Select Case datOrder
      Case 1
         iDy = CInt(sSplit(0))
         iMo = CInt(sSplit(1))
         iYr = CInt(sSplit(2))
      Case 2
         iMo = CInt(sSplit(0))
         iDy = CInt(sSplit(1))
         iYr = CInt(sSplit(2))
      Case 3
         iYr = CInt(sSplit(0))
         iMo = CInt(sSplit(1))
         iDy = CInt(sSplit(2))
   End Select

   If bValidate Then 'Are elements within bounds
      If (iYr >= 100 And iYr <= 9999) Then
         If (iMo > 0 And iMo < 13) Then
            If (iDy > 0 And iDy <= Day(DateSerial(iYr, iMo + 1, 0))) Then
               'Return validated date
               StringToDate = DateSerial(iYr, iMo, iDy)
            End If
         End If
      End If
   Else 'No validation
      StringToDate = DateSerial(iYr, iMo, iDy)
   End If

   Exit Function
ErrorHandler:
   'With Err
   '   .Raise .Number, .Source, .Description, .HelpFile, .HelpContext
   'End With
   Debug.Print "Error in StringToDate " & Err.Description
End Function

0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 14453852
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Suggested Courses

850 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