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!!!

[MMcCarthy]

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!!!

In Access the SQL would be

Format([startdate],"mm/dd/yyyy")

Mary

[jaishu]

Hi,
Thanks for ur advise,

i gave this in my code,
MEMBER_END_DATE1 = Format([MEMBER_END_DATE], "mm/dd/yyyy")

but it gives me this error:
Run time error 6: Overflow..

Member_end_date has value 19960101 ,but i get this error how to get around this, pls help!

[MMcCarthy]

Hi,
Thanks for ur advise,

i gave this in my code,
MEMBER_END_DATE1 = Format([MEMBER_END_DATE], "mm/dd/yyyy")

but it gives me this error:
Run time error 6: Overflow..

Member_end_date has value 19960101 ,but i get this error how to get around this, pls help!

Try this ...

 
MEMBER_END_DATE1 = Format(CDate([MEMBER_END_DATE]), "mm/dd/yyyy")