SQL Query - Time being Removed

M

Mike

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
 
M

Mike

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

Mike

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

Mike said:
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
Click to expand...
Click to expand...
 
M

Mike

Try to format it before you copy the recordset

Mike said:
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 said:
You need to format the column that the date is in.
Columns("A:A").NumberFormat = _
"[$-409]m/d/yy h:mm AM/PM;@"

Mike said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

EricG

Question 1: Are you sure that what you have stored in your database table
actually has a time component? I just took a look at a couple of my
databases, and all the time components show as 0:00 even though I'm using the
Date/Time type. I think that's because I just use the Short Date format for
those fields, so the time component is set to zero. Try editing your table
and setting the format for those fields to General Date.

Question 2: If you format your worksheet cells as a number with a few
decimal places, and then run your query, do you see anything other than zeros
after the decimal?
 
M

Mike

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 said:
Try to format it before you copy the recordset

Mike said:
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 said:
You need to format the column that the date is in.
Columns("A:A").NumberFormat = _
"[$-409]m/d/yy h:mm AM/PM;@"

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Procedure Call 2
How can I convert the date/time format in a SQL query on Access insidea ASP script ? 1

Top