What's wrong with my query ??

[Wesam]

Hi,

I have a query which extracts some records from a table according to some condition. The problem is when I run this query on Access it works perfectly; but when I use it in my VB program it doesn't work properly.

I have "Table1", and "Table2" and field in each called "Index"

"Table1.Index" "Table2.Index"
3 4
2 7
0 5
1 2
9 10

My query is:
"SELECT [Table1].* FROM [Table1] WHERE [Table1].Index NOT IN (Select [Table2].Index FROM [Table2])"

.. this query should return :

0
9
3
1

.. the problem is that if I create a query in Access and paste this code it work perfectly but when I use it in VB it returns only the first record .. I don’t know why??

Could anyone help

Thank you in advance.

Wesam

------------------

[preeti]

Hi,

How are you getting the results? How do you know it is returning only one record?

Preeti

[Wesam]

Actually I need the result to be processed by another routine, however, I use the following:

Dim Rec As RecordSet
SqlCommand="SELECT ....."

Set Rec = DB.OpenRecordset(SqlCommand)

For Counter = 0 TO Rec.RecordCount-1

'I take each record and pass it to a function

Next

.. when I debug this code and stop at Rec.RecordCount and check its value I always find it 1 !!!!!!!

Hopefully I well explained this problem, I have no idea why this happens in VB though it does't in Access.

I appreciate your help.

Wesam.

------------------

[preeti]

Hi,

Here's your problem: Recordcount for some reason returns the number of records accessed, not the total number of records!!!
(Don't ask why, I don't know)

Instead of using a for loop, you could use a do until loop:

do until Rec.eof
'This will loop through the records until you reach the end of the recordset.

'I take each record and pass it to a function

loop

..

HTH,

Preeti

[Wesam]

Ohhhhh thank you preeti .. that was the problem, and I corrected it as you said and it worked perfectly .. I really appreciate it.

Wesam

------------------

[kishore]

Here's your problem: Recordcount for some reason returns the number of records accessed, not the total number of records!!!
(Don't ask why, I don't know)

Record Count Property returns Current Record Position of the table.

You have to movelast to get total no of records.

I hopes this helps in future.

Rs.movelast
Rs.recordcount

The above code gives you exact no of Records.

[Wesam]

I actually did so, my fault was that I didn't know the exact function of RecordCount property. Anyway thank you very much for your help.

Wesam

------------------