No record to show "0"

[L'apprentis]

I am running a query with a field that can have or a values or no records.
When the field has no record the field is blank. My problem is that I am using a function with that field as a parameter, when that field has no record the function return #Error, It is quite important that I get a "0" instead.
The Nz () function doesn't work (I think it is because it only applies to null field not field with no record) and from what I have been reading so far the Has Data method only applies to report.
The query is to be used in a form, how can I solve this problem?

 

[neileg]

You can use DCount to see if the query would have no values before it runs. Use that to determine if your formula should use the value in the text box, or zero.

Or you could test the text box to see if it is an error.

 

[L'apprentis]

Well, the function is to be used as a new field in the query and therefore, give errors and values depending depending on the parameter field values . So, I don't think I could use the first method.
When your saying to test a text box to see if it is an error, what do you mean? How would that work?

 

[neileg]

No, I mean use DCount before the query runs and if it returns a count of 0, don't run the query.

Iserror() will return a value of True if you point it at a text box that contains Error#

 

[L'apprentis]

Thanks a lot Neileg, I like the idea of the iserror() function that I didn't know which I am going to try just now.

I just fought about an other possibility:
The "parameter field" can only be equal to 2 differents values or have no record so I could create a function to Calculate The PARAMETER FIELD use for the mainFunction:

FctPARAMETERFIELD
If Parameter="A" OR Parameter="B" then
FctPARAMETERFIELD=Parameter
Else
FctPARAMETERFIELD=0
End If

And I would Have the field I want = FctFinal(FctPARAMETERFIELD)

 

[L'apprentis]

I have just tried the following code, to get 0 instead of #Error but it doesn't seem to work.

Public Function FctError(Results As String)
If IsError(Results) = True Then
FctError = 0
Else
FctError = Results
End If
End Function