need help with expression

[Slez via AccessMonster.com]

I'm trying to develop an expression in a query that will return the value if
the record meets some criteria, or return a value of 0 (zero) if it doesn't
meet it. Because of the wide range of records, I am trying to utilize
wildcard characters. I'm doing this rather than just filtering out the other
records, because the other records carry some value in other query fields for
the report that this query serves.

My affected fields are: [Quantity] & [ProductDescription] & possibly
[ProductClass]
My intent was to create a calculated field called "CabQty" (representing
Cabinet Quantity)

If the [ProductDescription] = Like "Base*" Or Like "Wall*" Or Like "Tall*" Or
"Cart", then I'd like it to return the value that is in the [Quantity] field.
For the other records in the query that don't meet this criteria, I'd like it
to make the value 0.

In the Field box of the query I have been successful with the expression -
CabQty: IIf([ProductClass]="Base.Door Only",[Quantity],0) - However, when I
try adding the wildcard asterisk or "Like/Or" operators, I start to get error
messages.

I appreciate any help or advice!

 

[Jeff Boyce]

I suppose one approach would be to write one query that uses those Like xxx*
or Like yyy* criteria to select ONLY those records, and "calculate" your
value. Then write a second query that uses Not Like xxx* And Not Like yyy*
.... a the criteria to select all the others, and do your calculation (i.e.,
0) again. Finally, use a UNION query to combine the output of each of the
first two queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Slez via AccessMonster.com]

That prompts me to ask an additional question. Would I be better off trying
to develop an expression in the control on the report?
Thanks again!
Slez

I suppose one approach would be to write one query that uses those Like xxx*
or Like yyy* criteria to select ONLY those records, and "calculate" your
value. Then write a second query that uses Not Like xxx* And Not Like yyy*
... a the criteria to select all the others, and do your calculation (i.e.,
0) again. Finally, use a UNION query to combine the output of each of the
first two queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to develop an expression in a query that will return the value
if

[quoted text clipped - 28 lines]

I appreciate any help or advice!

 

[Jeff Boyce]

Hard to tell, not nearly enough information.

Regards

Jeff Boyce
Microsoft Office/Access MVP

That prompts me to ask an additional question. Would I be better off
trying
to develop an expression in the control on the report?
Thanks again!
Slez

I suppose one approach would be to write one query that uses those Like
xxx*
or Like yyy* criteria to select ONLY those records, and "calculate" your
value. Then write a second query that uses Not Like xxx* And Not Like
yyy*
... a the criteria to select all the others, and do your calculation
(i.e.,
0) again. Finally, use a UNION query to combine the output of each of the
first two queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to develop an expression in a query that will return the
value
if

[quoted text clipped - 28 lines]

I appreciate any help or advice!