#Error

B

Brian

I have a query that is using a left join. About 5% of the records are #Error,
which I expected. When I pull this query into another query, I am trying to
write an iif statement that replaces any record with #error with another
field. So for example:

I have field1...
1
2
3
#Error
5

Then I have field2 with..
10
7
6
4
11

When I am done, I would like a field that reads....
1
2
3
4 - with Field2 replacing #Error whereever it occurs
5

I have tried iif(iserror( as well as iif(field1="#error"....

Any help would be greatly appreciated.
 
M

Maurice

Why not place the #Error in the cirteria field of the query. Maybe you have
to place it between "" but then you have a set of records you can manipulate
anyway you want.
 
J

Jerry Whittle

What is causing the error? If it's something like a null field, use it in the
IIf statement.
 
B

Brian

Thanks for your replies.....The #Error is being caused when I join two
queries together, one of which doesn't have records for certain accounts.
So, I have a list of accounts and a list of GPS coordinates. The list of
coordinates doesn't cover every account. So when I build my query off of the
account number, the ones without coordinates on the linked GPS query show up
as #Error.

Every iif statement I try comes up as #Error. So if I write
iif([myfield]="#error","yes","no") it comes up as #Error. The #error won't
allow any function to calculate off of it.

Thanks again for all your help. Any way I can get something to calculate on
these fields?
 
J

Jerry Whittle

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Brian said:
Thanks for your replies.....The #Error is being caused when I join two
queries together, one of which doesn't have records for certain accounts.
So, I have a list of accounts and a list of GPS coordinates. The list of
coordinates doesn't cover every account. So when I build my query off of the
account number, the ones without coordinates on the linked GPS query show up
as #Error.

Every iif statement I try comes up as #Error. So if I write
iif([myfield]="#error","yes","no") it comes up as #Error. The #error won't
allow any function to calculate off of it.

Thanks again for all your help. Any way I can get something to calculate on
these fields?

Jerry Whittle said:
What is causing the error? If it's something like a null field, use it in the
IIf statement.
 
B

Brian

This is the SQL for the left join query which brings across the #Error for
missing records:
SELECT WFM_Data_Join.wrwoID AS [Service_Call_#],
Format([wrCreateDate],"mm/dd/yyyy") AS LongDate, WFM_Data_Join.wrUid,
(Format([wrcreatedate],"hh")*3600)+(Format([wrcreatedate],"nn")*60)+(Format([wrcreatedate],"ss"))
AS Begin_Sec, GeoCodes.Latitude, GeoCodes.Longitude, [sub_hub] & [sub_node]
AS 6Digit
FROM dbo_rre_subscribers RIGHT JOIN (WFM_Data_Join LEFT JOIN GeoCodes ON
WFM_Data_Join.wrAccountNumber = GeoCodes.Acct) ON
dbo_rre_subscribers.sub_account_num = WFM_Data_Join.wrAccountNumber
WHERE (((Format([wrCreateDate],"mm/dd/yyyy"))<>""));

This is the SQL statement for the query that builds off of the above, where
I want an iif statement which decides to use either a house's lat/long or a
fixed lat/long for a neighborhood. So, iif(house lat/long = #error, fixed
lat/long, house lat/long). That is basically what I am trying to do. If we
don't have the lat long for the house(#error) give me the lat long for the
fixed point in the neighborhood to calculate the distance off of:

SELECT GeoCodes_WFM_Master.[Service_Call_#], GeoCodes_WFM_Master.LongDate,
GeoCodes_WFM_Master.wrUid, GeoCodes_WFM_Master.Begin_Sec,
IIf(IsNull([GeoCodes_WFM_Master.Latitude]),"",[GeoCodes_WFM_Master.latitude])
AS HouseLat, GeoCodes_WFM_Master.Longitude AS HouseLong, [HubNode -
LatLong].Latitude AS NodeLat, [HubNode - LatLong].Longitude AS NodeLong,
1*3963*farccosine(Sin([houselat]/57.2958)*Sin([nodelat]/57.2958)+Cos([houselat]/57.2958)*Cos([nodelat]/57.2958)*Cos([nodelong]/57.2958-[houselong]/57.2958)) AS Air_Miles, IIf([houselat]="#error","yes","no") AS error
FROM [HubNode - LatLong] RIGHT JOIN GeoCodes_WFM_Master ON [HubNode -
LatLong].[6Digit] = GeoCodes_WFM_Master.[6Digit]
ORDER BY GeoCodes_WFM_Master.LongDate, GeoCodes_WFM_Master.wrUid,
GeoCodes_WFM_Master.Begin_Sec;

Thank you again in advance.

Brian
Jerry Whittle said:
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Brian said:
Thanks for your replies.....The #Error is being caused when I join two
queries together, one of which doesn't have records for certain accounts.
So, I have a list of accounts and a list of GPS coordinates. The list of
coordinates doesn't cover every account. So when I build my query off of the
account number, the ones without coordinates on the linked GPS query show up
as #Error.

Every iif statement I try comes up as #Error. So if I write
iif([myfield]="#error","yes","no") it comes up as #Error. The #error won't
allow any function to calculate off of it.

Thanks again for all your help. Any way I can get something to calculate on
these fields?

Jerry Whittle said:
What is causing the error? If it's something like a null field, use it in the
IIf statement.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a query that is using a left join. About 5% of the records are #Error,
which I expected. When I pull this query into another query, I am trying to
write an iif statement that replaces any record with #error with another
field. So for example:

I have field1...
1
2
3
#Error
5

Then I have field2 with..
10
7
6
4
11

When I am done, I would like a field that reads....
1
2
3
4 - with Field2 replacing #Error whereever it occurs
5

I have tried iif(iserror( as well as iif(field1="#error"....

Any help would be greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count field 4
Hyperlink 1
combining 2 expressions 3
Calculate Null Field 2
Query problem. 10
Error when trying to use calc field from Query 8
Aggregate Function Error 0
Finding null 4

Top