• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 45
  • Last Modified:

Trying to convert an Excel formula to Access.

I'm trying to convert an Excel formula to Access and use it as an expression in a query.  The Excel formula is:

=IFERROR((((J11-$M$5)*-I11)*$M$6)+(((K11-$M$7)*-I11)*$M$8),"")

In the Access form/subform...

J11 = txtSplits on the subform
M5 = txtSplitsBenchmark on the main form
I11 = txtNetCWT on the subform
M6 = txtDisSchd3 on the main form
K11 = txtDamage on the subform
M7 = txtDamBenchmark on the main form
M8 = txtDisSchd4 on the main form

Can someone please help convert the Excel formula in a syntax I can use for an expression in query designer?
0
SteveL13
Asked:
SteveL13
  • 3
1 Solution
 
Máté FarkasDatabase Developer and AdministratorCommented:
This is not enough information. We should see the Excel file to see data to convert this formula to excel.
Are these all data in the same table in Access?
Are the name of fields of the table in Access the same as control on your forms?
Anyway it looks like this in Access:
=Nz((((txtSplits-txtSplitsBenchmark )*-txtNetCWT)*txtDisSchd3)+(((txtDamage-M7)*-txtNetCWT)*txtDisSchd4),"")

Open in new window

0
 
SteveL13Author Commented:
Is not working.  So I'm trying to break it down a bit at a time.  Here is what I have so far but I get an error:  "is not a valid name....."

SplitDamInCWT: IIf([Splits],0)-Nz([Forms]![frmAssemblySheetHeaderPulse].[txtSplitsBenchmark],0)*Nz([NetCWT],0)*Nz([Forms!frmAssemblySheetHeaderPulse].[txtDisSchd3],0)
0
 
Jeffrey CoachmanMIS LiasonCommented:
Steve,
FWIW here is another way to do it using
IIF() and Isserror()

IIF(IsError(YourExpression),"",YourExpression)
This will show nothing if "YourExpression" evaluates to an error,
...or it will display the result of your expression if there is no error

...Here you will have to work out your parenthesis on your own though...
;-)
0
 
SteveL13Author Commented:
I ended up getting it to work with:

SplitDamInCWT: Format((([Splits]-[Forms]![frmAssemblySheetHeaderPulse]![txtSplitsBenchmark])*-[NetCWT])*[Forms]![frmAssemblySheetHeaderPulse]![txtDisSchd3]+(([Damage]-[Forms]![frmAssemblySheetHeaderPulse]![txtDamBenchmark])*-[NetCWT])*[Forms]![frmAssemblySheetHeaderPulse]![txtDisSchd4],"Standard")
0
 
SteveL13Author Commented:
This ended up working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now