DCount Wildcard???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've created a query (to create a report) that pulls a variety of
"descriptions" (construction types).

I'm using the following expression to count the number of times a particular
description is returned from a query. In this case, the description type is
"RENO":

=Sum(Abs([Description]="RENO"))

So far everything is working.... except, I have some descriptions that start
with the description type and then specify - for example:

RENO: ADD DOORWAY
RENO: BASEMENT
RENO: REMOVE WALL

When I use my expression,
=Sum(Abs([Description]="RENO"))
It doesn't acknowledge anything other than what is actually in the
expression... that is, it doesn't recognize that there are different "RENO"s.
Is there a wild card that I can use in my expression so that all of the
"RENO"s will be retrieved?
Hope this makes sense.

Any help would be greatly appreciated.

Thanks.
Barkaroo.
 
BARKAROO said:
I've created a query (to create a report) that pulls a variety of
"descriptions" (construction types).

I'm using the following expression to count the number of times a
particular
description is returned from a query. In this case, the description type
is
"RENO":

=Sum(Abs([Description]="RENO"))

So far everything is working.... except, I have some descriptions that
start
with the description type and then specify - for example:

RENO: ADD DOORWAY
RENO: BASEMENT
RENO: REMOVE WALL

When I use my expression,
=Sum(Abs([Description]="RENO"))
It doesn't acknowledge anything other than what is actually in the
expression... that is, it doesn't recognize that there are different
"RENO"s.
Is there a wild card that I can use in my expression so that all of the
"RENO"s will be retrieved?
Hope this makes sense.

Any help would be greatly appreciated.
Click to expand...

Try this:

=Sum(Abs([Description] Like"RENO*"))

Tom Lake
 
BARKAROO said:
I've created a query (to create a report) that pulls a variety of
"descriptions" (construction types).

I'm using the following expression to count the number of times a particular
description is returned from a query. In this case, the description type is
"RENO":

=Sum(Abs([Description]="RENO"))

So far everything is working.... except, I have some descriptions that start
with the description type and then specify - for example:

RENO: ADD DOORWAY
RENO: BASEMENT
RENO: REMOVE WALL

When I use my expression,
=Sum(Abs([Description]="RENO"))
It doesn't acknowledge anything other than what is actually in the
expression... that is, it doesn't recognize that there are different "RENO"s.
Is there a wild card that I can use in my expression so that all of the
"RENO"s will be retrieved?
Click to expand...


To use a wildcard xomparison, you have to use the Like
operator instead of =

=Sum(Abs([Description] LIKE "RENO*"))
 
Duane, what does the "4" in your expresson do?

Duane Hookom said:
Or....
=Sum(Abs(Left([Description],4)="RENO"))


--
Duane Hookom
MS Access MVP
--

BARKAROO said:
I've created a query (to create a report) that pulls a variety of
"descriptions" (construction types).

I'm using the following expression to count the number of times a
particular
description is returned from a query. In this case, the description type
is
"RENO":

=Sum(Abs([Description]="RENO"))

So far everything is working.... except, I have some descriptions that
start
with the description type and then specify - for example:

RENO: ADD DOORWAY
RENO: BASEMENT
RENO: REMOVE WALL

When I use my expression,
=Sum(Abs([Description]="RENO"))
It doesn't acknowledge anything other than what is actually in the
expression... that is, it doesn't recognize that there are different
"RENO"s.
Is there a wild card that I can use in my expression so that all of the
"RENO"s will be retrieved?
Hope this makes sense.

Any help would be greatly appreciated.

Thanks.
Barkaroo.
Click to expand...
Click to expand...
 
Duane, what does the "4" in your expresson do?
Duane Hookom said:
Or....
=Sum(Abs(Left([Description],4)="RENO"))
Click to expand...
Click to expand...

I'm not Duane but Left([Description],4) chooses the leftmost 4 characters
out of the Description field. The expression

Left([Description],4)="RENO"

is True (-1) if the leftmost four characters are "RENO" and False (0)
otherwise. The Abs takes the -1 and makes it positive
and leaves the 0 alone.

So we have

Abs(Left([Description],4)="RENO")

which is 1 if Description starts with "RENO"

and 0 if not. The Sum will sum up all the ones and therefore give a count
of all the fields that start with "RENO".

Tom Lake
 
Thanks Tom. I couldn't have explained it better :-)

--
Duane Hookom
MS Access MVP


Tom Lake said:
Duane, what does the "4" in your expresson do?

Duane Hookom said:
Or....
=Sum(Abs(Left([Description],4)="RENO"))
Click to expand...
Click to expand...

I'm not Duane but Left([Description],4) chooses the leftmost 4 characters
out of the Description field. The expression

Left([Description],4)="RENO"

is True (-1) if the leftmost four characters are "RENO" and False (0)
otherwise. The Abs takes the -1 and makes it positive
and leaves the 0 alone.

So we have

Abs(Left([Description],4)="RENO")

which is 1 if Description starts with "RENO"

and 0 if not. The Sum will sum up all the ones and therefore give a count
of all the fields that start with "RENO".

Tom Lake
Click to expand...
 
Thanks.

Duane Hookom said:
Thanks Tom. I couldn't have explained it better :-)

--
Duane Hookom
MS Access MVP


Tom Lake said:
Duane, what does the "4" in your expresson do?

:

Or....
=Sum(Abs(Left([Description],4)="RENO"))
Click to expand...

I'm not Duane but Left([Description],4) chooses the leftmost 4 characters
out of the Description field. The expression

Left([Description],4)="RENO"

is True (-1) if the leftmost four characters are "RENO" and False (0)
otherwise. The Abs takes the -1 and makes it positive
and leaves the 0 alone.

So we have

Abs(Left([Description],4)="RENO")

which is 1 if Description starts with "RENO"

and 0 if not. The Sum will sum up all the ones and therefore give a count
of all the fields that start with "RENO".

Tom Lake
Click to expand...
Click to expand...
 
Back
Top