MSSQL+MSACCESS function

[mase112]

I have linked tables on my MS access, The tables are on a MS SQL server.
I made a function to calculate a price. The function works whem i have the tables in ms access in de *.MDB file.
I think that i am dowing something wrong whit the Set dbData = CurrentDb.
Or that de recordset is not the same that i am getting back...?

This is the function:

Public Function gettest(Vorm, Groep, Prijs) As Currency
On Error GoTo stoppen2

Dim dbData As Database
Dim rec As Recordset
Dim szSQL As String

Set dbData = CurrentDb


szSQL = "SELECT * FROM tblContractprijs WHERE (GroepID=" + str(Groep) + " AND ContractvormID=" + str(Vorm) + ");"

Set rec = dbData.OpenRecordset(szSQL)
gettest = 0


While Not rec.EOF
If (Prijs >= rec![Cataloguswaardebegin]) And (Prijs < rec![Cataloguswaardeeind]) Then
gettest = rec![Contractprijs]
End If
rec.MoveNext
Wend


rec.Close
Exit Function

stoppen2:
gettest = 1111
End Function

The function never gets past Set rec = dbData.OpenRecordset(szSQL)
Please can someone help me..

 

[checoturco]

szSQL = "SELECT * FROM tblContractprijs WHERE (GroepID=" + str(Groep) + " AND ContractvormID=" + str(Vorm) + ");"




i'm not so sure but try this..

szSQL = "SELECT * FROM tblContractprijs WHERE GroepID=" & str(Groep) & " AND ContractvormID=" & str(Vorm) + & ";"


both groepID and ContractvormID are integer, right?

 

[mase112]

Yes it are integers.
But still the function go's to the on error.

 

[supercharge]

Don't you need to Dim your variables: Vorm, Groep, Prijs?
Something like:

Public Function gettest(Vorm As String, Groep As String, Prijs As Whatever) As Currency