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
     
LVL 3
Kevin RobinsonPrivate VB.NET ContractorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GivenRandyCommented:
Are the StartDate and EndDate strings formatted correctly?
SanclerCommented:
On my reading of the query, it only returns QueryDate, so there would be no "SumofVBI" item in the returned row.

Roger

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin RobinsonPrivate VB.NET ContractorAuthor Commented:
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.
SanclerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.