Creating A New Column In My Query

[AMW1106]

I am trying to add a new column in my Access query that will display a "Charged" status if the Fee_Amount was $5.00 and a "No Charge" status if the Fee_Amount was $0.00. This should just be an additional column in my query. In Brio I just use Decode but with my lack of Access knowledge I'm not sure how this is done. Any suggestions?

 

[jdegeorge]

You can add a new EXPRESSION in a blank column like using the IMMEDIATE IF function called IIF().

Assuming that it could only be 5.00 or 0.00, it would look something like this:

Expr1:IIF([FEE_AMOUNT]=5,"Charged","No Charge&quot

The syntax is IIf(expr, truepart, falsepart)

[FEE_AMOUNT]=5 is the expression, "Charged" is what you want to display when the expression is TRUE, "No Charge" is what you want to display when the expression is FALSE.

If there are other options for FEE_AMOUNT besides $5.00 and $0.00, this won't work as written.

Jim "Get it right the first time, that's the main thing..."

 

[AMW1106]

Thank you!!! It worked perfectly.

 

[BanditWk]

These are so much fun!! In the next blank column, in Field type:

IIf([Fee_Amount]=5,"Charged","No Charge&quot

Of course, this field will need a name - if you don't create one, it will automatically put one in like:

Expr1: IIf([Fee_Amount]=5,"Charged","No Charge&quot

When you might want:

Status: IIf([Fee_Amount]=5,"Charged","No Charge&quot

HTH! Roy McCafferty
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"I do this because I know I can and I'm bored at work - no need to send flowers, wine, dinner, or coffee... Just send me a smile to show me that I've helped." ~ Roy McCafferty, seen on a corner in Las Vegas with a sign that said, 'Will work

 

[AMW1106]

Hi again, I am now using this field in my Report and need to do a count on this field. I am using the DCount function but am getting #Error. Am I not able to do a DCount on an Expression?

My Expr in the query looks like this:
Expr1: IIf([FEE_AMOUNT]=5,"Charged","No Charge&quot

My DCount formula in my report looks like this:
=DCount("[Expr1]","PLL FEE Query ","[Expr1]='No Charge'&quot

Also, when I try to rename expr1 (naming it Fee Type), my query and report is treating as a prompt and prompts me for the Fee Type. I left it named Expr1. This seems strange.

Thank you again for your help!!!

 

[jdegeorge]

If you substitute Expr1 with Fee Type make sure you leave the colon at the end. Then, when you refer to Fee Type in a query, form or report, you have to but boxes around it like [Fee Type].

I always use COUNT in reports. Try COUNT([Fee Type]).

Jim "Get it right the first time, that's the main thing..."

 

[BanditWk]

Where's the DCount at? If it's in another form or report that doesn't have this as an underlying query, you not need to be referencing and do a count as jdegeorge has requested.

JDEGEORGE: How the heck do you get those cute icons in your signature?

Thx Roy McCafferty
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"I do this because I know I can and I'm bored at work - no need to send flowers, wine, dinner, or coffee... Just send me a smile to show me that I've helped." ~ Roy McCafferty, seen on a corner in Las Vegas with a sign that said, 'Will work

 

[AMW1106]

Thank you, it worked. But I'm still unable to count the number of "No Charge" types. Is there a way to count the "No Charge" types using COUNT rather than DCOUNT? I only want a count of the "No Charge" type.

It seems like the DCount formula does not like the name of my query or something because when I change it to read the name of the table rather than the name of the query I no longer get the #Error and get a count of something - unfortunatley not the correct count though.

Thanks again!

 

[jdegeorge]

AMW:

How did you format the field for counting "Charged". You should be able to just count "No Charge" as the value of a 2nd field. Paste your code, please.


Roy:
Before you submit your post, hit the PREVIEW button. There's a whole slew of codes you can add to have icons. "Get it right the first time, that's the main thing..."

 

[AMW1106]

Hi, my code for DCount is below.

=DCount("[Fee Type]","PLL Fee Query","[Fee Type]='No Charge'&quot

When I replace the "PLL Fee Query" with the actual table name rather than the Query name I get a count not the #Error but also not the correct count. Can I use COUNT to count something specific?

 

[jdegeorge]

I just created a table called tblFees with 1 field called Fee_Amount. I added 12 items, 9 = $5.00 and 3 = $0.00.

I created a query called PLL Fee Query with 1 field expression

Fee Type: IIf([Fee_Amount]=5,"Charged","No Charge&quot

Then I created a report with Fee Type in the DETAIL SECTION and 2 formulas in the PAGE FOOTER:

=DCount("[Fee Type]","PLL Fee Query","[Fee Type]='No Charge'&quot

to count the NO CHARGE items

and

=DCount("[Fee Type]","PLL Fee Query","[Fee Type]='Charged'&quot

to count the CHARGED items.

It worked fine.

I tried to keep the field, query names the same as your so you can almost just plug this in.

Changing the reference from the query to the table won't work the way you tried to do it, but you don't need to.

Give this a whirl...

Jim
"Get it right the first time, that's the main thing..."