Sql help please.

[Jacksparrow87]

Hi people,

I created a topic not soo long ago asking for help with a select statement and the people who helped me out did a great job. However after working on my application for a few days I can see that the select statement doesn't actually work.

Now so far I have the following in a search button:

     Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
            Dim adapter As New OleDbDataAdapter("Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where Dol BETWEEN #" & dtp.Text & "# AND #" & dtp2.Text & "# And CompID = " & cmbcomp.SelectedValue & " And NoteNumber = 1 Group By [Company], [ChaCode]", conn)
            Dim dt As New DataTable("Log")
            adapter.Fill(dt)
            dglog.DataSource() = dt
            dt.DefaultView.AllowNew = False

Dol in the Access database has been set to date.

Now comes the difficult part, its hard to explain because I have a range of data in my database but the answer the above Sql statement gives is not correct, for example I entered a few test data in the month of June, now when I choose a date in June in dtp1 and dtp2 (where the range of data is in between these dates) the answer the sql gives me is not the answer it should be as its empty, another example is I have data on the 30/06/2008 and data on the 01/07/2008 now if I do a search between these dates it doesnt show the correct data.

A lot of the times it shows all counts and not between the dates chosen.

Personally I think the problem is with the dates.

Would it be possible to have something like:

where Dol BETWEEN >#" & dtp.Text & "# AND <#" & dtp2.Text & "#

I think I should put the >< symbols in there somewhere, thanks.

I hope someone could offer me help please.

 

[Andrzejek]

IMHO, it would be better if you try this:

Dim strSQL As String = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where Dol BETWEEN #" & dtp.Text & "# AND #" & dtp2.Text & "# And CompID = " & cmbcomp.SelectedValue & " And NoteNumber = 1 Group By [Company], [ChaCode]"

[green]'Show your SQL in Immediate window so you can test it.[/green]
Debug.Print strSQL

Dim adapter As New OleDbDataAdapter(strSQL, conn)

It may be as simple as adding () in your SQL:
"Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where [red]([/red]Dol BETWEEN #" & dtp.Text & "# AND #" & dtp2.Text & "#[red])[/red] And CompID = " & cmbcomp.SelectedValue & " And NoteNumber = 1 Group By [Company], [ChaCode]"

Have fun.

---- Andy

 

[Jacksparrow87]

Hey thanks for your help,

I tried both methods but still it doesn't show the correct data.

 

[Sorwen]

How exactly is the data wrong?

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Jacksparrow87]

The data is incorrect because for example there is data in the log table which is dated 30/06/2008 and when I narrowed down my search between 28/06/2008 and 02/07/2008 it failed to pick up this data matter of a fact it didnt pick up any data. I have also tried this with other data in the log table, for example I tried narrowing down some data I put in yesterday and in the search fields I put in the 15th and 19th of July, however it didnt show any data again.

Now if I pick 1st of July and the 31st of July it picks up every data, including the data in June which should not be included. I think the problem could be with the date format but I just dont know what it is.

Hope that helps.

 

[Sorwen]

So generally it doesn't pull back anything at all. I'm not a database master by any means, but generally a proper date format (perhaps better to say by default rather than proper) is 06/28/2008 to 07/02/2008. What will sometimes happen is it will try to make it into a date it will understand rather than causing an error. That is what it sounds like is happening. I don't think you said in your other threads, but what kind of database are you pulling your data from?

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Sorwen]

NM. mdb. Access. I was wondering why I thought it was an Access database. You also need to use Having instead of Between. Depending on the version it isn't going to like that Between sometimes.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Jacksparrow87]

Hey I used parameters and have fixed the problem.

Thanks guys for your help.