Exporting query results to a spreadsheet

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have written a macro that executes a query, then
exports the results of the query to an excel sheet. I
would like to only execute the export if there are rows
returned from the query. (When attempting to reference
the query in the conditional expression area, I keep
receiving the message "can not find name". Can you
assist?

Jim
 
Hi Jim,


You can write a small piece of VBA code to do a dcount, which will return
the number of records eg:

Function getcount()
Dim x As Variant

x = DCount("*", "Invoices")
If x > 0 Then
MsgBox "There are records"
Else
MsgBox " There are no records"

End If
End Function

What this will do, is show a msgbox with "there are records" if the count
is greater than zero. You could modify it, so that if the count was greater
than zero, open the report,

hth

Mark

This information is provided "as is" and expresses no warranties and
confers no rights.
 
Jim,

Put this in the Condition column of your macro designer...
DCount("*","NameOfYourQuery")>0
 
Thanks for the hint.
The below command in the conditions expression section of
the macro determined if the query returned any results.


DCount("[field_name]","query_name","[field_name]<>null")>0

Jim
 
Jim

It is good that you got something that works for you.

The expression I suggested should also have determined if the query
returned any results. I should note that "[field_name]<>null" is not
valid syntax. If anything, it should be "[field_name] Is Not Null".
But I wouldn't do it like that myself. If there is really a chance, as
you imply by your expression, that the query could return some records
with nothing in the [field_name] field, and at the same time no records
with an entry in the [field_name] field, I would put the criteria of Is
Not Null directly in the [field_name] column in the query itself, and
continue to use the expression in the macro condition:
DCount("*","query_name")>0
 
Back
Top