Solved

More help on an expression.  Multiply something by a database figure

Posted on 2013-11-25
10
232 Views
Last Modified: 2013-11-26
Hello all.

Now I need to figure up the "Net" dollar figure on frmLogs.  The gross is done.

To figure out the net:
Multiply the Gross (already calculated and shown in frmLogs) by either 50% or 60%.  

To figure out whether to use 50% or 60%.
I added to tblGrades a column called NetPerc (net percentage).  Each tree species has either a value of .5 or .6.  Now I need to look up that value to multiply to the Gross figure.

I can get this to work.
In frmLogs for Net, if I put this in the expression builder "=[txtGross]*.5" that works correctly.  

A couple things I tried.
I cannot get something like this to work: "=[txtGross]*[tblGrades]![NetPerc]".  Maybe I would guess I need to somehow add whatever the syntax to it something like this: =[txtGross]*[tblGrades]![NetPerc] WHERE tblGrades.Species = Species.  IDK.
Database1683.accdb
0
Comment
Question by:getwidth28
  • 4
  • 3
  • 3
10 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39675276
Can't open your database at the moment, but try this or something similar, assuming that species is a field on your form:

If species is text in tblGrades:
 = [txtGross] * DLookup("NetPerc", "tblGrades", "Species = '" & Species & "'")

Open in new window

Or if species is numeric in tblGrades:
 = [txtGross] * DLookup("NetPerc", "tblGrades", "Species = " & Species)

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39675516
Don't use DLookup() in queries (or code loops).  Use a join instead.  Using DLookup() causes Access to run a completely separate query.  So, if your query returns a thousand rows, you'll be running another thousand queries.  That's a huge amount of overhead.  The join is much more efficient and therefore better practice.

Select ..., tb1.Gross * tblGrades.NetPerc As NetAmt
Fro tb1 inner join tblGrades on tb1.Species = tblGrades.Species.
0
 

Author Comment

by:getwidth28
ID: 39675631
I could not figure out how to do a select in the expression box.

I was able to work my way into getting the DLookup() to wok, although not a best practice as you mentioned.  Since my knowledge is limited, I will take what I can get to work for now.

Here is how I built myself into getting something that worked going off of yours mbizup.

=DLookUp("[NetPerc]","tblGrades"

=DLookUp("[NetPerc]","tblGrades","[Species] = ""Ash""")

=DLookUp("[NetPerc]","tblGrades","[Species] = Form!Species")

=DLookUp("[NetPerc]","tblGrades","[Species] = [Forms]![frmTrees]![TreeType]")

I messed up along the line the started using both species and TreeType for the same thing.  That is bad I know, maybe I will fix it soon.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39675676
Joins are quite easy if you use the QBE.  You'll probably take less time than it took to figure out the Dlookup().  Open the query window and drag the two tables on to it.  Draw a join line by clicking and dragging to connect the two tables on Species or Species to TreeType.  Then you can select columns from both tables.  Add a calculated column in the query or on the Form/Report.  Use this query as the RecordSource for your form/report.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39675742
to my understanding,this is not for a query or code loop so should be ok.

your syntax is close, but you need to separate out the form reference.

Dlookup("netperc","tblgrades", "species :  '" & forms!frmtrees!species & "'")
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 61

Expert Comment

by:mbizup
ID: 39675746
That colon should have been an equals sign.
0
 

Author Comment

by:getwidth28
ID: 39675970
What I posted was working ok.  I was able to use a label to show that it was finding the right value (either .5 or .6) plus then multiply it and give the right answer.  

Percentage
Now I need to fill out the area above that says "Total Tree Net".  I was able to do likewise queries with VBA with a Form action with nested loops.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39676168
It didn't need to be so complicated.
If you have A2010 or A2013 (possibly A2007), you can add a totals row to the subform by clicking on the sigma button on the ribbon.  Then choose sum as the option for net.  You can just leave it in the subform and do nothing or you can add an expression that updates the field in the header after the subform is updated.

So, in the subform's afterupdate event.
Me.Parent!txtTotalTreeNet = Me.txtSumNet
0
 

Author Comment

by:getwidth28
ID: 39678901
PatHarman
I like what you are saying but I can't get the sigma button to not be greyed out.  I think I see that there has to be a subform like you mentioned and such.  I don't quite get it though.

For now I suppose I will use what I have although not the best practice.  I am trying to get something functional quick with what I can learn for this project.  A 2nd revision will hopefully let me focus more on simplifying and speeding it up.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39678927
It may only be available for subforms in datasheet view.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now