#Error when data is missing 1

[CharlieT302]

Hi Folks,

I have a form with a field that counts the records in an unrelated table (within a query) using the Dcount function. However, the table is actually linked to an Excel spreadsheet.

When the spreadsheet is missing, the calculation returns a #Error value. I would like the calculation to return a zero if the data cannot be found.

I have tried:
IIf(Dcount("ID","Qry_Import")<1,0,Dcount("ID","Qry_Import"))
and
IIF(Isnull(Dcount("ID","Qry_Import")),0,Dcount("ID","Qry_Import"))

I have also tried using the IsError function without success.

Ideas?

 

[genomon]

Use the Nz() function.
Nz(variable,0)

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[CharlieT302]

Hi Genomon,

That was my first thought as well. I have tried the NZ function by encasing the Dcount function within it. I have also tried encasing the Site_ID field within the Dcount function. No luck.

FYI:
I actually posted this question a month ago (have not had to work on this problem until now). I was given a solution of creating a global function in the modules window that defines the spreadsheets pathname, which is a fine solution. However, this dbase will go out to other sites. I would like to create a solution that does not "lock" them into a predetermined path..if possible.

 

[genomon]

I would say lose the IIf altogether. If you're just trying to count, then Nz(Dcount("ID","Qry_Import"),0) should work.
If not, you should be able to test for the linked Excel sheet with something like If ObjectExists("Table", "tablename") Then....

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[CharlieT302]

The NZ function is not working regardless of how I try it. I found references to building a ObjectExists function. I will try that.

I do have a question re: that...

Will this work since I am referencing a table object, which is linked to a spreadsheet. The spreadsheet may not exist, but the link to it will always exist. If it sees the link, will it not still produce the same error since it will not find the field data?

 

[genomon]

You're probably right. I guess the problem is: When the spreadsheet is missing.... It just isn't good design to have a linked table that can be linked to nothing. The spreadsheet should never be missing, just empty.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[TheAceMan1]

How are ya CharlieT302 . . .

Just a guess, If by missing you mean the [blue]excel file is not in its expected folder[/blue], use the [blue]Dir[/blue] function to detect this.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[genomon]

Yikes! That's why the Ace was TMOW. And, a star...

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[CharlieT302]

Thanks Aceman,

I just got back to read this post. I haven't yet tried your solution. I will try it this afternoon. To answer Genomon observation: I agree and would prefer to not have the spreadsheet missing.

The reason for the dilemma is that we are sending the spreadsheet out to various sites. They complete the info and e-mail a copy of it to us. We then "upload" the data from the link. After we grab the info from one spreadsheet, we will replace it with another.

The error occurs when a spreadsheet is deleted and not yet replaced with another.

Thanks for the help.

 

[CharlieT302]

Eureka,

I got it to work by combining Dir with Len in the On Open form event:

Something like:

If Len(Dir("path/File"))=0 then
Field.Value = 0
Else
Field.Value = Dcount(Field,Table)

Thanks for the input.

 

[PHV]

No need for the Len function:
If Dir("path/File") = "" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886