Thanks, I was able to determine that the problem was with how the labeldate
was loaded into the Access database. i was referencing 2 tables and the one
table had the date format as just date and not date and time.

thanks for the help.

Mike

"Mike" wrote:

Try to format it before you copy the recordset

"Mike" wrote:

Thanks Mike but I tried formatting the cells after the recordset is copied
and the time always is 12:00:00 AM.

For example, the labeldate field in Access (formatted as Date/Time) has a
stored value of 11/17/2009 2:35 PM. When this record is pulled into Excel
using the query below, the labeldate field results in 11/17/2009 and does not
bring in the time field.

Mike

"Mike" wrote:

You need to format the column that the date is in.
Columns("A:A").NumberFormat = _
"[$-409]m/d/yy h:mm AM/PM;@"

"Mike" wrote:

I have an excel file which queries a backend Access database using the code
below. The labeldate field is a date/time field in Access. When I run the
query and get the data into a worksheet, Excel removes the time and just
dumps the date. I tried formatting the labeldate field in Excel but it only
shows the date. I ran the same query in Access and the labeldate field showed
the full date and time in the query result. I need some help as to explain
why the time is missing.

strsql = "SELECT AddresseeCity, AddresseeState, mailingcity, senderstate,
mailingstate, country, addresseename, addresseeaddress, " & _
"addresseezip, labeldate, labelnumber, mailservice,
totalpostage, originzip, " & _
"WgtPkginLbs, ozs, flatrate FROM seizures WHERE " & _
"labeldate =#" & beg & "# and labeldate <=#" & fin & "# AND
(addresseestate = '" & state & "');"

cnn.Open stcon
rst.Open strsql, cnn, adOpenForwardOnly, adLockOptimistic
'recs1 = rst.RecordCount
Worksheets("Data").Select
i = 1
For Each fld In rst.Fields
ActiveSheet.Cells(1, i).Value = fld.Name
i = i + 1
Next fld
'On Error Resume Next
Worksheets("Data").Range("A2").CopyFromRecordset rst