Function results displayed in queries

[jon]

Hi,
I've written a function that calculates a quantity of a product sold in a
dynamically entered number of days.

It works fine, but for some reason it seems to calculate all the lines
twice, starting at the first record and calculating all the way down the
screen, then going back to the top, and doing it all again.

Does anyone know why, or how to stop it doing the job twice ?


thanks

Jon

 

[John W. Vinson]

Hi,
I've written a function that calculates a quantity of a product sold in a
dynamically entered number of days.

It works fine, but for some reason it seems to calculate all the lines
twice, starting at the first record and calculating all the way down the
screen, then going back to the top, and doing it all again.

Does anyone know why, or how to stop it doing the job twice ?

Correct the error in your function or your query.

If you would like help doing so, please post your code. We can't see it from
here.

 

[jon]

Hi John,

My function/s are below, the query is very simple just 2 colunmns (Warehouse
and Product) with the function being called in the third column.

Thanks for looking

Jon

Option Compare Database
Option Explicit
Global STOCKSTATS_CUSTOMER As String
Global STOCKSTATS_DAYS As Integer
Global STOCKSTATS_WAREHOUSE As String
Global STOCKSTATS_PRODUCT As String

Public Function DAYS_SALES_QTY(ALL_CUSTOMER, WAREHOUSE, PRODUCT, DAYS)
STOCKSTATS_CUSTOMER = ALL_CUSTOMER
STOCKSTATS_DAYS = DAYS
STOCKSTATS_WAREHOUSE = WAREHOUSE
STOCKSTATS_PRODUCT = PRODUCT
DAYS_SALES_QTY = 0
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("QRY_DAYS_SALES_QTY_2")
For Each prm In qdf.Parameters
prm.VALUE = Eval(prm.NAME)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Do Until rst.EOF
DAYS_SALES_QTY = rst!QTY_SOLD
rst.MoveNext
Loop
End Function

Public Function DAYS_SALES_COUNT(ALL_CUSTOMER, WAREHOUSE, PRODUCT, DAYS)
STOCKSTATS_CUSTOMER = ALL_CUSTOMER
STOCKSTATS_DAYS = DAYS
STOCKSTATS_WAREHOUSE = WAREHOUSE
STOCKSTATS_PRODUCT = PRODUCT
DAYS_SALES_COUNT = 0
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("QRY_DAYS_SALES_QTY_4")
For Each prm In qdf.Parameters
prm.VALUE = Eval(prm.NAME)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Do Until rst.EOF
DAYS_SALES_COUNT = rst!ORDER_COUNT
rst.MoveNext
Loop
End Function
Public Function DAYS_CUSTOMER_COUNT(ALL_CUSTOMER, WAREHOUSE, PRODUCT, DAYS)
STOCKSTATS_CUSTOMER = ALL_CUSTOMER
STOCKSTATS_DAYS = DAYS
STOCKSTATS_WAREHOUSE = WAREHOUSE
STOCKSTATS_PRODUCT = PRODUCT
DAYS_CUSTOMER_COUNT = 0
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("QRY_DAYS_SALES_QTY_6")
For Each prm In qdf.Parameters
prm.VALUE = Eval(prm.NAME)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Do Until rst.EOF
DAYS_CUSTOMER_COUNT = rst!CUSTOMER_COUNT
rst.MoveNext
Loop
End Function

Public Function GET_STOCKSTATS_CUSTOMER() As String
GET_STOCKSTATS_CUSTOMER = STOCKSTATS_CUSTOMER
End Function

Public Function GET_STOCKSTATS_DAYS() As Date
GET_STOCKSTATS_DAYS = DATE - STOCKSTATS_DAYS
End Function

Public Function GET_STOCKSTATS_WAREHOUSE() As String
GET_STOCKSTATS_WAREHOUSE = STOCKSTATS_WAREHOUSE
End Function

Public Function GET_STOCKSTATS_PRODUCT() As String
GET_STOCKSTATS_PRODUCT = STOCKSTATS_PRODUCT
End Function