Robert
robert at servicexp.com
Thu Oct 22 07:46:22 CDT 2009
Shamil,
Thank you very much for your "dirty code", yours "Looks" much cleaner then
my first run at it.. I will work with it.. This is what I came up with..
First Rev. still need to clean it up....
Public Function FormatCCDate(dCCDate As String) As String
Dim dLastDay As Date
Dim dDate As String
Dim sFirstPart As String
Dim sSecondPart As String
Dim sThirdPart As String
Dim dFinalDate As Date
Dim lStr As Long
On Error GoTo HandleErr
If Nz(dCCDate, "") <> "" Then
dDate = dCCDate
'Strips everything but the numbers
dDate = varNumericPart(dDate)
Select Case Len(dDate)
Case 3 '909 entries would format to 09 31 09 31 would automaticly be
entered
sFirstPart = 0 & Left(dDate, 1)
sThirdPart = Right(dDate, 2)
sSecondPart = Mid(DateSerial(Year(CDate(sFirstPart & 1 & sThirdPart)),
Month(dDate) + 1, 0), 3, 2)
dFinalDate = Format(sFirstPart & "/" & sSecondPart & "/" & sThirdPart,
"Short Date")
Case 4 '0909 entries would format to 09 31 09 31 would automaticly be
entered
sFirstPart = Left(dDate, 2)
sThirdPart = Right(dDate, 2)
sSecondPart = Mid(DateSerial(Year(CDate(sFirstPart & 1 & sThirdPart)),
Month(dDate) + 1, 0), 3, 2)
dFinalDate = Format(sFirstPart & "/" & sSecondPart & "/" & sThirdPart,
"Short Date")
Case 5 '92109 entries would format to 09 21 09
sFirstPart = 0 & Left(dDate, 1)
sThirdPart = Right(dDate, 2)
sSecondPart = Mid(dDate, 2, 2)
dFinalDate = Format(sFirstPart & "/" & sSecondPart & "/" & sThirdPart,
"Short Date")
Case 6 '092109 entries would format to 09 21 09
sFirstPart = Left(dDate, 2)
sThirdPart = Right(dDate, 2)
sSecondPart = Mid(dDate, 3, 2)
dFinalDate = Format(sFirstPart & "/" & sSecondPart & "/" & sThirdPart,
"Short Date")
Case 7 '9212009 entries would format to 09 21 2009
sFirstPart = 0 & Left(dDate, 1)
sThirdPart = Right(dDate, 4)
sSecondPart = Mid(dDate, 2, 2)
dFinalDate = Format(sFirstPart & "/" & sSecondPart & "/" & sThirdPart,
"Short Date")
Case 8 '09212009 entries would format to 09 21 2009
sFirstPart = Left(dDate, 2)
sThirdPart = Right(dDate, 4)
sSecondPart = Mid(dDate, 3, 2)
dFinalDate = Format(sFirstPart & "/" & sSecondPart & "/" & sThirdPart,
"Short Date")
Case Else
dFinalDate = Empty
End Select
If dFinalDate = "12:00:00 AM" Then
MsgBox " Sorry, Could not determine date formatting. Please enter a valid
date ", , "Problem Box"
Else
FormatCCDate = dFinalDate
End If
End If
ExitHere:
On Error Resume Next
Exit Function
HandleErr:
Select Case Err.Number
Case Else
'MsgBox Err.BuildError("basServiceOrder:FormatCCDate"), vbCritical,
"Un-Expected Error"
MsgBox "There has been an error in Procedure:
basServiceOrder:FormatCCDate " & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & "Error Description: " &
Err.Description & " On Line: " & Erl() & vbCrLf & _
" Please Contact " & "The SoftwareVendor" & " for more help
regarding this error. ", vbCritical, "Un-Expected Error"
Call ErrorRecordSystem(Err.Number, Err.Description & " On Line: " &
Erl(), Now, "Un-Expected Error In Proc; " & "basServiceOrder:FormatCCDate",
CurrentUser, Err)
Resume ExitHere
End Select
End Function
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Thursday, October 22, 2009 5:58 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] All-In-One Date format Function:
Hi Max,
No, I didn't.
Did I note that was a "quick & dirty" code snippet, didn't I? (Somebody
might even find it too "dirty"...)
Please feel free to make fixes to cover your test cases as well as null or
empty or excessively long input string as well as ...
Your turn.
Thank you.
--
Shamil
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Thursday, October 22, 2009 11:01 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] All-In-One Date format Function:
What about?
1. 9 <enter> - month 9 same year
2. 99 <enter> - month 9 year 2009.
3. 999 <enter> - day 9, month 9, year 2009
Have you put these through the code and see what they come out with?
Max
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: 21 October 2009 23:47
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] All-In-One Date format Function:
Hi Robert,
Just a quick & dirty code snippet - it's C# but simulates VBA as close as
possible:
using System;
using VBA = Microsoft.VisualBasic.Strings;
namespace TestConsole.App
{
public class DateConvertor
{
public static DateTime Convert(string dateStr)
{
string temp = dateStr;
string monthStr;
string dayStr;
string yearStr;
DateTime retValue = DateTime.MinValue;
if (VBA.Left(temp, 1) == "1" ||
VBA.Left(temp, 1) == "0")
{
// two digits monthNum
monthStr = VBA.Left(temp, 2);
temp = VBA.Mid(temp, 3);
}
else
{
// one digits monthNum
monthStr = VBA.Left(temp, 1);
temp = VBA.Mid(temp, 2);
}
if (VBA.Len(temp) < 2)
throw new ApplicationException("Invalid date format: " +
dateStr);
else if (VBA.Len(temp) == 2)
{
temp = "00" + temp;
}
else if (
(VBA.Left(temp, 1) == "/") ||
(VBA.Left(temp, 1) == "-")
)
{
temp = VBA.Mid(temp, 2);
}
dayStr = VBA.Left(temp, 2);
temp = VBA.Mid(temp, 3);
if (
(VBA.Left(temp, 1) == "/") ||
(VBA.Left(temp, 1) == "-")
)
temp = VBA.Mid(temp, 2);
if (VBA.Len(temp) == 1) temp = "200" + temp;
else if (VBA.Len(temp) == 2) temp = "20" + temp;
yearStr = temp;
retValue = (dayStr != "00") ?
Microsoft.VisualBasic.DateAndTime.DateSerial(
Int32.Parse(yearStr),
Int32.Parse(monthStr),
Int32.Parse(dayStr)) :
Microsoft.VisualBasic.DateAndTime.DateSerial(
Int32.Parse(yearStr),
Int32.Parse(monthStr) + 1,
0);
return retValue;
}
public static string[] TestDates
{
get
{
return
new string[]
{
"909",
"0909",
"09/21/09",
"09-21-09",
"092109",
"92109",
"9212009"
};
}
}
public static void Test()
{
Console.WriteLine("// Test Output:");
Console.WriteLine("// ------------");
foreach (string date in TestDates)
{
Console.WriteLine("// {0} => {1:MM/dd/yyyy}", date,
Convert(date));
}
// Test Output:
// ------------
// 909 => 09/30/2009
// 0909 => 09/30/2009
// 09/21/09 => 09/21/2009
// 09-21-09 => 09/21/2009
// 092109 => 09/21/2009
// 92109 => 09/21/2009
// 9212009 => 09/21/2009
}
}
}
HTH.
--
Shamil
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert
Sent: Wednesday, October 21, 2009 10:17 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] All-In-One Date format Function:
Hoooooowdy,
Before re-inventing the wheel here, does anyone have or know of a function
that can handle "free form" date formatting. I'm looking for a function that
can parse any input and then format is correctly.
Example of user inputs:
0909
09/21/09
09-21-09
092109
92109
9212009
etc.
I don't want to use any formatting / Masking settings at the field or table
level. I would like the user to enter it the way they want to (to the most
degree possible)...
WBR
Robert
<<< snip >>>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com