Link to home
Start Free TrialLog in
Avatar of maximyshka
maximyshka

asked on

What's wrong with the expression DCount in Access 2000

Hi.  I'm trying to put below listed expression to the text field in the report

=IIf(DCount("*","[qry-loans-review-before month-end")>0,[LOAN-NUMBER] & "
" & [RM CODE] & "
" & [OFFICER NAME])

It's not working.  Report shows an error.  What's wrong with the expression.  I use Access 2000
Avatar of mbizup
mbizup
Flag of Kazakhstan image

You need A closing bracket at the end of your query name.   [Query name]
You also need a result for the false condition.

At the moment you have:

iif(condition, result)

you need..
iif(condition, resultiftrue, resultiffalse)
Really, Peter? I thought it would default to null.
This is what I get..
Capture2.JPG
However, it does appear that you are right when the expression is used as a controlsource.
Always keep it simple at first.

So instead of creating a long expression, then not knowing where to start troubleshooting when it fails, ..

Just do this:

=DCount("*","[qry-loans-review-before month-end]")
...If this does not work, then why even bother wrapping it in an IIF()?

Does this work?
IIF(DCount("*","[qry-loans-review-before month-end]")>0,"Greater Than Zero", "NOT Greater than Zero")
Again, if this simple expression fails, then why bother inserting variables or form control references?

Finally, I like to have both the true and false arguments filled in by default.
...And I try to use a naming convention and refer to the controls by that name, instead of the default name (that is the same as the field name)

IIF(Dcount("*","SomeTable")>0, me.txtSomeControl1, me.cboSomeControl2 & " " & me.lstSomeControl3)

Obviously every situation is different, ...just my 2c.

But again always Keep it Simple and test for the basic functionality first, then build on that.

;-)

JeffCoachman

========
edited by aikimark (ZA) to add missing "]" to query name
I think JeffCoachman is getting close to the solution.

I think your domain parameter of the DCount() function is incorrect because it is missing a right bracket.

=IIf(DCount("*","[qry-loans-review-before month-end]")>0 , 
[LOAN-NUMBER] & "" & [RM CODE] & "" & [OFFICER NAME])

Open in new window

Avatar of maximyshka
maximyshka

ASKER

Thank you for your answers.

However,

Query is the following: =IIf(DCount("*","[qry-loans-review-before month-end]")>0,[LOAN-NUMBER] & "
" & [RM CODE] & "
" & [OFFICER NAME], "No Data Found")

I did not include bracket when I copied text.  Sorry.  It's not working with a bracket as well.
Above listed Query listed in the text box of the report.  I need an outcome "No Data Found".   Instead I got "#ERROR"
RPT-Screen-Shot.docx
I found problem: Source Query "qry-loans-review-before month-end" reflects following expression: "Origination Date Between [Type the beginning date:] And [Type the ending date:]"

I noticed based on this and other reports that such criteria does not allow me to have message "No Data Found".  

Question: Is there any way to show this message based on query listed in previous comment (ID: 38585960) with expression: "Origination Date Between [Type the beginning date:] And [Type the ending date:]"

Please note that no date expressions allow me to show message "No Data Found"

I have another expression in the different report: "IIf([ORIGIN DATE]>[Exclude Last 12M New  Loans “Enter Date”],1) "- same thing.  Once I remove date expressions, problem will be solved.  My issues is that I have to keep these expressions.  Is there any way around to resolve the problem
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot.  Based on your help I put following code To OnOpen Event
Private Sub Report_Open(Cancel As Integer)
    If Me.HasData Then
    Me.Detail1.Visible = True
    Me.ReportFooter4.Visible = True
    Else
    If Me.HasData = False Then
    Me.lblnodata.Visible = True
    Me.Detail1.Visible = False
    Me.ReportFooter4.Visible = False
    End If
    End If
End Sub

Open in new window

Excellent Help!!!