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:


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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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)
Jeffrey CoachmanMIS LiasonCommented:
FWIW here is another way to do it using
IIF() and Isserror()

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...
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")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
This ended up working.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.