Expression with multiple parameters

[techexpressinc]

My error message is the "wrong number of arguements".

I am trying to create a new column with two criteria being met.
1) The column "Activity Type-SA not equal to "Travel"
and
2) The column "Rate Value = "1".


FaceTime: ((IIf([ACTIVITY TYPE-SA]<>"Travel") AND ([RATE VALUE] ="1")),[TIME-SA],"0")

Attached is a screen print.
Thanks for help formatting this one.
Russ

 

[SOS]

If RateValue is numeric then leave off the quotes:

FaceTime: IIf([ACTIVITY TYPE-SA]<>"Travel" AND [RATE VALUE]=1,[TIME-SA],0)

And you don't need all of the parentheses and no quotes on the other 0.

 

[techexpressinc]

SOS - your good code passed the syntax test but failed to deliver the data. The query ran smooth but the result is an ugly "error#" instead of amounts.
Can you fine tune the code some?
Thx Russ
attached is a snapshot

 

[SOS]

Are you sure that the field name is RateValue and not just RATE? The screenshot seems to appear that it is RATE and not RateValue.

 

[techexpressinc]

SOS - Yes, I squeezed the columns to keep it all in one window. Attached is another snapshot.
Russ

 

[SOS]

actually I guess you may need to use "0" for the last part so that it will accept text as well as numbers.

 

[techexpressinc]

I tried it like this no luck??
FaceTime: IIf([ACTIVITY TYPE-SA]<>"Travel" And [RATE VALUE]=1,[TIME-SA],"0")

 

[SOS]

Any chance of posting the database?

 

[techexpressinc]

not too easily size and confidential data

 

[SOS]

not too easily size and confidential data

What if you remove all of the data and then do a compact and repair and then zip it?

 

[techexpressinc]

Re: Expression with multiple parameters - zipped db

I just put the needed tables and part of the data there, the query within the db is attached.
Russ

 

[SOS]

Bummer - I don't have 2007 at work. I'll have to try when I get home. I'll try to remember to look as I typically have been avoiding forums when I get home as I have so little time before I go to bed.

 

[SOS]

Your design is not really optimal, but here's the answer:

FaceTime: IIf([ACTIVITY TYPE-SA]<>"Travel" And [RATE VALUE]="1",[TIME-SA],0)

since Rate Value is text (not sure why) it needs quotes but the value of Time-SA is numeric so we need no quotes around the zero.

 

[techexpressinc]

I will try your solution today and let you know. Thanks Russ

 

[techexpressinc]

SOS - Thanks for sticking with this tricky formula. I am sure I will run into more situations like this with what I am doing now, I do enjoy good results thou.
Have a good day, and kudos to you and your Access knowledge.
Russ - Data Specialist Manager at www.scanfw.org.

 

[SOS]

Glad to be able to help.