When Query Returns Null, Say "No Data Present"

[MJ]

I am sure someone has done this before and I am just not finding the right
discussion point.

I have a number of queries which are working correctly, yet when it returns
NULL I would like for it to tell me that "No Data is Present" so that we
don't go off chasing problems that are not there. For this posting I will
include a simplified example of the actual SQL code for one of my queries:

SELECT DISTINCT MyTable.Field1, MyTable.Field2, MyTable.Field3, ...
FROM MyTable
WHERE (((MyTable.Field2) In ("A","B","C")))
GROUP BY MyTable.Field1, MyTable.Field2, MyTable.Field3, ...
HAVING (((MyTable.Field1) Between (StartDate) And (EndDate)));

What I am looking for is when this query results in a NULL table, that
instead of the null table it would populate the Field1 with "No Data is
Present".

How can this be done simply?

Thank you in advance for your assistance with this.

 

[Dale_Fye via AccessMonster.com]

How are you using this query? Is it the source for a form or report?
What version of Access are you using?

Reports have an "On No Data" event that you can use to provide feedback to
the user.

Forms don't have that event, but you can use the forms Load event to
determine how many records there are in the forms recordsource. If that
number is zero, you could display a message. Another way to do it would be
to use DCOUNT to count the number of records in the recordset defined by your
sql statement. If the number is zero, display a message, if not, display the
results of the query.

 

[MJ]

Just using it as SELECT query and exporting the results to a XLS file.

Currently using in Ac03 (Access 2002-2003 file format). We will be moving
to 2007 sometime next month.

 

[Bob Barrows]

I am sure someone has done this before and I am just not finding the
right discussion point.

I have a number of queries which are working correctly, yet when it
returns NULL I would like for it to tell me that "No Data is Present"
so that we don't go off chasing problems that are not there. For
this posting I will include a simplified example of the actual SQL
code for one of my queries:

SELECT DISTINCT MyTable.Field1, MyTable.Field2, MyTable.Field3, ...
FROM MyTable
WHERE (((MyTable.Field2) In ("A","B","C")))
GROUP BY MyTable.Field1, MyTable.Field2, MyTable.Field3, ...
HAVING (((MyTable.Field1) Between (StartDate) And (EndDate)));

Using DISTINCT and GROUP BY in the same query is meaningless. GROUP BY
creates a single record for each combination of values in the GROUP BY
list, so the result of this query is already going to contain distinct
records. There is no need for that DISTINCT keyword.

What I am looking for is when this query results in a NULL table, that
instead of the null table it would populate the Field1 with "No Data
is Present".

How can this be done simply?

I don't know if you would consider this solution to be "simple" or not,
but save the above query as [MightReturnRecords]. Then create another
query that uses this query as its data source:
SELECT Count(*) As TotalRecords FROM [MightReturnRecords]

Save that query as [RecsReturnedFromMightReturnRecords]

Then create a third query that does this:
SELECT iif(T.TotalRecords=0,"No Records Returned","") As Result,
R.Field1, ...
FROM [RecsReturnedFromMightReturnRecords] as T,
[MightReturnRecords] as R

 

[MJ]

Dale,

I am using these queries in a series of DoCmd.TransferSpreadsheet commands
during routine daily processing by a user. On Monday (or first processing of
the week) or at EOM (end of month), these series of commands are triggered
and delivery the files for input to another report process.

DoCmd.TransferSpreadsheet acExport, 8, "MyQuery: WKLY", _
"\\server\Share$\Process_Dir\Source Files\" & _
Format(Now() - Weekday(Now()), "yyyy mm dd") & _
".Weekly Table.xls", True, ""

Since these output files are processed at a later time, my desire is to let
the user (processing the output files) that the files is empty because there
is no data that meets criteria, rather than have them think it errored in
output.

 

[Dale_Fye via AccessMonster.com]

I would use the last alternative. Use DCOUNT or actually open a recordset
using that SQL statement. If there are no records, display a warning message,
or export the results of a query that returns your "No records!" response.
If there are records, then close the recordset and export the query results
to Excel.

Just using it as SELECT query and exporting the results to a XLS file.

Currently using in Ac03 (Access 2002-2003 file format). We will be moving
to 2007 sometime next month.

How are you using this query? Is it the source for a form or report?
What version of Access are you using?

[quoted text clipped - 30 lines]

 

[John Spencer MVP]

Then open a recordset to check to see if there are any records. If not then
export a from a dummy query that generates your message.

If CurrentDb().OpenRecordset("MyQuery: WKLY").RecordCount > 0 Then
DoCmd.TransferSpreadsheet acExport, 8, "MyQuery: WKLY", _
"\\server\Share$\Process_Dir\Source Files\" & _
Format(Now() - Weekday(Now()), "yyyy mm dd") & _
".Weekly Table.xls", True, ""
Else
DoCmd.TransferSpreadsheet acExport, 8, "MyQuery: NoRecords", _
"\\server\Share$\Process_Dir\Source Files\" & _
Format(Now() - Weekday(Now()), "yyyy mm dd") & _
".Weekly Table.xls", True, ""

END IF

MyQuery: NoRecords could look like

SELECT DISTINCT "No records to export for " &
Format(Now()-Weekday(Now()),"yyyy mm dd")
FROM SomeSmallTable

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[MJ]

John,

I was already headed in that direction, but you suggestion confirmed my
thoughts and worked great.

Thanks again for your help.