Date comes out as Time after using DATE in VBA


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Date comes out as Time after using DATE in VBA

  1. #1
    Gill Guest

    Date comes out as Time after using DATE in VBA


    Hi ,

    I am using Access 2000 VBA. When I append to a table using VBA and sql statments
    the date comes out as time. I have formatted all fields and text boxes and
    get an overflow message when I try to format Date in VBA.

    Here is my code

    sql = "INSERT INTO Employees( Empl_No, details_No, Start_Date)"
    sql = sql & " SELECT " & Chr(34) & NewEmplNo & Chr(34)
    sql = sql & ", " & Chr(34) & Me.Details_No & Chr(34) & ", " & Date DoCmd.RunSQL
    (sql)


    When I look at the record after the append the date shows the time instead
    and when I formatted in vba using the following I get an overflow error:

    Dim strDate As Date
    strDate = Date
    strDate = Format(strDate, dd / mm / yyyy)' I get the overflow error here

    Thanks for any help

    Gill



  2. #2
    Arthur Wood Guest

    Re: Date comes out as Time after using DATE in VBA


    Gill,

    try this to resolve the error on the Format:

    Dim strDate As String

    strDate = Format$(Date, "dd/mm/yyyy")


    You had declared strDate as a DATE type variable, and then tried to assign
    a STRING value to that variable. Internally, VB (and VBA) store DATES as
    a NUMBER of tyep Double, where the WHOLE part of the Number (to the left
    of the Decimal point) is the NUMBER of DAYS since 12/31/1899 and the fractional
    part of the number is the fraction of one day (24 hours), since midnight,
    (measured in MilliSeconds). Thus NOON would have the frational part .5

    Also, in Access, remember that DATE-type STRINGS as enclosed in "#" characters
    (#1/1/2002# - which is that AUTOMATICALLY converted to the corresponding
    DATE value)

    Arthur Wood


    "Gill" <maloneygillian@hotmail.com> wrote:
    >
    >Hi ,
    >
    >I am using Access 2000 VBA. When I append to a table using VBA and sql statments
    >the date comes out as time. I have formatted all fields and text boxes and
    >get an overflow message when I try to format Date in VBA.
    >
    >Here is my code
    >
    >sql = "INSERT INTO Employees( Empl_No, details_No, Start_Date)"
    >sql = sql & " SELECT " & Chr(34) & NewEmplNo & Chr(34)
    >sql = sql & ", " & Chr(34) & Me.Details_No & Chr(34) & ", " & Date DoCmd.RunSQL
    >(sql)
    >
    >
    >When I look at the record after the append the date shows the time instead
    >and when I formatted in vba using the following I get an overflow error:
    >
    >Dim strDate As Date
    >strDate = Date
    >strDate = Format(strDate, dd / mm / yyyy)' I get the overflow error here
    >
    >Thanks for any help
    >
    >Gill
    >
    >



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