Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query - Time being Removed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query - Time being Removed
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query - Time being Removed
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query - Time being Removed
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query - Time being Removed
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? "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Query - Time being Removed
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing time that has its colon removed | Excel Worksheet Functions | |||
Query automatically being removed | Excel Discussion (Misc queries) | |||
Time query | Excel Worksheet Functions | |||
Web Query Run-time Error | Excel Programming | |||
Excel time out query | Excel Programming |