RecordCount Problems

[trystanhuwwilliams]

Hello,
This is my code:

Dim DAO As Database
Dim RS As DAO.Recordset
Set DB = CurrentDb

Set RS = DB.OpenRecordset(&quot;SELECT * FROM MAIL WHERE_ _NAME= '&quot; & NAME & &quot;' and XOPEN<>-1;&quot;, dbOpenDynaset)

With RS
.Movefirst
.Movelast
End With
TOT=RS.Recordcount:msgbox tot
DoCmd.OpenForm 'MAILB', , , , acFormEdit

This code opens a form based on a query which displays only the records filtered by the SQL statement. i.e NAME(text) & XOPEN (Yes/No box).

What I want to know is why does the Recordcount only count the records filtered by the first part of the statement & ignores the second. The form itself works fine & will only display records matching the Name & where 'XOPEN' = 'No', while the Recordcount will count all the records matching the name whether 'XOPEN' = 'Yes' or 'No'

Does anyone knows why this happens?
Cheers,
T

 

[grnzbra]

Is it possible that you have some nulls or something in the XOPEN field. You say you want the no values, but the sql statement is asking for anything that is not yes. Perhaps changing the sql statement to XOPEN = 0.

 

[xlbo]

Thanks, I tried whar u suggested but it continues to produce
the same results. For example if there were two records for SMITH, the first where XOPEN=NO & second where XOPEN=YES, then the query would only display 1 out of 1 records but the Recordcount variable will have the value of 2.

Cheers,
T Rgds
~Geoff~

 

[Guest_imported]

Hi.

I am thinking that you want to open the form 'MAILB' with only certain records (ie the sql statement)

Try :

Dim tmpmysql as string, mysql as string

tmpmysql = &quot;SELECT count(*) FROM MAIL WHERE &quot;
mysql = &quot; NAME= &quot; & chr(34) & NAME & chr(34) & &quot; and XOPEN <> -1 and XOPEN is not null&quot;

msgbox(&quot;Record count = &quot; & CDbl(DBEngine.Workspaces(0).Databases(0).OpenRecordset(tmpmysql & mysql).Fields(0))

' here, the filter option is set to the
' where clause 'mysql' in order to open the form
' allowing only records where Name = NAME and XOPEN <> -1
' and XOPEN <> null
DoCmd.OpenForm 'MAILB', , mysql , , acFormEdit


Hope this helps
Regards,
Mr Big