Can "HasData" be used in a query?

  • Thread starter Thread starter John
  • Start date Start date
J

John

I posted a message a few days ago about a query that I have that uses
a calculated field from a another query. Sometimes the referenced
query does not have any records for the second query and I get an
#Error value in the second query. I have tried IsNull, IsError, nz,
nnz, etc. and nothing has worked. The second query is used as the
record source for a report which will not run, but gives a "... too
complex to be evaluated... " because of the #Error values in the
recordsource query. I have also tried several fixes on the textbox on
the report (IsNull, IsError, HasData, etc). Is there a way to use
HasData in the recordsource query? This report is critical, so any
help is appreciated.

See "Re: Calculated field produces #Error (previously Null), when no
records" in this group for detailed description of problem, if needed.

Thanks - John
 
The problem appears to be with the use of a function

calc2: Function(x)

What is the data type of the parameter passed to the
function, and the data type the function returns??

Have you tried ...

Function TestFunction ( x as variant) as variant
 
What is the SQL of each of these queries?

--
HTH

Dale Fye


I posted a message a few days ago about a query that I have that uses
a calculated field from a another query. Sometimes the referenced
query does not have any records for the second query and I get an
#Error value in the second query. I have tried IsNull, IsError, nz,
nnz, etc. and nothing has worked. The second query is used as the
record source for a report which will not run, but gives a "... too
complex to be evaluated... " because of the #Error values in the
recordsource query. I have also tried several fixes on the textbox on
the report (IsNull, IsError, HasData, etc). Is there a way to use
HasData in the recordsource query? This report is critical, so any
help is appreciated.

See "Re: Calculated field produces #Error (previously Null), when no
records" in this group for detailed description of problem, if needed.

Thanks - John
 
Dale - Here is an illustration of the query. The function always
returns a valid numeric (double) value. The #Error appears in the
query (qry3) results window.

qry1:
custnum
field1 - Result of cascading query to find 'Field' value as of
'Date_In'

qry2: tblCustomers, tblOrders, qry1
(Query has records only for customers with orders)
CustNum
OrderDate
qry1.field1 <-- These two fields are always equal, numeric results
calc1: Functon([Date_In]) <-- and are never Null or Error

SQL: SELECT tblCustomers.custnum, tblOrders.orderdate, qry1.field1,
function([Date_In]) AS Calc1
FROM (tblCustomers LEFT JOIN qry1 ON tblCustomers.custnum =
qry1.custnum) INNER JOIN tblOrders ON tblCustomers.custnum =
tblOrders.custnum
WHERE (((tblOrders.orderdate)<=[Date_In]))
ORDER BY tblCustomers.custnum;

qry3: tblCustomers, qry2
(Query has records for all customers)
CustNum
qry2.field1 <-- Returns "Null" for customers w/no orders
qry2.calc2 <-- Returns "#Error" for customers w/no orders

SQL: SELECT tblCustomers.custnum, qry2.field1, qry2.calc1
FROM tblCustomers LEFT JOIN qry2 ON tblCustomers.custnum =
qry2.custnum;

Since qry3 is used in a report, it's ok if the values are Null, but I
get an error in the report if there are Error values. It seems
strange that in qry2 the results are identical, but in qry3 the fields
are different. Thanks, John
 
Back
Top