=Iif([SHIPTERM]="LDP",Round([INVQTY]*[LDPPRICE],2),Round([INVQTY]*[FOBPRICE],
2))

Please help me frame this in a Access project data base. i get error
validating formula. Help Help

You've probably figured out by now that you can use the Case statement here
as well.

CASE [SHIPTERM] WHEN 'LDP' THEN ROUND([INVQTY] * [LDPPRICE], 2) ELSE
ROUND([INVQTY] * [FOBPRICE], 2) END

I'm not 100% sure, but you might be able to simplify this a bit further by
putting the ROUND outside...just not sure if SQL Server will handle this
syntax, but give it a try:

ROUND([INVQTY] * CASE [SHIPTERM] WHEN 'LDP' THEN [LDPPRICE] ELSE
[FOBPRICE] END, 2)



Rob

"gmenon100" <u24255@uwe> wrote in message news:637067ac705fb@uwe...
> =Iif([SHIPTERM]="LDP",Round([INVQTY]*[LDPPRICE],2),Round([INVQTY]*[FOBPRICE],
> 2))
>
> Please help me frame this in a Access project data base. i get error
> validating formula. Help Help

SURELY IT WORKED!! THANKS.

Robert Morley wrote:
>You've probably figured out by now that you can use the Case statement here
>as well.
>
> CASE [SHIPTERM] WHEN 'LDP' THEN ROUND([INVQTY] * [LDPPRICE], 2) ELSE
>ROUND([INVQTY] * [FOBPRICE], 2) END
>
>I'm not 100% sure, but you might be able to simplify this a bit further by
>putting the ROUND outside...just not sure if SQL Server will handle this
>syntax, but give it a try:
>
> ROUND([INVQTY] * CASE [SHIPTERM] WHEN 'LDP' THEN [LDPPRICE] ELSE
>[FOBPRICE] END, 2)
>
>Rob
>
>> =Iif([SHIPTERM]="LDP",Round([INVQTY]*[LDPPRICE],2),Round([INVQTY]*[FOBPRICE],
>> 2))
>>
>> Please help me frame this in a Access project data base. i get error
>> validating formula. Help Help

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...erver/200607/1

Hi Rob,

I need help in just these couple of issues:

1. In the same project, which has a form "ENTRY FORM" I have used the
fields [invqty], [invprice], [invval], [poqty], [poprice] [poval], where
invval = invqty * invprice.....poval = [poqty]*[poprice].....
I am able to calculate the value, however, the invval is not displayed on the
form unless I save the record. How can I make this work so that I dont need
to exit the record and come back to check the invval. Please help.

2. On the same form, i have field for [STYLE], which looksup for a value from
the STYLE table. I have enabled a macro to open the [STYLE MASTER] Form when
NOT IN LIST. However, after adding the style in the master, It still does not
become available until I save the underlying record on the "ENTRY FORM" and
reopen it. So every time a new style is added, I have to close the form and
reopen it to enter the style. Can you please help.

Thank you..




Robert Morley wrote:
>You've probably figured out by now that you can use the Case statement here
>as well.
>
> CASE [SHIPTERM] WHEN 'LDP' THEN ROUND([INVQTY] * [LDPPRICE], 2) ELSE
>ROUND([INVQTY] * [FOBPRICE], 2) END
>
>I'm not 100% sure, but you might be able to simplify this a bit further by
>putting the ROUND outside...just not sure if SQL Server will handle this
>syntax, but give it a try:
>
> ROUND([INVQTY] * CASE [SHIPTERM] WHEN 'LDP' THEN [LDPPRICE] ELSE
>[FOBPRICE] END, 2)
>
>Rob
>
>> =Iif([SHIPTERM]="LDP",Round([INVQTY]*[LDPPRICE],2),Round([INVQTY]*[FOBPRICE],
>> 2))
>>
>> Please help me frame this in a Access project data base. i get error
>> validating formula. Help Help

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...erver/200607/1

Hi Gmenon,

While there are a variety of ways of handling these situations, it sounds to
me like probably the quickest way of handling them is to leave everything as
is, but add a couple lines.

1. After you've done the calculations, add Me.Requery to your code.
2. After the Style Master form closes, requery the combo or list box on
your original form: Me!(name of the control).Requery



Rob

"gmenon100 via AccessMonster.com" <u24255@uwe> wrote in message
news:638bf07f5bba7@uwe...
> Hi Rob,
>
> I need help in just these couple of issues:
>
> 1. In the same project, which has a form "ENTRY FORM" I have used the
> fields [invqty], [invprice], [invval], [poqty], [poprice] [poval], where
> invval = invqty * invprice.....poval = [poqty]*[poprice].....
> I am able to calculate the value, however, the invval is not displayed on
> the
> form unless I save the record. How can I make this work so that I dont
> need
> to exit the record and come back to check the invval. Please help.
>
> 2. On the same form, i have field for [STYLE], which looksup for a value
> from
> the STYLE table. I have enabled a macro to open the [STYLE MASTER] Form
> when
> NOT IN LIST. However, after adding the style in the master, It still does
> not
> become available until I save the underlying record on the "ENTRY FORM"
> and
> reopen it. So every time a new style is added, I have to close the form
> and
> reopen it to enter the style. Can you please help.
>
> Thank you..
>
>
>
>
> Robert Morley wrote:
>>You've probably figured out by now that you can use the Case statement
>>here
>>as well.
>>
>> CASE [SHIPTERM] WHEN 'LDP' THEN ROUND([INVQTY] * [LDPPRICE], 2) ELSE
>>ROUND([INVQTY] * [FOBPRICE], 2) END
>>
>>I'm not 100% sure, but you might be able to simplify this a bit further by
>>putting the ROUND outside...just not sure if SQL Server will handle this
>>syntax, but give it a try:
>>
>> ROUND([INVQTY] * CASE [SHIPTERM] WHEN 'LDP' THEN [LDPPRICE] ELSE
>>[FOBPRICE] END, 2)
>>
>>Rob
>>
>>> =Iif([SHIPTERM]="LDP",Round([INVQTY]*[LDPPRICE],2),Round([INVQTY]*[FOBPRICE],
>>> 2))
>>>
>>> Please help me frame this in a Access project data base. i get error
>>> validating formula. Help Help
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...erver/200607/1

Hi,

The below formula when used in a query, works fine but when I use this in the
table, it gives me error:

CONVERT (MONEY, ROUND(INVVAL * .0825 / 360, 2) * DATEDIFF(d, DISCTDT, DUEDATE)
)

I am trying to put this formula in a table for field [DISCCHG].
[DISCTDT] and [DUEDATE] are 2 other fields in the same table.

Appreciate your help. Thanks.

gmenon

Robert Morley wrote:
>You've probably figured out by now that you can use the Case statement here
>as well.
>
> CASE [SHIPTERM] WHEN 'LDP' THEN ROUND([INVQTY] * [LDPPRICE], 2) ELSE
>ROUND([INVQTY] * [FOBPRICE], 2) END
>
>I'm not 100% sure, but you might be able to simplify this a bit further by
>putting the ROUND outside...just not sure if SQL Server will handle this
>syntax, but give it a try:
>
> ROUND([INVQTY] * CASE [SHIPTERM] WHEN 'LDP' THEN [LDPPRICE] ELSE
>[FOBPRICE] END, 2)
>
>Rob
>
>> =Iif([SHIPTERM]="LDP",Round([INVQTY]*[LDPPRICE],2),Round([INVQTY]*[FOBPRICE],
>> 2))
>>
>> Please help me frame this in a Access project data base. i get error
>> validating formula. Help Help

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...erver/200607/1

You can probably get rid of the "CONVERT(MONEY...)" part of things; if the
field is defined as a Money field, that's probably not necessary. Other
than that, though, I don't see a problem with it. Maybe it doesn't like the
DateDiff? I know there's only a limited amount of functions that work in
calculated fields.

Hopefully somebody else who's reading this can help, as I don't use very
many calculated fields in my tables.



Rob

"gmenon100 via AccessMonster.com" <u24255@uwe> wrote in message
news:63bd2929de274@uwe...
> Hi,
>
> The below formula when used in a query, works fine but when I use this in
> the
> table, it gives me error:
>
> CONVERT (MONEY, ROUND(INVVAL * .0825 / 360, 2) * DATEDIFF(d, DISCTDT,
> DUEDATE)
> )
>
> I am trying to put this formula in a table for field [DISCCHG].
> [DISCTDT] and [DUEDATE] are 2 other fields in the same table.
>
> Appreciate your help. Thanks.
>
> gmenon
>
> Robert Morley wrote:
>>You've probably figured out by now that you can use the Case statement
>>here
>>as well.
>>
>> CASE [SHIPTERM] WHEN 'LDP' THEN ROUND([INVQTY] * [LDPPRICE], 2) ELSE
>>ROUND([INVQTY] * [FOBPRICE], 2) END
>>
>>I'm not 100% sure, but you might be able to simplify this a bit further by
>>putting the ROUND outside...just not sure if SQL Server will handle this
>>syntax, but give it a try:
>>
>> ROUND([INVQTY] * CASE [SHIPTERM] WHEN 'LDP' THEN [LDPPRICE] ELSE
>>[FOBPRICE] END, 2)
>>
>>Rob
>>
>>> =Iif([SHIPTERM]="LDP",Round([INVQTY]*[LDPPRICE],2),Round([INVQTY]*[FOBPRICE],
>>> 2))
>>>
>>> Please help me frame this in a Access project data base. i get error
>>> validating formula. Help Help
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...erver/200607/1