Create a new module (MODULE1)
Create a new function MyCalculation.
Note: Need to make sure F1-F4 are appropriate data types when passed into
the function e.g. numbers of the proper type
and check for invalid numbers within the function.
Call this from a query with tables that contain the requied fields, say
myField1, myField2, myField3, myField4
In the query results field enter :
NameValue:MyCalculation(myField1,myField2,myField3,myField4)
Your results will be a dataset with the calculations done for you and
seperated out into a module where you can make changes.
Hope this helps to get you started.
Ed Warren
---------------------------------------------------------------------code
Public Function MyCalculation(F1 as integer, F2 as double, F3 as long, F4 as
currency) As Double
'Returns ((F1+F2 TIMES F3+F4) TIMES F1) DIVIDED BY 60
Dim returnValue As Double
returnValue = ((F1 + F2) * (F3 + F4)) * F1
returnValue = returnValue / 60
MyCalculation = returnValue
End Function
"Laurel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Well, not a lot.... not to warrant setting up a spreadsheet.... just to
much
> for me to do comfortably inside the SQL statement. I need to learn to
> traverse records inside code, and I think I'm getting closer.... See my
> second posting "accessing tables directly."
> "PC Datasheet" <(E-Mail Removed)> wrote in message
> news:UwlIb.15673$(E-Mail Removed)...
> > You say you have a lot of calculations. You might consider writing the
> data out
> > to a preformatted Excel workbook, let Excel crunch the numbers and then
> import
> > the results back to Access.
> >
> >
> > --
> > PC Datasheet
> > Your Resource For Help With Access, Excel And Word Applications
> > (E-Mail Removed)
> > www.pcdatasheet.com
> >
> >
> > "Laurel" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > I have a situation where I need to do a lot of calculations on the
> details
> > > of a query before displaying the summarized results. I'm having a
> > > difficult time sorting out the best approach for behind-the-scenes
> > > traversing of recordsets, since everything in Access seems tied to a
> form.
> > > The idea I have so far is this....
> > >
> > > Note - If the manipulations could be done directly in the query, I'm
not
> > > good enough at SQL to manage it, so that's not a solution.
> > >
> > > 1 - Initially assign the detail query to my form.
> > > 2 - Set the fields to visible = false (since I don't want the user to
> see
> > > the details at all)
> > > 3 - In my Load event, step through the recordset. Gather up my
summary
> > > information, do my manipulations, and write the results out to a
> reusable
> > > summary table (I'm not sure how to do that in visual basic... could
> someone
> > > help out here? Can you just do a SQL Insert statement inside of code?
> Or do
> > > you have to use an Insert Query?)
> > > 4 - Reasssign the form's Recordsource value to the new summary table
and
> do
> > > a Me.Requery. Make everything visible.
> > > The summary table will have the same column names as the original
> detail
> > > table.
> > >
> > > This seems pretty klugey. I'm hoping I'm just missing some basic
> technique
> > > for working behind the scenes in Access. Please advise.
> > >
> > >
> >
> >
>
>
Similar Threads
