Text box

[Jeffery]

I would like to run a query from an unbound text box in a form to show a
value. When the form is open instead of a value I get a #name? display.

The control source for the text box is as follows:

=IIf([qryIncidentsCountClosed]![Count] Is
Null,"0",[qryIncidentsCountClosed]![Count]).

The underlying query (qryIncidentsCountClosed) is:

SELECT Count(tblIncident.IncidentNum) AS [Count]
FROM tblIncident
WHERE (((tblIncident.Critical)=-1) AND ((tblIncident.DateClosed) Is Not Null
And (tblIncident.DateClosed)>(Date()-365)) AND ((tblIncident.[Customer
Number])=[Forms]![Customers]![Customer Number]));

Any suggestions on how to get this to work?

I would like to display a count of open and closed incidents for the
customer when the form is open. The above stuff is for the count of closed
incidents.

Thanks
Jeff G

 

[Ken Snell [MVP]]

You cannot "reference" a query in a control source the way you're trying to
do.

Use a DCount function to get the result instead:

=DCount("IncidentNum", "qryIncidentsCountClosed", "Critical=-1 AND
DateClosed Is Not Null And DateClosed>(Date()-365) AND [Customer Number]=" &
[Forms]![Customers]![Customer Number])

 

[Graham R Seach]

Jeffery,

Untested, but it should work.

=Nz(DLookup("[Count]", "qryIncidentsCountClosed"), 0)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Jeffery]

Ken/Graham-
Thanks for the help. I'll give it a try!

-----Original Message-----
You cannot "reference" a query in a control source the way you're trying to
do.

Use a DCount function to get the result instead:

=DCount

("IncidentNum", "qryIncidentsCountClosed", "Critical=-1
AND

DateClosed Is Not Null And DateClosed>(Date()-365) AND [Customer Number]=" &
[Forms]![Customers]![Customer Number])

--

Ken Snell
<MS ACCESS MVP>

news:5897F37B-0788-4EB3-A394-

(e-mail address removed)...

I would like to run a query from an unbound text box in a form to show a
value. When the form is open instead of a value I get a #name? display.

The control source for the text box is as follows:

=IIf([qryIncidentsCountClosed]![Count] Is
Null,"0",[qryIncidentsCountClosed]![Count]).

The underlying query (qryIncidentsCountClosed) is:

SELECT Count(tblIncident.IncidentNum) AS [Count]
FROM tblIncident
WHERE (((tblIncident.Critical)=-1) AND

((tblIncident.DateClosed) Is Not

Null

And (tblIncident.DateClosed)>(Date()-365)) AND ((tblIncident.[Customer
Number])=[Forms]![Customers]![Customer Number]));

Any suggestions on how to get this to work?

I would like to display a count of open and closed incidents for the
customer when the form is open. The above stuff is for the count of closed
incidents.

Thanks
Jeff G

.