Join problem - #Error

[יריב החביב]

Hello,

Usually when I join querys to get a new one,

If in the child query the record is missing, i get missing field in the new
query.

but somtime's, for reason's i don't know, i get in the new query field

the value " #Error " , wich i dont know how to handel.

Why is it happened and how to avoid this ?

thank you

 

[Allen Browne]

Can you give us an example of a query that yields #Error?

There can be lots of reasons why this could appear. A common example is if
you use one of the VBA functions that cannot handle the Null value, such as
CDbl() or Val().

 

[יריב החביב]

thank you Allen

It was the VBA function 'format number'

What can i do if i must use this function ?

 

[Allen Browne]

Format() copes with nulls, so I'm not sure what's going on.

There must be something else happening here. For example, if you had:
Foramt([Field1] / [Field2], "0.00")
it would yield #Error when Field2 is zero. (Division by zero error.)

Or perhaps your regional setting has a different list seperator, so you need
something other than the comma between items inside the brackets?