Which Function...

[Dos Equis]

will allow me to calculate a value on a form depending on the
information in two other fields?

For instance:

If ad_Advertiser can have 3 values Private party, Commercial or
National and each has a different rate and the cost of an ad is
dependent on the number of words in the ad, i would expect to write
something like this:

CCUR(IIF([ad_Advertiser]=Private party, 3.50+(([wordcount]-10)*.35);
IIF([ad_Advertiser]=Commercial, 4.50+(([wordcount]-10)*.45);
IIF([ad_Advertiser]=National, 6.50+(([wordcount]-10)*.65)

I know that the IIF function is not correct but not sure what to use
in its place. any help would be appreciated. BTW, the total will be
stored in the field for archive data and so that invoices are
accurate.

I am using Access 2000.

Thanks for any help you can provide.

Byron

 

[SteveS]

Hi Byron,

You can use the IIF() function or you can write your own function.

I would write a function to do the calculation because every time the rates
change, you will have to edit this query (or lose money - rates never seem to
go down <bg>). Using code you can look up the per word rates from a table. A
form could be used to change the per word rates as necessary.


Note that in the IIF() function I used a zero as the last rate. If the
[ad_Advertiser] entry is ever misspelled, an amount of $0 will be displayed
(which should never happen).

Here is the IIF()......

TheCost: CCUR(IIF([ad_Advertiser]="Private party",
3.50+(([wordcount]-10)*.35), IIF([ad_Advertiser]="Commercial",
4.50+(([wordcount]-10)*.45), IIF([ad_Advertiser]="National",
6.50+(([wordcount]-10)*.65), 0))))


And just for fun, here is another formula to calculate the cost:

Cost2: CCUR(([ad_Advertiser]="Private party")*-(3.50+(([wordcount]-10)*.35))
+ ([ad_Advertiser]="Commercial")*-( 4.50+(([wordcount]-10)*.45)) +
([ad_Advertiser]="National")*-( 6.50+(([wordcount]-10)*.65)))



HTH

 

[Douglas J. Steele]

Actually, the Switch function may be more appropriate:

TheCost: CCur(Switch([ad_Advertiser]="Private party",
3.50+(([wordcount]-10)*.35), [ad_Advertiser]="Commercial",
4.50+(([wordcount]-10)*.45), [ad_Advertiser]="National",
6.50+(([wordcount]-10)*.65)))

That will return Null if ad_Advertiser isn't one of the 3 values. If you
want zero instead, use

TheCost: CCur(Nz(Switch([ad_Advertiser]="Private party",
3.50+(([wordcount]-10)*.35), [ad_Advertiser]="Commercial",
4.50+(([wordcount]-10)*.45), [ad_Advertiser]="National",
6.50+(([wordcount]-10)*.65))))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Byron,

You can use the IIF() function or you can write your own function.

I would write a function to do the calculation because every time the
rates
change, you will have to edit this query (or lose money - rates never seem
to
go down <bg>). Using code you can look up the per word rates from a table.
A
form could be used to change the per word rates as necessary.


Note that in the IIF() function I used a zero as the last rate. If the
[ad_Advertiser] entry is ever misspelled, an amount of $0 will be
displayed
(which should never happen).

Here is the IIF()......

TheCost: CCUR(IIF([ad_Advertiser]="Private party",
3.50+(([wordcount]-10)*.35), IIF([ad_Advertiser]="Commercial",
4.50+(([wordcount]-10)*.45), IIF([ad_Advertiser]="National",
6.50+(([wordcount]-10)*.65), 0))))


And just for fun, here is another formula to calculate the cost:

Cost2: CCUR(([ad_Advertiser]="Private
party")*-(3.50+(([wordcount]-10)*.35))
+ ([ad_Advertiser]="Commercial")*-( 4.50+(([wordcount]-10)*.45)) +
([ad_Advertiser]="National")*-( 6.50+(([wordcount]-10)*.65)))



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

will allow me to calculate a value on a form depending on the
information in two other fields?

For instance:

If ad_Advertiser can have 3 values Private party, Commercial or
National and each has a different rate and the cost of an ad is
dependent on the number of words in the ad, i would expect to write
something like this:

CCUR(IIF([ad_Advertiser]=Private party, 3.50+(([wordcount]-10)*.35);
IIF([ad_Advertiser]=Commercial, 4.50+(([wordcount]-10)*.45);
IIF([ad_Advertiser]=National, 6.50+(([wordcount]-10)*.65)

I know that the IIF function is not correct but not sure what to use
in its place. any help would be appreciated. BTW, the total will be
stored in the field for archive data and so that invoices are
accurate.

I am using Access 2000.

Thanks for any help you can provide.

Byron

 

[Dos Equis]

Actually, the Switch function may be more appropriate:

TheCost: CCur(Switch([ad_Advertiser]="Private party",
3.50+(([wordcount]-10)*.35), [ad_Advertiser]="Commercial",
4.50+(([wordcount]-10)*.45), [ad_Advertiser]="National",
6.50+(([wordcount]-10)*.65)))

That will return Null if ad_Advertiser isn't one of the 3 values. If you
want zero instead, use

TheCost: CCur(Nz(Switch([ad_Advertiser]="Private party",
3.50+(([wordcount]-10)*.35), [ad_Advertiser]="Commercial",
4.50+(([wordcount]-10)*.45), [ad_Advertiser]="National",
6.50+(([wordcount]-10)*.65))))

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Hi Byron,

You can use the IIF() function or you can write your own function.

I would write a function to do the calculation because every time the
rates
change, you will have to edit this query (or lose money - rates never seem
to
go down <bg>). Using code you can look up the per word rates from a table.
A
form could be used to change the per word rates as necessary.

Note that in the IIF() function I used a zero as the last rate. If the
[ad_Advertiser] entry is ever misspelled, an amount of $0 will be
displayed
(which should never happen).

Here is the IIF()......

TheCost: CCUR(IIF([ad_Advertiser]="Private party",
3.50+(([wordcount]-10)*.35), IIF([ad_Advertiser]="Commercial",
4.50+(([wordcount]-10)*.45), IIF([ad_Advertiser]="National",
6.50+(([wordcount]-10)*.65), 0))))

And just for fun, here is another formula to calculate the cost:

Cost2: CCUR(([ad_Advertiser]="Private
party")*-(3.50+(([wordcount]-10)*.35))
+ ([ad_Advertiser]="Commercial")*-( 4.50+(([wordcount]-10)*.45)) +
([ad_Advertiser]="National")*-( 6.50+(([wordcount]-10)*.65)))

HTH

will allow me to calculate a value on a form depending on the
information in two other fields?
For instance:
If ad_Advertiser can have 3 values Private party, Commercial or
National and each has a different rate and the cost of an ad is
dependent on the number of words in the ad, i would expect to write
something like this:
CCUR(IIF([ad_Advertiser]=Private party, 3.50+(([wordcount]-10)*.35);
IIF([ad_Advertiser]=Commercial, 4.50+(([wordcount]-10)*.45);
IIF([ad_Advertiser]=National, 6.50+(([wordcount]-10)*.65)
I know that the IIF function is not correct but not sure what to use
in its place. any help would be appreciated. BTW, the total will be
stored in the field for archive data and so that invoices are
accurate.
I am using Access 2000.
Thanks for any help you can provide.
Byron

Thank you both. I was under the impression the IIF function was
"either or", not "one of the following..." Now to study the switch
function and understand why it may be better... Thanks again.

Byron