#Error in the field

[gdanalakshmi]

I have 2 tables TableA with columns (MoveId, QA)
TableB with columns (MoveId, QB).

TableB will not have all the records as in TableA.
I am listing the 3 fields MoveId, QA, QB. When there is no matching record in TableB, I get an #error in the output. How do I set it to 0.
I tried using the IIf also for QB field.

 

[pdx_man]

Does your query look like this?

Select TableA.MoveId, TableA.QA, TableB.QB From TableA Left Join TableB On TableA.MoveId = TableB.MoveId

Or do you have a calculated field using TableB.QB? If so, try looking up the Nz function in Access Help.

 

[gdanalakshmi]

SELECT MoveRequests.MoveRequest_ID, RptQry_VOQ1MinMax.Q1Hours, IIf(IsNull([Q2Hours]),0,[Q2Hours]) AS Q2Hours1, IIf(IsError([Q3Hours]),0,[Q3Hours]) AS Q3Hours1
FROM ((MoveRequests INNER JOIN RptQry_VOQ1MinMax ON MoveRequests.MoveRequest_ID = RptQry_VOQ1MinMax.MoveRequest_ID) LEFT JOIN RptQry_VOQ3Final ON MoveRequests.MoveRequest_ID = RptQry_VOQ3Final.MoveRequest_ID) LEFT JOIN RptQry_VOQ2Final ON MoveRequests.MoveRequest_ID = RptQry_VOQ2Final.MoveRequest_ID;

The above is my query - MoveRequests, RptQry_VOQ1MinMax(Only include rows where the MoveRequest_ID fields match)

MoveRequest, RptQry_VOQ2Max - Include all records from MoveRequest and those that match with Rptqry_VOQ2Max


MoveRequest, RptQry_VOQ3Final - Include all records from MoveRequest and those that match with Rptqry_VOQ3Final

Rptqry_VOQ3Final - This table contains only few moverequests, so the Q3Hours column has lot of #errors.

 

[pdx_man]

Give this a try:

SELECT MoveRequests.MoveRequest_ID, RptQry_VOQ1MinMax.Q1Hours, Nz([Q2Hours],0) AS Q2Hours1, Nz([Q3Hours],0) AS Q3Hours1

before the WHERE clause

 

[gdanalakshmi]

it doesnot work?

any other suggestions?

 

[gdanalakshmi]

To eliminate the problem, I checked the primary key field value for Null or not other than checking the needed field name in Q3 table.

 

[pdx_man]

By it's nature, a Primary Key field cannot contain a NULL value, so ...

Is RptQry_VOQ1MinMax (and the like) a query or a table?

Your issues may be occurring before this query.

 

[gdanalakshmi]

RptQry_VOQ1MinMax It is a query.
RptQry_VOQ2Final is a query.
RptQry_VOQ3Final is a query.

 

[pdx_man]

Your issues may be occurring before this query.

If you'd like, you can send me your DB and I can take a look, but your issues are in one of the queries.

dhenely@go2netmail.com