How to format a field on a form as date ?

[jaishu]

I am using ODBC with oracle tables..

My table has columns startdate and end date which are of type varchar(backend tables)..but when i display it in form it should display it as mm/dd/yyyy format, i tried setting format property as Short date but that doesnt work as my form is based on a query.and all the more my table stores the date as yyyymmdd -19990110, this is the way its stored in table (in varchar) so how do i do this?

i also tried "to_date(substr(startdate,5,4),'mmdd')" --putting this in the query based on which the form loads but this doesnt work even..
i cannot run this query from access as it says undefined function to_date , but when i run this in Sqlplus it gives me the output but i m not able to get the year part...

So how do get the date field in form as mm/dd/yyyy?

Thanks very much in advance!!!

 

[petehilljnr]

Something like this might work:

Function get_date(strInput As String) As Date

Dim year_part As Integer
Dim month_part As Integer
Dim day_part As Integer

year_part = Val(Left(strInput, 4))
month_part = Val(Mid(strInput, 5, 2))
day_part = Val(Right(strInput, 2))

get_date = DateValue(month_part & "/" & day_part & "/" & year_part)

End Function

It might depend on the Regional Settings on the computer though ...

Regards,
Pete

 

[raskew]

Hi -

The dateserial() function should get it. Here's an example from the debug (immediate) window:

x = 19990110
y = dateserial(left(x,4), mid(x,5,2), right(x, 2))
? y
1/10/99 
to show that y is in data/time data type.
? cdbl(y)
 36170

HTH - Bob

 

[jaishu]

i used what raskew had suggested...
It works fine, but it gives type mismatch errors when i keep navigating thro records..i really dont know how to fix this problem since i tried declaring all the variables i use with almmost all datatypes that i can..but no use..

CAN U HELP ME FURTHER???????
This is the code that i put in bcos its based on query , i put this on form_current event where in if i keep clicking on next records it shudnt be a problem, this works fine but what i face is type mismatch, sometimes the string becomes empty for no reason.

Private Sub Form_Current()
Dim Y As String ' ---------------date, and other datatypes doesnt help even
Dim Y1 As String

' Trying if this atleast helps...
Me!MEMBER_START_DATE.Format = "Short Date"
Forms!FRM_PHS_ENTITY_MATCH!MEMBER_START_DATE.Format = "mm/dd/yyyy"

Me.MEMBER_START_DATE.SetFocus
X = MEMBER_START_DATE
On Error GoTo Continue1
Y = DateSerial(Left(X, 4), Mid(X, 5, 2), Right(X, 2))
MEMBER_START_DATE = Y

Continue1:
If MEMBER_END_DATE <> "" Then

Me.MEMBER_END_DATE.SetFocus
X1 = MEMBER_END_DATE
On Error GoTo Continue2
Y1 = DateSerial(Left(X1, 4), Mid(X1, 5, 2), Right(X1, 2))
MEMBER_END_DATE = Y1
Else
GoTo Continue2
End If


Continue2:
Me.Text34.SetFocus


End Sub