DCount problem

[AnhCVL via AccessMonster.com]

Hi all,

Can anyone tell me why this codes doesn't work?

=DCount("[Status] = 'InStock'","Inventory Table","[Name] = 'hammer'")

I have this code on a textbox and it return all value that associate with
hammers regardless of the status ( Sold/InStock), any suggestion?

Thank
Mark

 

[Douglas J. Steele]

DCount doesn't work that way. Try:

=DCount("*", "Inventory Table", "[Name] = 'hammer' And [Status] =
'InStock'")

Incidentally, Name is not a good choice for a field name. For a
comprehensive lists of names to avoid (as well as a link to a free utility
that will check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

 

[AnhCVL via AccessMonster.com]

Hi Douglas,

Thanks for the quick response, I've tried your suggestion as:

=DCount("*", "Inventory Table", "[Tname] = 'hammer' And [Status] =
'InStock'")

the [Name] was [Tname], sorry for the typo, this resulted in "#Error", I had
it on the page footer, I've also tried to put it under report footer but
received the same error, any idea why?

Thanks
Mark

DCount doesn't work that way. Try:

=DCount("*", "Inventory Table", "[Name] = 'hammer' And [Status] =
'InStock'")

Incidentally, Name is not a good choice for a field name. For a
comprehensive lists of names to avoid (as well as a link to a free utility
that will check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

Hi all,

[quoted text clipped - 7 lines]

Thank
Mark

 

[Douglas J. Steele]

The only thing I can think of is to put square brackets around the table
name since it contains spaces:

=DCount("*", "[Inventory Table]", "[Tname] = 'hammer' And [Status]
='InStock'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,

Thanks for the quick response, I've tried your suggestion as:

=DCount("*", "Inventory Table", "[Tname] = 'hammer' And [Status] =
'InStock'")

the [Name] was [Tname], sorry for the typo, this resulted in "#Error", I
had
it on the page footer, I've also tried to put it under report footer but
received the same error, any idea why?

Thanks
Mark

DCount doesn't work that way. Try:

=DCount("*", "Inventory Table", "[Name] = 'hammer' And [Status] =
'InStock'")

Incidentally, Name is not a good choice for a field name. For a
comprehensive lists of names to avoid (as well as a link to a free utility
that will check your application for compliance), see what Allen Browne
has
at http://www.allenbrowne.com/AppIssueBadWord.html

Hi all,

[quoted text clipped - 7 lines]

Thank
Mark

 

[AnhCVL via AccessMonster.com]

I've tried but it shows same "#Error", I'll do some more research on it and
will take it slow, if you come accross something similiar, please keep me
update and if I'll find out and get it to work, I'll inform you. Thanks for
all the suggestions Douglas.

Mark

The only thing I can think of is to put square brackets around the table
name since it contains spaces:

=DCount("*", "[Inventory Table]", "[Tname] = 'hammer' And [Status]
='InStock'")

Hi Douglas,

[quoted text clipped - 27 lines]

 

[Douglas J. Steele]

One thing to verify is that Tname and Status are actually text fields. If
you used the Lookup Wizard field for one or both of the fields, you find
that the field doesn't actually contain what you think it does (This is but
one of the many reasons why many of us abhor lookup fields: see
http://www.mvps.org/access/lookupfields.htm at "The Access Web" for some
others)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've tried but it shows same "#Error", I'll do some more research on it
and
will take it slow, if you come accross something similiar, please keep me
update and if I'll find out and get it to work, I'll inform you. Thanks
for
all the suggestions Douglas.

Mark

The only thing I can think of is to put square brackets around the table
name since it contains spaces:

=DCount("*", "[Inventory Table]", "[Tname] = 'hammer' And [Status]
='InStock'")

Hi Douglas,

[quoted text clipped - 27 lines]

Thank
Mark

 

[AnhCVL via AccessMonster.com]

Hi Douglas,

All the fields I am dealing with are text, I manually specified them and
double checked them. Funny thing is that after I tried and played around with
it, I got bored and decided to do try the same code from a fresh new report
so I've created a new report, totally blank, drop a text box and employed the
code, it work perfectly. The very same codes I place on the textbox on my
existing report which I had lots of other information on there, showing
"#Error", I am thinking spend a little time to move or recreate all the info
from my other report to the new report. I first thought it was the trailing
spaces and type and verified, retyped but wasn't the case. I copy and pasted
the same codes which shown "#Error" on one from to the new form, it show me
the correct number. it's weird isn't it? Thanks for all the helps Douglas.

Mark

One thing to verify is that Tname and Status are actually text fields. If
you used the Lookup Wizard field for one or both of the fields, you find
that the field doesn't actually contain what you think it does (This is but
one of the many reasons why many of us abhor lookup fields: see
http://www.mvps.org/access/lookupfields.htm at "The Access Web" for some
others)

I've tried but it shows same "#Error", I'll do some more research on it
and

[quoted text clipped - 16 lines]