error

  • Thread starter Thread starter gmenon100
  • Start date Start date
G

gmenon100

=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
 
SURELY IT WORKED!! THANKS.

Robert said:
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
Click to expand...
Click to expand...
 
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top