Question Checking if query is empty

[mauni]

Hi

I need to check whether a query returns zero values. If it returns zero, program needs to go to "EXIT_CREATION".

My code always ends up giving ee = 0, when it should be ee = 1.

This is my code:


Dim ee as integer
ee = 0

Dim db As dao.Database
Dim qdef As dao.QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM customers WHERE billing = 0;"
Set qdef = db.QueryDefs("Show events")
qdef.sql = strSQL
If qdef.ReturnsRecords = False Then
ee = 1
End If
qdef.Close
Set qdef = Nothing
Set db = Nothing

If ee = 1 Then
GoTo EXIT_CREATION
End If

 

[DCrake]

I have noticed with earlier posts of your that you always create querydefs on the fly to obtain results? why? this seems to me to be overkill. You have 18 lines of code whereas 1 line would do it just a accurately and more manageable

If Nz(DSum("*","customers","billing = 0"),0) = 0 Then
[COLOR="Lime"].... your code here[/COLOR]
End If

David

 

[Atomic Shrimp]

Does the qdef.ReturnsRecords line automatically execute the querydef? If not, where in the code is the query actually being run?

 

[mauni]

I have noticed with earlier posts of your that you always create querydefs on the fly to obtain results? why? this seems to me to be overkill. You have 18 lines of code whereas 1 line would do it just a accurately and more manageable

If Nz(DSum("*","customers","billing = 0"),0) = 0 Then
[COLOR=lime].... your code here[/COLOR]
End If

David

Hi

I need to run lots of SELECT statements, which can't be run with docmd.runsql. I am a beginner, and i don't know other way to run them.

The select statements need to be modified on the fly, depending on what user inputs on the forms, so i have to call up the saved query and modify it.

I always call up the saved query with code:

DoCmd.OpenQuery "MySavedQuery", acViewNormal


I tried the code you suggested, but it gives an error:

Syntax Error (missing operator) in query expression 'Sum(*'

So i changed the "*" to follow the PK field which is "aid" (integer). After this the code works. Thanks!

BR,
Mauni

 

[gemma-the-husky]

similar code to DC's then, but use dcount

If Nz(Dcount("*","customers","billing = 0"),0) = 0 then etc

this just counts the number of customers, where the billing value is 0.
this should work with no problems