Converting #Error to return zero

[SilverFox4me]

Im using Count([ContractID]) to count the number of records in a query used on a Subform. It works great when the query finds 1 or more records but if there are no records it returns #Error and I need it to return 0(zero)... Any ideas how I can achieve it?

 

[boblarson]

You can try:

Count(Nz([ContractID],0))

 

[SilverFox4me]

Thanks boblarson but that still returns #Error when the query doesn't find any records.

 

[SilverFox4me]

It may be easier to help me if I explain what I'm trying to achieve... When my Form activates, I've set a Command Button to be either Enabled = True or Enabled = False depending upon the number of records found in a query on a Subform... 0 records it should be disabled whilst 1 or more records it should be enabled. It works fine when there are some records found but the #Error returned for no records does not disable it.

I often use...

If IsNull(Me!CustomerID) Or Me!CustomerID = "" Then

I think this identifies a Null or empty value, is there something I can put to identify an error?

 

[John_W]

You could do it the other way round - have the button disabled by default but enabled if the count >0?

 

[SilverFox4me]

Have tried that John_W but it never enables the button and when there are 1 or more records and I'm getting a 'Run-Time error "2427" You entered an expression that has no value' when there are no records.

The expression I'm using is, where CountTotal is a field on my Form that displays the number of records on my Subform from a calculated field...(Count([TotalRecords])

If Me!CountTotal > 0 Then
Me!BtnThisSite.Enabled = True
End If

Is the expression wrong?

 

[neileg]

Let's have a think about what you want to test. You have a query that populates your subform. You then have a Count() that counts the number of records in the subform.

Why not short circuit this and use DCount() to count the numner of qualifying records in the underlying dataset? This will return a zero if it finds no qualifying records.

 

[Simon_MT]

I cheat a little, I put would put the Contract on the Parent. Then:

If Contract_No on the Parent IsNumeric then do the Count else 0

If you need to go into Contract Enquiry and the Parent Contract number IsNumeric then open Contract Enquiry else do nothing other than a MsgBox.

Its not exactly the answer and fails if the user is not on a real record.

Simon

 

[SilverFox4me]

Thanks everyone,

"Sites" is the underlying Table and OTCustomerID is a Field on the parent Form. The following calculating Field works great and returns a 0(zero) instead of an #Error

=DCount("[ContractID]","[Contracts]","[ContractCustomerID] = [OTCustomerID]")

Thanks again!