Distinct sql ? 1

[azrobert]

I am trying to limit the records returned to 1 for each batch number in the following sql.....

I am familliar with this in access but fairly new to VB

strQuerySql = "SELECT DISTINCT * FROM ChillTemp where batch = " & Selected.Text '"

what did i do wrong ?


thanks in advance

ps vb 6.0 with an access database


thanks in advance !

 

[vb5prgrmr]

Say you have the following simple table ...
[tt]
Batch Value
1 1
1 2
1 3
1 4
2 1
2 2
2 3
2 4
...
[/tt]

You will return 4 records when batch = 1 because of your query...

"SELECT DISTINCT * FROM ChillTemp where batch = " & Selected.Text '"

loosly translated

select all records that are not the same where batch = 1

or...

find all records where batch = 1, now search value field for unique items... lets see there is a 1, a 2, a 3, and a 4 so return each of these records since each record is different from one another.

So you may want to change your query to have specific fields instead of the * or add to your where clause.

On another note if you can get what you want in the query editor of access then switch to sql view and take a look at the query there. For the most part (a very general statement) you can copy the query from there and paste it into your VB program (you may have to change some of the syntax).

Good Luck

 

[Transcend]

Change

strQuerySql = "SELECT DISTINCT * FROM ChillTemp where batch = " & Selected.Text '"

To

strQuerySql = "SELECT DISTINCT * FROM ChillTemp where batch = '" & Selected.Text & "'"

Transcend

 

[azrobert]

I actually screwed up and put the wrong sql in there..... it is clsoe to the one giving me trouble.....

here is the full code:


Dim dbs As Database
Dim RS1 As Recordset
Dim strQuerySql As String
Dim checkdate As Date
Dim testdate As String

checkdate = DTPicker1.Value
testdate = "#" & checkdate & "#"

ListGrid.Clear
ListGrid.Rows = (0)
ListGrid.Cols = (2)
ListGrid.ColWidth(0) = 600
ListGrid.ColWidth(1) = 900


Set dbs = OpenDatabase("C:\program files\Temps\TempLogs.mdb&quot

strQuerySql = "SELECT distinct * FROM ChillTemp where Date = " & testdate & " "


Set RS1 = dbs.OpenRecordset(strQuerySql)
Do Until RS1.EOF
ListGrid.AddItem RS1!batch & Chr(9) & RS1!Date

RS1.MoveNext
Loop
dbs.Close



Thanks for the quick reply !!!!!!!!!!!!!!

 

[SilverSide]

try # between the dates

strQuerySql = "SELECT distinct * FROM ChillTemp where Date = #" & testdate & "#"




®od
--------------------------------------some Music?

http://www.artistlaunch.com/silverside

 

[azrobert]

Nope..... get a run time error on that one

missing operator

 

[vb5prgrmr]

Sometimes (only sometimes), you can pass it a string and access will convert it for you since it is wrapped with date markers (#).
[tt]
strQuerySql = "SELECT distinct * FROM ChillTemp where Date = '#" & testdate & "#'"
[/tt]

Good Luck

 

[azrobert]

The Problem I am having is not with the date.... this

strQuerySql = "SELECT DISTINCT * FROM ChillTemp where Date = " & testdate & " "

is pulling the correct records, it is just pulling multiple records for the same date, I would like to get only one record for each date......


I need to find a good tutorial for sql......

 

[vbSun]

Are u sure there is only one row that identifies the

Date = " & testdate & " "

condition?

first this is wrong..

it should be
[Date]=#" & testdate & "#"
cos, Date is a reserved word, and testdates' datatype is date.

Then, if u have mulitple rows, and u want only one, u can go for,

"select top 1 from chilltemp where [Date]=#" & testdate & "#"

 

[azrobert]

Thanks VB, changing the * to specific fields did the trick