OpenRecordSet syntax help

[Guest]

Hello,

I have a counter on my switchboard that runs with the following code:

Function MissingEntryCountRecords() As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From havecrf Where (formstat = 2 &
isnull(page) = False)")


rst.MoveLast

MissingEntryCountRecords = rst.RecordCount

End Function

If I leave at just 'formstat = 2', it works great, but I also need to check
if the field 'page' is null and I just can't seem to get the syntax correct.
Is it possible to get a second condition into the Where clause of
OpenRecordSet?

 

[Jeff Boyce]

Pat

One approach to doing this would be to create a query in query design view.
Get it working, doing the selection you want, then convert to SQL view.
Copy the SQL statement, and return to your code and paste it in. You might
need to make a few punctuation changes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

You can't use the ampersand "&" to mean AND in a logical statement

Set rst = db.OpenRecordset("Select * From havecrf Where (formstat = 2
AND isnull(page) = False)")




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Sorry, I may have posted this in the wrong section. This is actually code
within a module. When I adjust the code as shown below, I get an error
message that says: 'Run time error:3601. Too few parameters. Expected 1'.
Can I do what I want to do in Visual Basic code?

 

[John Spencer]

If you are getting the TOO few parameters message that usually means
that a field name is misspelled or does not exist at all in the table.
Do you have a field named Page? Do you have a field named FormStat?

Although when I look at what you are doing, I would suggest using the
dcount function and skipping all the recordset stuff.

MissingEntryCountRecords = DCount("*","HaveCrf","Formstat=2 AND [Page]
is not null"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Sorry, I may have posted this in the wrong section. This is actually code
within a module. When I adjust the code as shown below, I get an error
message that says: 'Run time error:3601. Too few parameters. Expected 1'.
Can I do what I want to do in Visual Basic code?

Function MissingEntryCountRecords() As Long

 

[John Spencer]

If you are getting the TOO few parameters message that usually means
that a field name is misspelled or does not exist at all in the table.
Do you have a field named Page? Do you have a field named FormStat?

Although when I look at what you are doing, I would suggest using the
dcount function and skipping all the recordset stuff.

MissingEntryCountRecords = DCount("*","HaveCrf","Formstat=2 AND [Page]
is not null"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Sorry, I may have posted this in the wrong section. This is actually code
within a module. When I adjust the code as shown below, I get an error
message that says: 'Run time error:3601. Too few parameters. Expected 1'.
Can I do what I want to do in Visual Basic code?

Function MissingEntryCountRecords() As Long

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Hi John,

You were dead on. I needed to reference a query that had the fields I was
referencing from two different tables, not just the one table. Once I put in
the query name instead of just one of the table names, I was good to go.
Thanks!
--
Pat Dools

If you are getting the TOO few parameters message that usually means
that a field name is misspelled or does not exist at all in the table.
Do you have a field named Page? Do you have a field named FormStat?

Although when I look at what you are doing, I would suggest using the
dcount function and skipping all the recordset stuff.

MissingEntryCountRecords = DCount("*","HaveCrf","Formstat=2 AND [Page]
is not null"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Sorry, I may have posted this in the wrong section. This is actually code
within a module. When I adjust the code as shown below, I get an error
message that says: 'Run time error:3601. Too few parameters. Expected 1'.
Can I do what I want to do in Visual Basic code?

Function MissingEntryCountRecords() As Long