Hi, I have an Access front end with a SQL Server backend and need to able to call a User define function in a query. To do this I need to create the function but I can't get the right syntax.

CREATE FUNCTION BCL_GetForeignKeyCode(@param1 as varchar(16))
RETURNS Integer
AS
BEGIN
declare @intPos Integer;
declare @sTempValue varchar(16);
declare @sNumber Integer;
declare @iRet Integer;
declare @iRett Integer;
declare @sRet varchar(16);
declare @sStr varchar(16);

set @iRet = 1;
set @iRett = 1;

While @sStr <>"O"
Begin
@sStr=SubString(@param1,@iRet,1)
@sTempValue=@sTempValue + @sStr
@iRet=@iRet + 1
End
'Convert from Hex to double ??? How
Set @sNumber = "&H'" & @sTempValue;

While @iRett <= Len(@sNumber)
Begin
@sRet=SubString(@sNumber, @iRet, 1) + @sRet
@iRett=@iRett + 1
End

Return @sRet
END
The error (170) is in two places
Line 18 near @sStr
Line 27 near @sRet

The first part takes a string and I want it to just collect all the characters up until the letter O.
The second bit needs to covert what is now a Hexadecimal number to an actual number and the 3rd and final part reverses the digits.
Okay a decryption!! I need to use in a query hence the UDF.
Thanks

Hi, I have an Access front end with a SQL Server backend and need to able to call a User define function in a query. To do this I need to create the function but I can't get the right syntax.

CREATE FUNCTION BCL_GetForeignKeyCode(@param1 as varchar(16))
RETURNS Integer
AS
BEGIN
declare @intPos Integer;
declare @sTempValue varchar(16);
declare @sNumber Integer;
declare @iRet Integer;
declare @iRett Integer;
declare @sRet varchar(16);
declare @sStr varchar(16);

set @iRet = 1;
set @iRett = 1;
set @sRet = "";

While @sStr <>"O"
Begin
@sStr=SubString(@param1,@iRet,1)
@sTempValue=@sTempValue + @sStr
@iRet=@iRet + 1
End
'Convert from Hex to double ??? How
Set @sNumber = "&H'" & @sTempValue;

While @iRett <= Len(@sNumber)
Begin
@sRet=SubString(@sNumber, @iRet, 1) + @sRet
@iRett=@iRett + 1
End

Return @sRet
END
The error (170) is in two places
Line 18 near @sStr
Line 27 near @sRet

The first part takes a string and I want it to just collect all the characters up until the letter O.
The second bit needs to covert what is now a Hexadecimal number to an actual number and the 3rd and final part reverses the digits.
Okay a decryption!! I need to use in a query hence the UDF.
Thanks

There are two things that stand out to me. First, you do not initialize the variable @sRet, and second, you are trying to take a substring from an Integer variable @sNumber. I am not sure that either of these would create your problem, but you should address both of them and see what happens.