Date comes out as Time after using DATE in VBA

[Gill]

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

[Arthur Wood]

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