DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006

    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.....


  2. #2
    Join Date
    Aug 2004
    Orange, California
    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
    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)

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
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.