Calculated field produces #Error (previously Null), when no records

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

John

I have a query (Results) that references fields calculated in a second
query (Calculation_Query). The relationship is such that Results may
have records for which there are no records in Calculation_Query since
Results includes other tables. Initially, one of the inputs for the
calculated fields in Calculation_Query was based off of a cascading
query. Now, I have written a function to provide the input instead.
In the version where the calculated fields were based off of the
queries, the calculated fields would be Null in the Results query when
Calculation_Query returned no records. However, the calculated fields
now return #Error to Results when Calculation_Query has no
corresponding record. Non-calculated fields from Calculation_Query
still return Null, only the calculated fields now return #Error. Any
help is appreciated. Thanks
 
Can you force the function to return a value that the textbox can consume,
without showing the #Error?

Otherwise, try using Nz().


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Thanks, Steve, for the response. The function always returns a valid
numeric (double) value. Also, the #Error is not in a textbox, it
appears in the query results window. Finally, I tried Nz in the
second query, but it did not help.

Here is a modified example/test of the problem (not my tables):

qry1:
calc1 - Calculated field

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

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

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