A
Arvi Laanemets
Hi
I have created a query Q1:
SELECT a.ID, ..., ROUND(IIf(Nz(a.Turnover,0)=0,0,a.Profit/a.Turnover),4) AS
Profitableness
FROM SourceTable AS a
ORDER BY a.Year, a.Group;
Q1 serves as source for some another queries, and one of them doesn't work
properly. The syntax for this query is:
SELECT a.Year, a.Group, a.ID, a.Profitableness,
OPts("Q1","ID","Profitableness",a.Profitableness,DLookup("ProfitablenessN","
Rules","Year=" & a.Year),a.Year,a.Group,DLookup("Selection","Rules","Year="
& a.Year)) AS PProfitableness
FROM Q1 AS a
ORDER BY a.Year, a.Group, a.Profitableness;
Every one of those queries is estimating order for some field (indicator ) -
present in SourceTable, or calculated using fields from SourceTable
(Profitableness in our case)- from Q1 by Year and Group, and distributes
points between N best entries for every Group in year (calculated field
PProfitableness here). The number N is defined for every field separately
for every year in table Rules. Also is in table Rules for every year defined
calculation regulations for cases, when field values are negative or zero's
(Rules.Selection can have values 1, 2 or 3)
The UDF used in query is:
Public Function OPts(SourceN As String, KeyN As String, FieldN As String,
FieldVal, MaxN As Integer, YearVal As Integer, GroupVal As Integer,
Selection As Integer) As Double
Dim dbs1 As Database
Dim rs1 As Recordset
Set dbs1 = CurrentDb
On Error GoTo Err_OPts
Set rs1 = dbs1.OpenRecordset("SELECT " & KeyN & ", " & FieldN & _
" FROM " & SourceN & _
" WHERE Year = " & YearVal & " AND Group = " & GroupVal & _
" Order By 2 DESC")
rs1.FindFirst "[" & FieldN & "] = " & Round(FieldVal, 4)
FFnd = Nz(rs1.AbsolutePosition, 0) + 1
PntSum = 0
If FFnd > MaxN Or (Selection = 3 And Nz(FieldVal, 0) <= 0) Or (Selection
= 2 And Nz(FieldVal, 0) < 0) Then
OPts = 0
Else
i = -1
While Nz(rs1.Fields(1), 0) = FieldVal
i = i + 1
PntSum = PntSum + IIf(FFnd + i > MaxPl, 0, MaxPl - FFnd - i + 1)
rs1.MoveNext
OPts = PntSum / (i + 1)
Wend
End If
Err_OPts:
rs1.Close
dbs1.Close
Set rs1 = Nothing
Set dbs1 = Nothing
End Function
This works for all indicators except one - Profitableness, for which no
points are distributed. On UDF's row with rs1.FindFirst occurs error (no
message there, it looks like no match is found) and the function returns
nothing. The only real difference I can see, is that Profitableness is only
indicator which can (and has) values less than 1. There are both direct and
calculated indicators present, for which points are distributed properly,
and they all have integer values.
When someone here had enough patience to read all this, then maybe he/she
looks at my UDF - there must be something I missed.
Thanks in advance
I have created a query Q1:
SELECT a.ID, ..., ROUND(IIf(Nz(a.Turnover,0)=0,0,a.Profit/a.Turnover),4) AS
Profitableness
FROM SourceTable AS a
ORDER BY a.Year, a.Group;
Q1 serves as source for some another queries, and one of them doesn't work
properly. The syntax for this query is:
SELECT a.Year, a.Group, a.ID, a.Profitableness,
OPts("Q1","ID","Profitableness",a.Profitableness,DLookup("ProfitablenessN","
Rules","Year=" & a.Year),a.Year,a.Group,DLookup("Selection","Rules","Year="
& a.Year)) AS PProfitableness
FROM Q1 AS a
ORDER BY a.Year, a.Group, a.Profitableness;
Every one of those queries is estimating order for some field (indicator ) -
present in SourceTable, or calculated using fields from SourceTable
(Profitableness in our case)- from Q1 by Year and Group, and distributes
points between N best entries for every Group in year (calculated field
PProfitableness here). The number N is defined for every field separately
for every year in table Rules. Also is in table Rules for every year defined
calculation regulations for cases, when field values are negative or zero's
(Rules.Selection can have values 1, 2 or 3)
The UDF used in query is:
Public Function OPts(SourceN As String, KeyN As String, FieldN As String,
FieldVal, MaxN As Integer, YearVal As Integer, GroupVal As Integer,
Selection As Integer) As Double
Dim dbs1 As Database
Dim rs1 As Recordset
Set dbs1 = CurrentDb
On Error GoTo Err_OPts
Set rs1 = dbs1.OpenRecordset("SELECT " & KeyN & ", " & FieldN & _
" FROM " & SourceN & _
" WHERE Year = " & YearVal & " AND Group = " & GroupVal & _
" Order By 2 DESC")
rs1.FindFirst "[" & FieldN & "] = " & Round(FieldVal, 4)
FFnd = Nz(rs1.AbsolutePosition, 0) + 1
PntSum = 0
If FFnd > MaxN Or (Selection = 3 And Nz(FieldVal, 0) <= 0) Or (Selection
= 2 And Nz(FieldVal, 0) < 0) Then
OPts = 0
Else
i = -1
While Nz(rs1.Fields(1), 0) = FieldVal
i = i + 1
PntSum = PntSum + IIf(FFnd + i > MaxPl, 0, MaxPl - FFnd - i + 1)
rs1.MoveNext
OPts = PntSum / (i + 1)
Wend
End If
Err_OPts:
rs1.Close
dbs1.Close
Set rs1 = Nothing
Set dbs1 = Nothing
End Function
This works for all indicators except one - Profitableness, for which no
points are distributed. On UDF's row with rs1.FindFirst occurs error (no
message there, it looks like no match is found) and the function returns
nothing. The only real difference I can see, is that Profitableness is only
indicator which can (and has) values less than 1. There are both direct and
calculated indicators present, for which points are distributed properly,
and they all have integer values.
When someone here had enough patience to read all this, then maybe he/she
looks at my UDF - there must be something I missed.
Thanks in advance