VB,MS Access: Problem with date format
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.....
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")
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
Microsoft MVP (Visual Basic)
By sal in forum VB Classic
Last Post: 08-25-2002, 10:58 PM
By Bas in forum VB Classic
Last Post: 05-04-2002, 07:32 AM
By Greg Rothlander in forum VB Classic
Last Post: 12-13-2000, 12:11 AM
By Saiful in forum VB Classic
Last Post: 11-24-2000, 05:21 AM
By Bill in forum VB Classic
Last Post: 10-24-2000, 09:29 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL