(Format Func )in DAO how to manage in ADO 1

[WFadloun]

hello all
I am converting my connection from DAO to ADO .
in DAO I used to use format but in ADO it doesn't work.
could you tell me what is the correct function in ADO

sample I am using in DAO
set rst=dbs.open(select format(""dd/mm/yyyy"",Hirdate from) from employee

in ADO it gives error message that format is not recognized function name

thanks in ADVANCE

 

[CCLINT]

Works for me...usually means some JET dlls are not all of the right versions.

How are you connecting - using the JET provider, or through ODBC?

Why don't you just format the values locally?

 

[WFadloun]

Hi CClINT
THANK for your reply

I am connecting to SQL server7 (this is what push me to ADO)
my code basis is :
'----------------------
dim Conn as adodb.connection
Dim Rst as adodb.recordset
Dim ConnStr as string

ConnStr="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Data;Data Source=server"

set conn =New Adodb.connection
conn.open ConnStr
Set RST = New adodb.Recordset
RST.ActiveConnection = dbs
RST.CursorLocation = adUseClient
RST.CursorType = adOpenDynamic
RST.LockType = adLockBatchOptimistic
rst.open "Select format(""dd/mm/yyyy"",HireDate) from employee;"
'-----------------------

regarding your point about formating this field locally it will not work in my case coz this field is changed up to user select (sometime it could be number ,currency or date) so I have to preformatted)

More help will be appriciated

 

[CCLINT]

Sure you can format it locally. You only need to inspect the field type and depending if it is a number, date, whatever field, format it accordingly.

Otherwise, you will have to use SQLServer date conversion syntax if you are connecting to that.
What you mentioned in the original post had to do with JET and not SQLServer. The format function is DBMS specific with DBMS specific syntax..

 

[MarkSweetland]

Try "select HireDate=convert(varchar,Hiredate,101) from employee"


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[WFadloun]

HI all
Thank For Co-operating

MarkSweetLand :
your solution works great but I got the date formatted "mm/dd/yyyy" ,,,I'll look for the conversion code to get the date formatted "dd/mm/yyyy"

CCLINT :
yes it could but really complicated for my project

thank for help both

 

[WFadloun]

ya to get the date formatted "dd/mm/yyyy" have to use code 103

"select HireDate=convert(varchar,Hiredate,103) from employee"

regards