![]() |
Query on Date range does not qualify the Year
I have a query that pulls data based on a date range. The data that comes
out is correct based on the Month and Day values, however the year seems to not factor into the query. For instance, I am looking at dates from years 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive results from 06/01 thru 07/01 from both 2004 and 2005. Here is what my query looks like: BeginDate = Format(Date, "mm/dd/yyyy") EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy") SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate = '" & _ BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate" |
Query on Date range does not qualify the Year
Looks like what you are getting is an alpha sort based on the text string,
not a date sort based on date values. The way to fix it depends on your data source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the dates in their native numeric format. "Malcolm Makin" wrote: I have a query that pulls data based on a date range. The data that comes out is correct based on the Month and Day values, however the year seems to not factor into the query. For instance, I am looking at dates from years 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive results from 06/01 thru 07/01 from both 2004 and 2005. Here is what my query looks like: BeginDate = Format(Date, "mm/dd/yyyy") EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy") SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate = '" & _ BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate" |
Query on Date range does not qualify the Year
My data source is another Excel workbook. The dates were entered by default
as m/d/yyyy. I have then set the explicit format on the cells to be mm/dd/yyyy. However, the query pulls data regardless of year. Like I metioned before, the Month/Day range is correct, just not the year. I have also tried the #mm/dd/yyyy method in my query, but it does not help. "K Dales" wrote: Looks like what you are getting is an alpha sort based on the text string, not a date sort based on date values. The way to fix it depends on your data source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the dates in their native numeric format. "Malcolm Makin" wrote: I have a query that pulls data based on a date range. The data that comes out is correct based on the Month and Day values, however the year seems to not factor into the query. For instance, I am looking at dates from years 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive results from 06/01 thru 07/01 from both 2004 and 2005. Here is what my query looks like: BeginDate = Format(Date, "mm/dd/yyyy") EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy") SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate = '" & _ BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate" |
Query on Date range does not qualify the Year
That is consistent with an alphabetic sort - in alphabetic terms both
"06/01/2004" and "06/02/2005" are = "06/01/2005" and <= "07/01/2005" (alpha sort looks at each character left to right). If you are using Excel data - and if the parameters were entered AS DATE VALUES (regardless of cell formatting), don't change the dates at all - just use WHERE (reqdate = " & BeginDate & ") AND (reqdate <= " & EndDate & ")" If, though, the dates are actually stored as TEXT values, then convert them first: CBeginDate = DateValue(BeginDate) CEndDate = DateValue(EndDate) And then I think (not sure, never had to do it this way) you could use WHERE (DateValue(reqdate) = " & CBeginDate & ") AND (DateValue(reqdate) <= " & CEndDate & ")" "Malcolm Makin" wrote: My data source is another Excel workbook. The dates were entered by default as m/d/yyyy. I have then set the explicit format on the cells to be mm/dd/yyyy. However, the query pulls data regardless of year. Like I metioned before, the Month/Day range is correct, just not the year. I have also tried the #mm/dd/yyyy method in my query, but it does not help. "K Dales" wrote: Looks like what you are getting is an alpha sort based on the text string, not a date sort based on date values. The way to fix it depends on your data source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the dates in their native numeric format. "Malcolm Makin" wrote: I have a query that pulls data based on a date range. The data that comes out is correct based on the Month and Day values, however the year seems to not factor into the query. For instance, I am looking at dates from years 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive results from 06/01 thru 07/01 from both 2004 and 2005. Here is what my query looks like: BeginDate = Format(Date, "mm/dd/yyyy") EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy") SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate = '" & _ BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate" |
Query on Date range does not qualify the Year
Now that you brought up the point of an alphabetic sort, I've realized what
is happening here. The results has defied all logic because it obviously has not been treating the values as dates (otherwise the query would have worked). I examined the column in the Excel sheet, and realized that there are 2 or 3 extra rows with comments or other text in this column, before any Date actually occurs. So what is happening is that Excel during the query determines the data type for this column to be text rather than a date. I have tried using DateValue in the query like you suggested, but alas it did not accept it (was worth a try). I'm not sure I can convince those whom I am creating this query for to simply delete the extra text, so do you have any further suggestions on how to "force" the query to recognize the column as a date? I suppose as a work around, I could check each value for the year with an IF statement, after the query is actually run (I'm using ADO rather than MS Query, for more flexibility). "K Dales" wrote: That is consistent with an alphabetic sort - in alphabetic terms both "06/01/2004" and "06/02/2005" are = "06/01/2005" and <= "07/01/2005" (alpha sort looks at each character left to right). If you are using Excel data - and if the parameters were entered AS DATE VALUES (regardless of cell formatting), don't change the dates at all - just use WHERE (reqdate = " & BeginDate & ") AND (reqdate <= " & EndDate & ")" If, though, the dates are actually stored as TEXT values, then convert them first: CBeginDate = DateValue(BeginDate) CEndDate = DateValue(EndDate) And then I think (not sure, never had to do it this way) you could use WHERE (DateValue(reqdate) = " & CBeginDate & ") AND (DateValue(reqdate) <= " & CEndDate & ")" "Malcolm Makin" wrote: My data source is another Excel workbook. The dates were entered by default as m/d/yyyy. I have then set the explicit format on the cells to be mm/dd/yyyy. However, the query pulls data regardless of year. Like I metioned before, the Month/Day range is correct, just not the year. I have also tried the #mm/dd/yyyy method in my query, but it does not help. "K Dales" wrote: Looks like what you are getting is an alpha sort based on the text string, not a date sort based on date values. The way to fix it depends on your data source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the dates in their native numeric format. "Malcolm Makin" wrote: I have a query that pulls data based on a date range. The data that comes out is correct based on the Month and Day values, however the year seems to not factor into the query. For instance, I am looking at dates from years 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive results from 06/01 thru 07/01 from both 2004 and 2005. Here is what my query looks like: BeginDate = Format(Date, "mm/dd/yyyy") EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy") SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate = '" & _ BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate" |
Query on Date range does not qualify the Year
I can see why that is a problem now. Those comments are going to be a
problem since it means that the column MUST be a text field for ADO purposes. One way to get around this, if the structure of the spreadsheet permits it, would be to create a named range that will exclude the rows with the comments. You are right that you could do a workaround, but a simple IF is not going to help in sorting (unless you want to write your own sort routine!). The fact that you are using ADO instead of MSQuery will help, since you can minipulate the recordset in code. This depends on what you do with the recordset once you have retrieved it. But here is one solution I have in mind: Disconnect the recordset (i.e. close the connection while saving the recordset in memory - if you are not sure how to do this look in an ADO reference or online help for "ADO disconnected recordset"). Then you could add a field (Recordset.Fields.Add) and specify that it will hold a date value (Field.Type = adDate). Then loop through the recordset - check the value of the "problem" date field with IsDate(). If so, set the new field value (the field you created) to the true date using DateValue() to convert it. Then you should be able to sort (Recordset.Sort) accurately by the NEW date field. This is a complex workaround, though, and there might be an easier way - again depending how you are ultimately going to use the recordset. Sometimes these seemingly "simple" problems turn out to be quite complex! If only your users understood the impact of mixing data types in a column!!! "Malcolm Makin" wrote: Now that you brought up the point of an alphabetic sort, I've realized what is happening here. The results has defied all logic because it obviously has not been treating the values as dates (otherwise the query would have worked). I examined the column in the Excel sheet, and realized that there are 2 or 3 extra rows with comments or other text in this column, before any Date actually occurs. So what is happening is that Excel during the query determines the data type for this column to be text rather than a date. I have tried using DateValue in the query like you suggested, but alas it did not accept it (was worth a try). I'm not sure I can convince those whom I am creating this query for to simply delete the extra text, so do you have any further suggestions on how to "force" the query to recognize the column as a date? I suppose as a work around, I could check each value for the year with an IF statement, after the query is actually run (I'm using ADO rather than MS Query, for more flexibility). "K Dales" wrote: That is consistent with an alphabetic sort - in alphabetic terms both "06/01/2004" and "06/02/2005" are = "06/01/2005" and <= "07/01/2005" (alpha sort looks at each character left to right). If you are using Excel data - and if the parameters were entered AS DATE VALUES (regardless of cell formatting), don't change the dates at all - just use WHERE (reqdate = " & BeginDate & ") AND (reqdate <= " & EndDate & ")" If, though, the dates are actually stored as TEXT values, then convert them first: CBeginDate = DateValue(BeginDate) CEndDate = DateValue(EndDate) And then I think (not sure, never had to do it this way) you could use WHERE (DateValue(reqdate) = " & CBeginDate & ") AND (DateValue(reqdate) <= " & CEndDate & ")" "Malcolm Makin" wrote: My data source is another Excel workbook. The dates were entered by default as m/d/yyyy. I have then set the explicit format on the cells to be mm/dd/yyyy. However, the query pulls data regardless of year. Like I metioned before, the Month/Day range is correct, just not the year. I have also tried the #mm/dd/yyyy method in my query, but it does not help. "K Dales" wrote: Looks like what you are getting is an alpha sort based on the text string, not a date sort based on date values. The way to fix it depends on your data source/SQL interpreter; you may need to put #mm/dd/yy# or else leave the dates in their native numeric format. "Malcolm Makin" wrote: I have a query that pulls data based on a date range. The data that comes out is correct based on the Month and Day values, however the year seems to not factor into the query. For instance, I am looking at dates from years 2005 and 2004. If my query looks for 06/01/2005 thru 07/01/2005, I receive results from 06/01 thru 07/01 from both 2004 and 2005. Here is what my query looks like: BeginDate = Format(Date, "mm/dd/yyyy") EndDate = Format(DateAdd("d", 14, BeginDate), "mm/dd/yyyy") SQL = "SELECT reqdate, Customer FROM [detail$] WHERE (reqdate = '" & _ BeginDate & "') AND (reqdate <= '" & EndDate & "') ORDER BY reqdate" |
All times are GMT +1. The time now is 05:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com