problems with DISTINCT 1

[johnnyv]

Hello can any one tell me why..
the following statement returns a record count of 0
strsql = "SELECT * From INV_Prof Where [AdvisorKey] = " & lngAdvisorKey

Yet the following statement returns a recordcount of 1
strsql = "SELECT DISTINCT [ClientName] From INV_Prof Where [AdvisorKey] = " & lngAdvisorKey


There are no records in the Table that meet the above conditions so the returned recordcount should be = 0

??

Thanks

 

[NoCoolHandle]

What type of DATABASE are you using?
What is the content in the row?

 

[johnnyv]

Access 97
the DB has a total of 12 records in it but none of the records have data in the field [AdvisorKey] that match the value of lngAdvisorKey

 

[petermeachem]

Can we see the code that goes after the select string. Must be something odd in it somewhere.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[SonOfEmidec1100]

Mmmm I think you have your comments the wrong way round.

When you do a SELECT DISTINCT it returns a nonupdatable recordset with a recordcount of zero.

Your first statement returns an updatable recordset, and as there are no existing rows the cursor is set to the 'pending' new record and the recordcount is 1.

If you use DISTINCTROW you will get an updatable recordset.

My Access terminology may be a bit off but it should give you the idea.

 

[johnnyv]

Thanks for the replys

I tried the DISTINCTROW statement within my sql and it kinda works. What I mean is that if there are no reocrds given a clientname with a certain lngAdvisorKey it will tell so. The problem is that if there are multiple records with the same clientname for a unique lngAdvisorKey then it returns all of them as part of the record set. Here is my code, any comments would be appriciated!!

strsql = "SELECT DISTINCTROW [ClientName] From INV_Prof Where [AdvisorKey] = " & lngAdvisorKey
adorecordset.Open strsql, adoconnection, adOpenStatic, adLockReadOnly
If adorecordset.RecordCount > 0 Then
adorecordset.MoveFirst
Do Until adorecordset.EOF
lstClient_Name.AddItem adorecordset!ClientName
adorecordset.MoveNext
Loop
Else
MsgBox "There are no saved Clients for this Advisor"
End If
' closing connection to DB
adorecordset.Close
Set adorecordset = Nothing
adoconnection.Close

 

[petermeachem]

I don't use
If adorecordset.RecordCount > 0 Then
adorecordset.MoveFirst

But always

If not ( adorecordset.eof and adorecordset.eof )Then

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[johnnyv]

You make an excellent point petermeachem

I will give it a try

 

[petermeachem]

Should be

If not ( adorecordset.eof and adorecordset.Bof )Then

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/