When Query Returns Null, Say "No Data Present"

  • Thread starter Thread starter MJ
  • Start date Start date
M

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.
 
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.
 
MJ said:
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
 
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.
 
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
 
John,

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

Thanks again for your help.
 
Back
Top