Can "HasData" be used in a query?

[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

 

[Chris]

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

 

[Dale Fye]

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

 

[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