VB,MS Access: Problem with date format


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: VB,MS Access: Problem with date format

  1. #1
    Join Date
    Nov 2006
    Posts
    3

    VB,MS Access: Problem with date format

    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

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    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")

  3. #3
    Join Date
    Dec 2003
    Posts
    2,750
    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
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. Date data type problem, please help
    By sal in forum VB Classic
    Replies: 3
    Last Post: 08-25-2002, 10:58 PM
  2. Date changes from VB to Access
    By Bas in forum VB Classic
    Replies: 2
    Last Post: 05-04-2002, 07:32 AM
  3. VB, Regional Settings, and Access
    By Greg Rothlander in forum VB Classic
    Replies: 4
    Last Post: 12-13-2000, 12:11 AM
  4. Access And SQL date statement
    By Saiful in forum VB Classic
    Replies: 7
    Last Post: 11-24-2000, 05:21 AM
  5. Replies: 2
    Last Post: 10-24-2000, 09:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center