Help with form #Name error

[ShawnH]

Hello, I'm having a problem with a form and was hoping someone can help.

I have things assigned to individuals and those things are in different status. I have a crosstab query to count the number of things in each category, per individual. I have a form based off of this crosstab query.

Please see the attached images. The problem that I have is, when there is at least one thing in each status, the form is displayed correctly with no errors. However, if there are no things assigned to a particular category (which in cases, there might be no things assigned to that status), it shows the #Name error. I understand why it's showing the error (that field is not in the crosstab since no things are assigned to that status).

What I need it to do is, if this field is not in the crosstab, show 0 or nothing at all. I tried the Nz function on the form but that don't seem to work. HasData doesn't work in forms so I'm at a loss.

Anyone know what I could do?

 

[plog]

I have a form based off of this crosstab query.

That's the issue. You should avoid building reports off crosstab queries, much less forms. This form surely doesn't allow edits does it? Why isn't it a report then?

In either case, the problem exists because you can't rely on a cross-tab query to have specific columns, but reports/form require you to define specific columns. If a form/report expects a SallyTuesday field and the underlying datasource doesn't have a SallyTuesday field, its going to break. Further, if you add a new person and a new column gets added in your cross-tab, BobWednesday, the form will not show it until you go into designview and add the BobWednesday control. It's a painful way to make forms all the way around, which is why you should avoid cross-tabs as the basis for forms/reports.

There is a way around this, but I'd like to know why this is a form?

 

[ShawnH]

No real reason why it's a form vs. report. I could easily make it a report if necessary. I just made it a form since it was displaying info, vs. needing to print etc.

 

[plog]

Also, looking at your image of the form, there's no reason this needs to be a cross-tab, unless of course you are trying to overcome a poor structure. Is 'Ready To Be Faxed' a field name, or a value in a field?

Can you post some sample data from your table? Include table and field names.

 

[ShawnH]

Here's a dump of my the main table with dummy data and unnecessary fields removed.

The cross tab tells me:

Of the individuals that have referrals assigned to, how many referrals are in each category.

Both Assigned to and Referral Status is a drop down menu to a look up table.

 

[plog]

I don't see why a cross tab comes into this. Your data looks structured properly. Why not just do a regular totals query grouping by Referral_Status? Why a cross-tab?

 

[ShawnH]

To be honest, I really do not know why I went with a cross tab. I'll switch it to a total query. Same info, cleaner, and easier to work with.

Thank you!

 

[ShawnH]

Ok, the other form that I was using to display numbers is attached. I think this one does have to be a crosstab, unless I'm not thinking outside the box.

Any recommendation on this?

 

[plog]

Yes that one does, but why do you need that format? Why wouldn't this do it:



Clear, Crystal
----To Fax: 18
----Received: 1
----Info Needed: 5
----Total: 24
Doe, John
----Waiting Vendor: 14
----Confirmed: 24
----Pending Signature: 18
----Total 56
Harvey, Paul
---- etc.

If you really need that type of format, generally its better to export a query's data to Excel and do a Pivot Table.

 

[ShawnH]

Format is not necessarily required. I'll switch this to a report and group it that way.

Thanks for your help (again)!