Could someone help me , by explaining where I'm going wrong with this SQL Query of an Access Database which populates a Datagrid,
From a calendar selection the user can select a From and To Date and I want the SQL Query to ONLY pick up the dates between these two values, the selection is fine it seems to be the sql query which is the problem ... (I have added dates rather than the
text value to make it easier to get working ) the following query wil pick up dates outside of the selected values 01/08/2010 , 02/08/2010, etc
SELECT COUNT(*) AS Date_Installed, Date_Installed AS Expr1, Installed_By FROM Buildprocess WHERE (Date_Installed BETWEEN '20/08/2010' AND ' 25/08/2010') GROUP BY Date_Installed, Installed_By
Thanks for your reply , I am usinging the Calendar Function within Visual Webdeveloper, but my biggest issue is the SQL Query, as its not returning the correct search values , as I said I sak for between "10/08/2010" and "19/08/2010" and I receive results
including July "07" , the results i am after are a list of Dates "Dates_Installed" displaying a Count of how many Installed by Each Engineer "Installed_By" - Between the Date Criteria...
So Far this gives me a very rough result
Installed_By
Expr1001
Expr1
5
21/07/2010
5
22/07/2010
3
23/07/2010
4
MooreD
20/8/2010
2
MooreD
23/8/2010
1
MooreD
24/8/2010
1
yorkj
20/8/2010
Query Is
SELECT COUNT(*) AS Installed_By, Installed_By, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN '20-08-2010' AND '25-08-2010') GROUP BY Installed_By, Date_Installed
"It is not uncommon to find that people are trying to work with dates and times stored in TEXT fields. The problem with this approach is that any sorting on dates in TEXT fields will only be done alphabetically because they will be treated as strings."
My guess is that this is exactly what you're doing! So once again, read the articles, use datetime fields for storing dates and use parameterized queries....
Yes I will read the article and apreciate I need to work this out myself but what i dont understand is why if I run the query with actual values in it as per my earlier message, it still returns incorrect values
SELECT COUNT(*) AS Installed_By, Installed_By, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN
'20/8/2010' AND '25/08/2010') GROUP BY Installed_By, Date_Installed
ok I now have the query working as per the links you kindly sent me , It works fine if I add manual dates into the but when I try adding the calendar date selection for the between 2 dates into the sql query I get a error :
Syntax error (missing operator) in query expression '(Date_Installed BETWEEN ([Date1]=?) AND ([Date2]=?) GROUP BY Installed_By, Date_Installed'
I am using the calender feature from within the Viusual Web Developer , the back end code I have is :
Sub calDate_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim dt1 As DateTime
dt1 = Convert.ToDateTime(CalDate.SelectedDate.ToString("d"))
Date1.Text = dt1
End Sub
Sub calDate2_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim dt2 As DateTime
dt2 = Convert.ToDateTime(CalDate2.SelectedDate.ToString("d"))
Date2.Text = dt2
End Sub
Can you help me , Is it because the selected date returns a value of say 17/08/2010 instead of 17/08/2010 00:00:00 ?
sismeya
Member
63 Points
60 Posts
Access Database SQL Query between two dates Datagrid
Aug 25, 2010 05:11 PM|LINK
Hi
Could someone help me , by explaining where I'm going wrong with this SQL Query of an Access Database which populates a Datagrid,
From a calendar selection the user can select a From and To Date and I want the SQL Query to ONLY pick up the dates between these two values, the selection is fine it seems to be the sql query which is the problem ... (I have added dates rather than the text value to make it easier to get working ) the following query wil pick up dates outside of the selected values 01/08/2010 , 02/08/2010, etc
SELECT COUNT(*) AS Date_Installed, Date_Installed AS Expr1, Installed_By FROM Buildprocess WHERE (Date_Installed BETWEEN '20/08/2010' AND ' 25/08/2010') GROUP BY Date_Installed, Installed_By
Any Help would be geat !
Cheers
riswadkarhar...
Contributor
2458 Points
561 Posts
Re: Access Database SQL Query between two dates Datagrid
Aug 25, 2010 05:41 PM|LINK
Hi,
May I know what calendar control you are using? Are you directly passing the date value to SQL query?
I think the issue seems to be with the date format. SQL Server may encounter issue while converting the date you have mentioned.
If you try '2010-08-20' and '2010-08-25', it should work fine. You may need to convert the date from your calendar control and then pass it to SQL.
Thanks,
----------------------------------------------------------
Please mark as answer if the post helped you.
hans_v
All-Star
35986 Points
6550 Posts
Re: Access Database SQL Query between two dates Datagrid
Aug 25, 2010 06:42 PM|LINK
Use parameterized queries:
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
and read this for more info about Access and Date(times)
http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET
sismeya
Member
63 Points
60 Posts
Re: Access Database SQL Query between two dates Datagrid
Aug 26, 2010 09:42 AM|LINK
Hi
Thanks for your reply , I am usinging the Calendar Function within Visual Webdeveloper, but my biggest issue is the SQL Query, as its not returning the correct search values , as I said I sak for between "10/08/2010" and "19/08/2010" and I receive results including July "07" , the results i am after are a list of Dates "Dates_Installed" displaying a Count of how many Installed by Each Engineer "Installed_By" - Between the Date Criteria...
So Far this gives me a very rough result
Query Is
SELECT COUNT(*) AS Installed_By, Installed_By, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN '20-08-2010' AND '25-08-2010') GROUP BY Installed_By, Date_Installed
From Table "Build_Process
Thanks
Andy
hans_v
All-Star
35986 Points
6550 Posts
Re: Access Database SQL Query between two dates Datagrid
Aug 26, 2010 10:22 AM|LINK
Did you read the articles I referred to?
"It is not uncommon to find that people are trying to work with dates and times stored in TEXT fields. The problem with this approach is that any sorting on dates in TEXT fields will only be done alphabetically because they will be treated as strings."
My guess is that this is exactly what you're doing! So once again, read the articles, use datetime fields for storing dates and use parameterized queries....
sismeya
Member
63 Points
60 Posts
Re: Access Database SQL Query between two dates Datagrid
Aug 26, 2010 10:29 AM|LINK
Yes I will read the article and apreciate I need to work this out myself but what i dont understand is why if I run the query with actual values in it as per my earlier message, it still returns incorrect values
SELECT COUNT(*) AS Installed_By, Installed_By, Date_Installed AS Expr1 FROM Buildprocess WHERE (Date_Installed BETWEEN '20/8/2010' AND '25/08/2010') GROUP BY Installed_By, Date_Installed
cheers
Andy
hans_v
All-Star
35986 Points
6550 Posts
Re: Access Database SQL Query between two dates Datagrid
Aug 26, 2010 10:35 AM|LINK
Do you store the dates into Text fields?
In that case "21/07/2010" is larger as "20/08/2010" and smaller as "25/08/2010"
sismeya
Member
63 Points
60 Posts
Re: Access Database SQL Query between two dates Datagrid
Aug 26, 2010 11:14 AM|LINK
Yes they are stored as Text Fields
hans_v
All-Star
35986 Points
6550 Posts
Re: Access Database SQL Query between two dates Datagrid
Aug 26, 2010 11:23 AM|LINK
Exactly what I expected. The solution of your problem can be found in the articles....
sismeya
Member
63 Points
60 Posts
Re: Access Database SQL Query between two dates Datagrid
Aug 26, 2010 07:11 PM|LINK
ok I now have the query working as per the links you kindly sent me , It works fine if I add manual dates into the but when I try adding the calendar date selection for the between 2 dates into the sql query I get a error :
Syntax error (missing operator) in query expression '(Date_Installed BETWEEN ([Date1]=?) AND ([Date2]=?) GROUP BY Installed_By, Date_Installed'
I am using the calender feature from within the Viusual Web Developer , the back end code I have is :
Sub calDate_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs) Dim dt1 As DateTime dt1 = Convert.ToDateTime(CalDate.SelectedDate.ToString("d")) Date1.Text = dt1 End Sub Sub calDate2_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs) Dim dt2 As DateTime dt2 = Convert.ToDateTime(CalDate2.SelectedDate.ToString("d")) Date2.Text = dt2 End SubCan you help me , Is it because the selected date returns a value of say 17/08/2010 instead of 17/08/2010 00:00:00 ?
Cheers
Andy