By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,557 Members | 1,406 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,557 IT Pros & Developers. It's quick & easy.

How to format a field on a form as date ?

P: 22
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!!!
Mar 15 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. Format([startdate],"mm/dd/yyyy")
Mary
Mar 17 '07 #2

P: 22
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!
Mar 19 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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 ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. MEMBER_END_DATE1 = Format(CDate([MEMBER_END_DATE]), "mm/dd/yyyy")
  3.  
Mar 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.