Just for now, try:
=DCount("*", "tblIssuesandconcerns", "[Year] = 2005")
Then try:
=DCount("*", "tblIssuesandconcerns", "[Year] = 1")
Which one gives the results you expect?
Sorry, I think I must not have explained what happened properly. I
think the Error message has came up with me doing something silly.
I have put the code
=DCount("[Year]","tblissuesandconcerns","tblissuesandconcerns![Year]
"=Nz([cboyear].[Column](1),0))
Into the control source and it is still bringing back 0.
Do you think I should remove the AutoNumber column from
tblissuesandconcerns?
Allen Browne wrote:
Okay, it looks like the combo is bound to a number that is not the
year,
i.e. the 1 or 2 or whatever is in the hidden column. That explains why
the
expression in your form returns no value, i.e. you don't have any
records
for year 1.
The expression starting with the question mark, you tried it in the
Immediate Window (not the Control Source of a text box)? And it gave
you
#Error? I don't understand that. You could try simplifying it to:
? DCount("*", "tblIssuesandconcerns", "[Year] = 2005")
No point trying anything else until we get that working.
If you did try putting the expression into a text box, it must start
with
"=" instead of "?". And if Access adds square brackets around
[Column],
that's fine.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
It was option a)
I tried that code, but its bringing back #Error now. (No ? as it
sometimes does). Where it says Column in the code Access
automatically
is putting square brackets round it.
Colin
Allen Browne wrote:
When you asked for the result in the Immediate window, did you get:
a) 1 i.e. just the digit from the ID, or
b) 2005 i.e. just the year, or
c) 2005 - 1 i.e. both numbers plus the dash?
If (a), try:
=DCount("[Year]", "tblissuesandconcerns",
"tblissuesandconcerns![Year] " = Nz([cboyear].Column(1),0))
If (b), open the Immediate Window (Ctrl+G) and enter this (one
line):
? DCount("[Year]", "tblissuesandconcerns",
"tblissuesandconcerns![Year] = 2005")
Hi
Row Source Type - Table/Query
Row Source - SELECT tblyear.ID, tblyear.Year FROM tblyear;
Column Count - 2
Column Widths - 0cm;2.54cm
The imediate window gave back the number which corresponds to the
year
2005 - 1
2006 - 2
etc
Data type of the year field is number.
Thanks
Colin
Allen Browne wrote:
No. It won't work better in another context. We need to trace
what's
wrong.
If you open tblissuesandconcerns in design view, what data type
is
the
Year
field?
What is in these properties of the combo:
Row Source Type
Row Source
Column Count
Column Widths
Open the form so that it should show a result, and shows
nothing.
Open the Immediate Window (Ctrl+G).
Enter this:
? Forms![Form1]!cboYear
substituting your form name for Form1.
What response to you get?
Hi Allen
Thanks for your reply. I'm not getting any errors, but
nothing
seems
to be happening with it yet. Would it work better if I was
putting
it
into a VB code builder rather than the control source? If so,
where
would I put it? AtferUpdate etc and for what item on form?
Thanks again
Colin
Allen Browne wrote:
Concatenate the value in the combo into the 3rd argument,
like
this:
=DCount("[Year]", "tblissuesandconcerns",
"tblissuesandconcerns![Year] " = [cboyear])
If the combo is Null (nothing selected), the 3rd argument
resolves
to
just:
tblissuesandconcerns![Year] =
and of course Access can't make sense of that, so you get
#Error.
To
avoid
that, use Nz():
=DCount("[Year]", "tblissuesandconcerns",
"tblissuesandconcerns![Year] " = Nz([cboyear],0))
If the Year field it a Text type (not a Number type), you
need
to
use
extra
quotes:
=DCount("[Year]", "tblissuesandconcerns",
"tblissuesandconcerns![Year] """ = [cboyear] & """")
If the quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html
BTW, Year is a function name in VBA, and known to cause
problems.
You
should
get away with it in this context okay (with the square
brackets
and
explicit
table names), but it can cause code in your form to fail in
weird
ways.
For
a list of the names to avoid, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
I'll try and explain this as best I can.
I am trying to get a text box to display the number of
"Issues
and
Concerns" registered in a particular year (txttotalissues).
I
have
a
form with a DCount function in the text box as follows in
the
control
source
=DCount("Year","tblissuesandconcerns","tblissuesandconcerns!Year
=cboyear")
cboyear is a combo box that is controlled by a table that
has
years
in
it. Also this table has an ID which is the primary key.
I am having a few problems with it. When I select the year
txttotalissues does nothing. I then added another text box
(yearval)
which displayed the value that cboyear gave out. So this
changes
when
I select different years. But it displays 1, 2, 3 etc
rather
than
2005, 2006, 2007. So I changed the control source to the
following
=DCount("Year","tblissuesandconcerns","tblissuesandconcerns!Year
=yearval.Value")
I did this as the form was running, and as I had cboyear
selected
to
2006, the txttotalissues changed to show me the value for
2006,
but
when I then changed the year in cboyear, txttotalissues
remained
the
same.
I figure I'm doing something silly, but not sure what it
is.
Can
anyone please help?
Cheers
Colin
