Re: PRODUCT calculation in queries

Hi,

Once pasted the code compiles fine.
I get the following runtime error:

Run-time error '3078':

The Microsoft Jet database engine could not find the input table or query
'Field3'.
Make sure it exists and that its name is spelled correctly.

When I click debug the following line is thrown up:

Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")

I'm using Access 2003 and the DAO library is definately referenced.

Joe

"Allen Browne" wrote:

What error are you getting?
Which line generates the error?

After pasting it into the code window, choose Compile on the Debug menu.
Does it compile okay?

If you are using Access 2000 or 2002, you may need to add a reference to the
Microsoft DAO Library. More on references:
http://allenbrowne.com/ser-38.html

Once you have it running, you can use it just like one of the built-in
functions in any query.

You cannot reverse-engineer the built-in functions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe Miller" <JoeMiller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:056F9137-00BA-45EC-AB07-40CA8ECD8268@xxxxxxxxxxxxxxxx

Thanks for the reponse, I've given it a whirl, but it throws up a runtime
error. I'm sure the problem lies with the limitations of my instructions,
together with my relative ineptitude with VBA and with maths in general.

What I really need is a basic generic Product function that can simply be
dropped into any query. After that I can simply use queries to fiddle with
the critera aspects of things. I would have assumed that a function based
on
similar code/maths principles as the existing series-based functions
(Count,
Sum etc) shouldn't be that difficult to write.

Is there any way to see the underlying code behind the standard functions
like Sum - if you could take this particular function and modify the code
by
changing the addition operator to a multiplication operator that would do
the
trick.

All of these assumptions are made by someone with a very basic maths
education so feel free to highlight my naivety!


Joe

"Allen Browne" wrote:

This is untested aircode, but you should be able to save the function
below
into a standard module (Modules tab of Database window). Then in a query,
type an expression like this into a fresh column in the Field row:
Product("Table1", "Field3", "ID <> " & [ID])

Function Product(strField As String, strTable As String, _
Optional strCriteria As String) As Double
Dim rs As DAO.Recordset
Dim strSql As String
Dim dblResult As Double

'Build the SQL string.
strSql = "SELECT " & strField & " FROM " & strTable & _
" WHERE (" & strField & " Is Not Null) "
If strCriteria <> vbNullString Then
strSql = strSql & " AND (" & strCriteria & ")"
End If

'Loop through the recordset.
Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")
If rs.RecordCount > 0 Then
dblResult = rs(strField)
rs.MoveNext
Do While Not rs.EOF
dblResult = dblResult * rs(strField)
rs.MoveNext
Loop
End If
rs.Close

Product = dblResult
End Function

"Joe Miller" <Joe Miller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ABE55634-37CC-4962-AE76-9B918A94B67E@xxxxxxxxxxxxxxxx
Hi,

I'm having trouble creating a query/ expression that will multiply all
of
the values in a field together (i.e return the Product of those
numbers.)

I have a table with several records, Field3 of which contains numbers
(with
a few decimal places.) Ultimately, I want to be able to calculate, for
each
record in the table/query, the product of all of the other Field3
values
excluding its own (i.e the expression will return for Record #1 the
product
of Field3 in records # 2-10, the expression for record #2 will return
the
product of records#1,3-10 etc)

At the moment, however, I'm simply struggling to be able to return any
sort
of product at all. I don't seem to be able to locate any equivalent of
the
Excel PRODUCT function.

Cany anyone with better maths skills than me help!?
Joe



.

Relevant Pages

  • Re: OnOpen Code Problem Still Exists After Workarounds
    ... Sorry I didn't point out where to Compile, I took for granted that you had ... "Gina Whipp" wrote: ... Microsoft Office 10.0 Object Library can't 'translate' the later ...
    (microsoft.public.access.formscoding)
  • Re: PRODUCT calculation in queries
    ... Tips for Access users - http://allenbrowne.com/tips.html ... functions in any query. ... Function Product(strField As String, strTable As String, _ ... Dim dblResult As Double ...
    (microsoft.public.access.queries)
  • Re: input type="radio" ASP.net bug
    ... > Microsoft does not make decisions regarding their object models based on ... W3C is the authorative source for HTML and CSS. ... > HTTP POST is a string. ... Microsoft is about standards. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: problem logging off and shutting down
    ... One using the Windows OS CD ... using repar from the setup menu and another microsoft repair tool that I ran ... I have been all over the Microsoft website and internet trying to ... #1 is a String Value ...
    (microsoft.public.windowsxp.general)
  • Re: PRODUCT calculation in queries
    ... After pasting it into the code window, choose Compile on the Debug menu. ... Does it compile okay? ... Function Product(strField As String, strTable As String, _ ... Dim dblResult As Double ...
    (microsoft.public.access.queries)