Access2000: Function don't work properly with Window's decimal symbol set to comma

[Arvi Laanemets]

Hi

I have an UDF, which is called from query:
***
Public Function ORnk(SourceN As String, KeyN As String, FieldN As String,
FieldVal, YearVal As Integer, GroupVal As Integer) As String
Dim dbs2 As Database
Dim rs2 As Recordset
Set dbs2 = CurrentDb
On Error GoTo Err_ORnk
Set rs2 = dbs2.OpenRecordset("SELECT " & KeyN & ", " & FieldN & _
" FROM " & SourceN & _
" WHERE Aasta = " & YearVal & " AND Grupp = " & GroupVal & _
" Order By 2 DESC")
rs2.FindFirst "[" & FieldN & "] = " & FieldVal
FFnd = Nz(rs2.AbsolutePosition, 0) + 1
i = -1
While Nz(rs2.Fields(1), 0) = FieldVal
i = i + 1
LFnd = FFnd + i
rs2.MoveNext
ORnk = CStr(FFnd) & IIf(FFnd = LFnd, "", "-" & CStr(LFnd))
Wend
Err_ORnk:
rs2.Close
dbs2.Close
Set rs2 = Nothing
Set dbs2 = Nothing
End Function
***

The function returns ranking for field FieldN in table/query SourceN for
particular year and group. The ranking is a string like "1" or "3 - 5" etc.
But the function fails for all cases with non-integer FieldVal, when in
Window's settings the decimal separator is set to ',' instead of '.' The
problem arrives at row
rs2.FindFirst "[" & FieldN & "] = " & FieldVal
Access simply doesn't find any match whenever the FieldVal isn't integer,
and the function is ended at spot. I tried to declare VieldVal in function
as Double or Variant parameter, but it didn't help.

I avoided the problem at moment by resetting the decimal separator for my
computer to period, but later I have to deliver my database to another user,
where such setup isn't allowed. Has somebody here encoutered a similar
problem, and is there some cure for it?

 

[david epsom dot com dot au]

" WHERE Aasta = " & YearVal & " AND Grupp = " & GroupVal & _

" WHERE Aasta = " & YearVal & " AND Grupp = " & format(GroupVal,"#") & _

(david)

 

[Arvi Laanemets]

Hi

" WHERE Aasta = " & YearVal & " AND Grupp = " & format(GroupVal,"#") & _

The field Group is an integer always. Sooner the problem is here:
Set rs2 = dbs2.OpenRecordset("SELECT " & KeyN & ", " & FieldN & _
or in the way, Access does interpret the value of parameter FieldVal.

The problem arrives, when ranking for field p.e. Profitableness, which has
entries like #.####, is calculated. The select string for OpenRecordset will
be like
"SELECT ID, Profitableness FROM Source WHERE Year=2003 AND Group=1 ORDER BY
2 DESC"
(field Group can have values 1, 2 or 3)

After the recordset is opened, the first occurence of a value FieldVal for
Profitableness is searched for. The value is there - with first row in
recordset I can see this in watch window, when I watch for rs2 - and all
entries matching for given WHERE conditions are there. When I watch for
FieldVal, the number looks exactly same. But when the first occurence for
this number in rs2 is serched for, whenever this value has digits, the
search fails as long as Window's decimal delimiter is set to comma. I set
decimal delimiter to period in regional settings, and all works perfectly.

I have a suspect that with non-US settings, Access uses diferent number
formats in queries and in VBA. The function is called from query like this
one (not the same as in example above - the function isn't the same, but the
diference is only in the way the result value is calculated, and both source
and field are different too):
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;

The value for parameter FieldVal is given as reference to field value
Q1.PProfitableness (PProfitableness has values like 1, 2, (3+4)/2=3.5 etc .
It's Access here, and all International settings are here valid. I.e. when
decimal delimiter is comma, the value for field PProfitableness is read here
like 2,5.

In VBA, international settings are ignored. So I'm afraid, for non-integer
values the parameter FieldVal isn't taken for number (it MUST be 2.5 here)!
I hope I'm wrong, of-course, but I'll give it a check at home after work

 

[Arvi Laanemets]

Hi

The solution (thanks to Karl Donaubauer from microsoft.public.de.access)
rs1.FindFirst "[" & FieldN & "] = " & Str(FieldVal)

Arvi Laanemets