VB,MS Access: Problem with date format

[sumalatham]

Hi...

I'm using VB 6.0 as Front end and MS Access as Back end.....

I want to Show the date in dd/MM/yyyy for reports always....

So when I use SQL statement in Access, it works fine... But if use in VB 6.0, it gives me error...

Ex: select format(Tdate,"dd/MM/yyyy") from emp where sal > 2000
The above example is in Access....

If I use this code in VB it gives me error.....

Please any one can suggest me how to write the above statement with recordset and also use date format in that.....

Bye
Suma

[Ron Weller]

select Tdate from emp where sal > 2000

VB6 Understands Access Dates, just send the value. If you are assigning it to a text box then use date for the DataFormat Property, or if you want to show as text then use the format function from within VB6 rather than your SQL Statement.
Several Ways to do that:
Dim MyDate As Date
Dim MyDateStr As String
Dim rs As ADODB.Recordset
...
'Save Date into Date Variable then Format to a string
MyDate = rs.Fields("Tdate").Value
MyDateStr = Format(MyDate, "dd/mm/yyyy")
...
'Just Format it Directly, so long as it is not null
MyDateStr = Format(rs.Fields("Tdate").Value, "dd/mm/yyyy")
...
'Assign it to an unbound textbox, no variables needed
Me.Text1 = rs.Fields("Tdate").Value
'Assign it with formatting to an unbound textbox, no variables needed
Me.Text1 = Format(rs.Fields("Tdate").Value, "dd/mm/yyyy")

[pclement]

You don't indicate what the error is but you probably need to specify an alias in your SQL statement for the formatted date:

SELECT FORMAT(Tdate,"dd/MM/yyyy") AS FormattedDate FROM emp WHERE sal > 2000