No records in attempted query is a problem

[emilybartholomew]

I'm using ADO in some VBA code (access 2000) to run a simple query. Some of the time this query will have no records associated with it. However, this is apparently not ok with VB. It gives me the error "You entered an expression which has no value". I don't care if the resulting recordset is empty. Is there a way to get Access not to care either?

I'm including a little bit of code. It's the first "rs.Open" statement that's causing the problem.

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open ("SELECT count(*) FROM Deliverable_players WHERE lead_flag = 0 AND deliverable_id = " & [deliverable_number] & " AND deliverable_year = " & [deliverable_year]), conn
If IsNull(rs(0)) = False Then
rs.Open "SELECT l_name FROM Contacts, Deliverable_players WHERE Contacts.contact_id = Deliverable_players.contact_id AND lead_flag = 0 AND deliverable_id = " & [deliverable_number] & " AND deliverable_year = " & [deliverable_year], conn
num_names = rs.RecordCount
While Not rs.EOF
list = rs(0) & ", " & list
rs.MoveNext
Wend
[dp_list].Value = list
Debug.Print "Should say " & list
End If

 

[jitter]

When working with recordsets I always surround it with a conditional statement to make sure there is a recordset returned before I try to reference anything related to the recordset.


'only expecting one record back
If (Not (rs.EOF)) Then
'put anything related to working with recordset in here
End If

'expecting 1 or more records back
Do While Not(rs.EOF) Then
'put anything related to working with recordset in here
rs.MoveNext
Loop


hope this helps...

 

[emilybartholomew]

In my case I can't even open the recordset. And when I try to surround the "rs.Open..." with a conditional it gives me a "Expected Function or Variable" error (at that line)

 

[jitter]

ooops. did not really look close enough to what you were saying.

By looking at your code you may need to be surrounding some of the values you are passing with single quotes.


rs.Open "SELECT count(*) FROM Deliverable_players WHERE lead_flag = 0 AND deliverable_id = " & [deliverable_number] & " AND deliverable_year = '" & [deliverable_year] & "'", conn


You don't need the () around it. The only other thing I changed in it was added the ' around the date. I am assuming that deliverable_id is a number type. If it is not then you may want to surround it with ' also.

I hope this works....

 

[emilybartholomew]

I tried the query as a regular Access query and it works fine. It returns a count of zero. So I don't think it's a problem with the sql. My hunch is that the VB is executing the query, and then because there are no records which match the criteria described in the where clause, is claiming that there's a problem. Is there a way to just check to see if there are records returned or not, without triggering an error?

 

[jitter]

I don't know. In my opinion the way you have the query written it will not run from vb when you construct the sql string.

Access automatically deals with this when you run it internally.

As far as checking to see if a recordset is empty or no records returned that is what the EOF method does. The fact that you cannot open the recordset tells me that there is something wrong either the connection or the sql string.

What kind of errors is it kicking back? That would greatly help isolate what is causing what.

Access can also be sort of picky about setting the type of statement being executed.

Anyway, I would make sure you are surrounding your string values with a single quote....the other thing is that Access likes # sign around any dates so you have to do that in vb while you are building the sql string. Access normally does it when you pass a date field within itself but when making the string in vb you need to set the string up correctly.

Hope this helps...

 

[TrojanRabbit]

Try replacing rs.Open with rs.OpenRecordset. Keep the parentheses.

 

[emilybartholomew]

Access doesn't like rs.OpenRecordset (Error "Method or data member not found&quot.
rs.OpenRecordset("SELECT count(*) FROM deliverable_players WHERE lead_flag is null AND deliverable_id = " & this_deliverable & " AND deliverable_year = " & this_year), conn
I tried it with conn.OpenRecordset (with conn being the ADO connection to the db) but it can't seem to execute the sql that way. (Error: "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.&quot This same sql works in a plain query.
Set rs = conn.OpenRecordset("SELECT count(*) FROM deliverable_players WHERE lead_flag is null AND deliverable_id = " & this_deliverable & " AND deliverable_year = " & this_year)

deliverable_year and deliverable_id are both integers.

 

[jebry]

Hi!

I don't know if I am looking at a problem or a typo, here is what you posted:

rs.Open ("SELECT count(*) FROM Deliverable_players WHERE lead_flag = 0 AND deliverable_id = " & [deliverable_number] & " AND deliverable_year = " & [deliverable_year]), conn

It should be:

rs.Open ("SELECT count(*) FROM Deliverable_players WHERE lead_flag = 0 AND deliverable_id = " & [deliverable_number] & " AND deliverable_year = " & [deliverable_year], conn)

or:

rs.Open "SELECT count(*) FROM Deliverable_players WHERE lead_flag = 0 AND deliverable_id = " & [deliverable_number] & " AND deliverable_year = " & [deliverable_year], conn

hth
Jeff Bridgham
bridgham@purdue.edu

 

[TrojanRabbit]

You may need to create a totals query using Count with every field. Then, open the query and add an error handler in case it can't open it. I had such a query give me an error when there was nothing to count. You can also check Deliverable_players for emptiness before trying the query. Although this takes more time and is, thus, less practical, it's an option.

 

[cmmrfrds]

It is easier to debug if you construct your sql statement as a variable then you can check out the syntax before executing. If this is an Access 2000 database then surround the date with #. if Sql Server then use '. I assume deliverable_number is numeric.

Dim sql1 as string
sql1 = "Select count(*) FROM Deliverable_players " & _
"WHERE lead_flag = 0 AND deliverable_id = " & _
[deliverable_number] & " AND deliverable_year = " & _
"#" & [deliverable_year] & "#"
Debug.print sql1

rs.Open sql1, conn

If rs.EOF = True then
some message
else
continue
End if

 

[Rolliee]

I would check both re.EOF and rs.BOF. Both must be true for a vacuoous recordset.

Rollie

 

[Rolliee]

You could also do a

numRecs = DCOUNT("fieldname", "Table Name", 'Criteria&quot

If it is zero do your thing or not zero do your other thing.

Rollie E