When DCount does not return a value 1

[CharlieT302]

Hi Folks,

I am using Dcount to trigger code. Basically, I have a subform that may or may not return values. If there are no values, the form is completely blank without even a blank row. Functionally not relevant, but aesthetically not pleasing.

I have separate unbound version of the subform (that is stacked on top of the bound form) that I would like to display if the record source is empty. This way I will always see a form with at least one blank record.

What I Need:
If Dcount returns one or more records, I want the normal bound form to be visible. If nothing is returned, I want the unbound form to be visible.

Ex: Dcount("*","DataSource") > 0 then
MyUnBoundform.Visible = False
else
MyUnBoundForm.Visible = True

The problem is that Dcount returns nothing if there are no records. Therefore, the "else" is never evaluated.

How can I work around this?

 

[MazeWorX]

How about

If Not IsNull(Dcount("*","DataSource") > 0 then
MyUnBoundform.Visible = False
else
MyUnBoundForm.Visible = True
end if

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

Try:

Me.MyUnboundForm.Visible = Nz(Dcount("*","DataSource"),0) = 0

Duane
Hook'D on Access
MS Access MVP

 

[CharlieT302]

That did it!

I replaced my formula with:
Nz(Dcount("*","DataSource"),0) = 0

Thanks again.

 

[TheAceMan1]

Howdy dhookom . . .

Although you've solved the problem with a work around, I'm stuck at the post origination and why [blue]Dcount("*","DataSource")[/blue] returns a [blue]Null[/blue] at all. To my understanding the only time [blue]DCount[/blue] returns a [blue]Null[/blue] is as follows:

[blue]Criteria: ... [purple]Any field that is included in criteria must also be a field in domain; otherwise the DCount function returns a[/purple] [red]Null[/red].[/blue]

Not only does the Op not use criteria (alleviating the null problem), but the Op also uses the [blue]"*"[/blue] wildcard, which [purple]counts nulls as well![/purple] As such I'd expect ...

[blue]   If Dcount("*","DataSource") > 0 then
      MyUnBoundform.Visible = False
   Else
      MyUnBoundForm.Visible = True
   End If[/blue]

... to work.

Is it that 2003 successor versions are returning [blue]Null[/blue] instead of [blue]zero[/blue] (this is what triggered me)? ... or perhaps some other reason for returning null I'm not aware of?

What am I missing?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

Aceman,
I think you are correct regarding the DCount() returning 0. I just created a query named "datasource" that returns no records. I then entered:

? dcount("*","datasource")
 0

From my test in Access 2010, the Nz() shouldn't be required.

Duane
Hook'D on Access
MS Access MVP