Anyway to set DateFormat in VB application

[bugeditor]

Hi,
Iam using Access2000 and VB as frontend ..Iam getting lot of problems with date Vb application while Inserting date into database/retrieving date.

User wants in DD/MM/YYYY, format..
So,
1.I set the Date/Time field in Access as short date
2.FrontEnd(VB) DatePicker format as short date..
3. Regional Setting to DD/MM/YYYY..

Still problems..

Is there any global way to just define dataforma in Vb application so that it can be used through out life time of application..with our changing regional settings.IF so, where can i set the dateformat in VB application.




thanks

 

[salvator]

I had exectly the same problem last week. After some research I could find only one logical answer to that question. The format you use in Access actually is a mask. It doesn't matter which format you choose or what regional settings you use on your PC. Access always save the dates in one format: DD/MM/YYYY. Me and my collegue did several tests for 3 days. And that's all we could come up with...
So, I created a little function in VB that convert my dates in that format, to use in my queries.

Good luck

 

[ITphile]

Hi,

This is date conversion for DD/Mon/YYYY.

Public Function DateConversion(Data As String, SourceName As String) As String
'On Error GoTo ErrDisp
Dim X(1 To 12) As String
Dim b As String
Dim s As String
Dim a As String
Dim ret As String
X(1) = "Jan"
X(2) = "Feb"
X(3) = "Mar"
X(4) = "Apr"
X(5) = "May"
X(6) = "Jun"
X(7) = "Jul"
X(8) = "Aug"
X(9) = "Sep"
X(10) = "Oct"
X(11) = "Nov"
X(12) = "Dec"
'
If Not IsDate(Data) Then
Err.Raise 4000, SourceName, "Invalid Date"
End If
Data = Trim(Data)
If Data = "" Then
Exit Function
End If
a = InStr(1, Data, "-&quot
If a = 0 Then
a = InStr(1, Data, "/&quot
b = "/"
Else
b = "-"
End If
ret = InStr(a + 1, Data, b)
s = Mid(Data, a + 1, ret - (a + 1))
'
If val(s) > 12 Then
Err.Raise 4000, SourceName, "Invalid Date"
Exit Function
End If
For i = 1 To 12
If i = val(s) Then s = X(i)
Next i
s = Mid(Data, 1, a) & s & Mid(Data, ret)
s = Format(s, "dd-mmm-yyyy&quot
If IsDate(s) = True Then
DateConversion = s
Else
Err.Raise 4000, SourceName, "Invalid Date"
End If
Exit Function
'ErrDisp:
' MsgBox Err.Description, , "Date Validation"
End Function



HTH
All the best

 

[WilMead]

Don't confuse diaplay with storage. Dates are stored in a date value. Most date aware programs accept almost any date format.

Ouch with that code! It kind of hurt all over, my eyes and brain will eventually find a way to forgive you.
[tt]
if isdate(data) then
DateConversion = format(data, "dd-mmm-yyyy&quot
else
Err.Raise 4000, SourceName, "Invalid Date: " & data
end if
[/tt]



Wil Mead
wmead@optonline.net

 

[ITphile]

WilMead,

The code given is for text string to date conversion and takes care of the User typing / or - (any ssseparator) between the numbers in the date.

Hope you get the clarity Think!

Hi bugeditor try NOT storing the date as short date or any other foramt.

ITphile (with input from friends!)