Kevin Robinson
asked on
SQL dates
I have some code that does a count of some fields in a datebase and displays the results on a form. This works fine. What I really need to do is to allow the user to select a date range.
So I got an SQL statement with dates manually inserted first and this worked.
Eg :- "SELECT TBL_Queries.QueryDate FROM TBL_Queries WHERE QueryDate Between #06/07/2005# AND #07/07/2005#"
Then I added some calendar coltrols so the user could select the dates.
StartDate = calStart.Text
EndDate = calEnd.Text
SQL = "SELECT TBL_Queries.QueryDate FROM TBL_Queries WHERE QueryDate Between #" + StartDate + "# AND #" + EndDate + "#"
Now I get the error "INDEX OUT OF RANGE EXCEPTION"
FULL CODE
==========================
StartDate = calStart.Text
EndDate = calEnd.Text
SQL = "SELECT TBL_Queries.QueryDate FROM TBL_Queries WHERE QueryDate Between #" + StartDate + "# AND #" + EndDate + "#"
cmd.CommandText = SQL
cmd.Connection = cn
cn.Open()
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader()
If dr.HasRows Then
lblVBIresults.Text = CType(dr("SumofVBI"), String) '''' <------- Error occurs on this line
lblCVSResults.Text = CType(dr("SumofCVS"), String)
lblYOVResults.Text = CType(dr("SumofYOV"), String)
lblMVResults.Text = CType(dr("SumofMV"), String)
End If
So I got an SQL statement with dates manually inserted first and this worked.
Eg :- "SELECT TBL_Queries.QueryDate FROM TBL_Queries WHERE QueryDate Between #06/07/2005# AND #07/07/2005#"
Then I added some calendar coltrols so the user could select the dates.
StartDate = calStart.Text
EndDate = calEnd.Text
SQL = "SELECT TBL_Queries.QueryDate FROM TBL_Queries WHERE QueryDate Between #" + StartDate + "# AND #" + EndDate + "#"
Now I get the error "INDEX OUT OF RANGE EXCEPTION"
FULL CODE
==========================
StartDate = calStart.Text
EndDate = calEnd.Text
SQL = "SELECT TBL_Queries.QueryDate FROM TBL_Queries WHERE QueryDate Between #" + StartDate + "# AND #" + EndDate + "#"
cmd.CommandText = SQL
cmd.Connection = cn
cn.Open()
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader()
If dr.HasRows Then
lblVBIresults.Text = CType(dr("SumofVBI"), String) '''' <------- Error occurs on this line
lblCVSResults.Text = CType(dr("SumofCVS"), String)
lblYOVResults.Text = CType(dr("SumofYOV"), String)
lblMVResults.Text = CType(dr("SumofMV"), String)
End If
Are the StartDate and EndDate strings formatted correctly?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry yes, stupid mistake.
Just one more thing though. I am counting yes/no fields. The results seem right but come back as -2 in stead of 2. Is this the best way of counting these fields.
Just one more thing though. I am counting yes/no fields. The results seem right but come back as -2 in stead of 2. Is this the best way of counting these fields.
Usually False=0 and True=-1, so that answer represents that two instances of the relevant field are set to True/Yes/Whatever and any others in the records summed are False/No/Whatever. Provided that gives you the information you need, you could continue to do it that way. But, as I say, that is "usually". You might like to consider the following, from the VB Help file definition of the Boolean Data Type
>>
When numeric data types are converted to Boolean values, 0 becomes False and all other values become True. When Boolean values are converted to numeric types, False becomes 0 and True becomes -1.
Note The ToInt32 method of the Convert class in the System namespace converts True to +1. If you need to convert a Boolean value to a numeric data type, take care which conversion method you use.
<<
The other way would be to include a WHERE clause only including True instances and then use COUNT instead of SUM.
Roger
>>
When numeric data types are converted to Boolean values, 0 becomes False and all other values become True. When Boolean values are converted to numeric types, False becomes 0 and True becomes -1.
Note The ToInt32 method of the Convert class in the System namespace converts True to +1. If you need to convert a Boolean value to a numeric data type, take care which conversion method you use.
<<
The other way would be to include a WHERE clause only including True instances and then use COUNT instead of SUM.
Roger