How can I get rid of the Error in the result?

[henniec]

Good Day,

I am combining four different queries and use Sample date as the link. I am using the flow table sample date as the primary date. Not all the tables have the same number of record and when I run the query everywhere where there is not an equivalent date there is an error as seen in the sample. How can I eliminate that error. In other words make it blank.

Thanks for your time.

Hennie

qryMEWS0300Data
Sample Date Flow pH1 pH2 pH3 O and G
20070102 1568.7 7.83 8.16 8.53 #Error
20070103 606.66 8.21 7.93 8.29 #Error
20070104 1147.5 8.22 8.28 8.19 7.32
20070105 1437.5 8.15 8.28 8.11 #Error
20070106 3088.7 8.13 7.99 8.20 #Error

 

[lespaul]

what's the SQL of qryMEWS0300Data?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[henniec]

Thank you for the reply. Here is the sql. There is nothing special to the query as it only five tables/queries linked on one date set and one field from each selected.

The last table may have say 27 records while the others have 54 records

SELECT qryMEWS0300WeeklyFlowData.sample_date_time, qryMEWS0300WeeklyFlowData.result_value AS Flow, qryMEWS0300pHP1Data.Result_value AS pH1, qryMEWS0300pHP2Data.Result_value AS pH2, qryMEWS0300pHP3Data.Result_value AS pH3, qryMEWS0300OandGData.result_value AS {O&G]
FROM ((qryMEWS0300OandGData RIGHT JOIN (qryMEWS0300WeeklyFlowData LEFT JOIN qryMEWS0300pHP1Data ON qryMEWS0300WeeklyFlowData.sample_date_time = qryMEWS0300pHP1Data.sample_date_time) ON qryMEWS0300OandGData.sample_date_time = qryMEWS0300WeeklyFlowData.sample_date_time) LEFT JOIN qryMEWS0300pHP2Data ON qryMEWS0300WeeklyFlowData.sample_date_time = qryMEWS0300pHP2Data.sample_date_time) LEFT JOIN qryMEWS0300pHP3Data ON qryMEWS0300WeeklyFlowData.sample_date_time = qryMEWS0300pHP3Data.sample_date_time;

 

[PHV]

Have a look at the Nz function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[henniec]

Thank you,

I have tried both O&G: nz([qryMEWS0300OandGData]![result_value]) or O&G: nz([qryMEWS0300OandGData].[result_value]) and still receive the same result. #Error

Does not seem to help.

Hennie

 

[lespaul]

SELECT qryMEWS0300WeeklyFlowData.sample_date_time, qryMEWS0300WeeklyFlowData.result_value AS Flow, qryMEWS0300pHP1Data.Result_value AS pH1, qryMEWS0300pHP2Data.Result_value AS pH2, qryMEWS0300pHP3Data.Result_value AS pH3, NZ(qryMEWS0300OandGData.result_value, "What you want instead of #ERROR") AS {O&G]
FROM ((qryMEWS0300OandGData RIGHT JOIN (qryMEWS0300WeeklyFlowData LEFT JOIN qryMEWS0300pHP1Data ON qryMEWS0300WeeklyFlowData.sample_date_time = qryMEWS0300pHP1Data.sample_date_time) ON qryMEWS0300OandGData.sample_date_time = qryMEWS0300WeeklyFlowData.sample_date_time) LEFT JOIN qryMEWS0300pHP2Data ON qryMEWS0300WeeklyFlowData.sample_date_time = qryMEWS0300pHP2Data.sample_date_time) LEFT JOIN qryMEWS0300pHP3Data ON qryMEWS0300WeeklyFlowData.sample_date_time = qryMEWS0300pHP3Data.sample_date_time;

and you may want to do that in the ORIGINAL query, in this case the qryMEWS0300OandGData query.

 

[PHV]

So, the problem seems to reside in qryMEWS0300OandGData ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886