problem passing param to SQL function

[sparkbyte]

I cannot seem to get the correct results.

I enter the file number A28402602 into the user prompt and no records are found. It should return around 30 results.

ALTER FUNCTION [dbo].[Test] 
(	
	-- Add the parameters for the function here
	@p1 varchar
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT     TOP 100 PERCENT Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
	FROM         dbo.tblTrackingTable
	WHERE     (FileNumber like @p1)
	ORDER BY TrackingDate DESC
)

This works from MS Query Manager.

	SELECT     TOP 100 PERCENT Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
	FROM         dbo.tblTrackingTable
	WHERE     (FileNumber like 'A28402602')
	ORDER BY TrackingDate DESC

Thanks

John Fuhrman

 

[sparkbyte]

I also have tried several variations of

(FileNumber = @p1)

(FileNumber = "'" + @p1 + "'")

(FileNumber = '' + @p1 + '')

(FileNumber = ''' + @p1 + ''')

(FileNumber = '''' + @p1 + ''')


Thanks

John Fuhrman

 

[sparkbyte]

Private Sub btnTestLookup_Click()
CurrentProject.Connection.Execute ("Test")
End Sub

Run-time error '-2147217900 (80040e14)':
the request for procedure 'Test' failed because 'Test' is a table valued function object.

Private Sub btnTestLookup_Click()
CurrentProject.Connection.Execute ("usp_FileNumberLookup")
End Sub

No Error Code - But does not return table.

Could use a little guidence.



Thanks

John Fuhrman

 

[PWise]

how about

dim rst as recordset
set rst = CurrentProject.Connection.Execute("Select * from dbo.test('A28402602')")
with rst
do while not .eof
debug.print .fields(0).name ;.fields(0)
.movenext
loop
end with