Pass a parameter to a function in a query?

  • Thread starter Thread starter James Houston
  • Start date Start date
J

James Houston

I'm trying to run the following query:

SELECT ITEMS.[ORDER#], ITEMS.[ITEM#], ITEMS.COLOR, ITEMS.SIZE,
ParseSize([items]![size]) AS ParsedSize
FROM ITEMS
WHERE (((ParseSize([items]![size]))=[Enter Size]));

where ParseSize is a function I wrote which strips the " X " from a string
like "38 X 32" and returns "3832". The query runs fine if I don't try to
prompt for user input, but throws an error otherwise. The code for the
function is below:

Function ParseSize(ByVal sSizeToParse As String) As String
Dim sWL, sW, sL As String
sSizeToParse = Trim(sSizeToParse)
sWL = " X "
If InStr(sSizeToParse, sWL) Then
sW = Left$(sSizeToParse, 2)
sL = Right$(sSizeToParse, 2)
ParseSize = sW & sL
End If

End Function

Thanks in advance

Jim
 
I'm trying to run the following query:

SELECT ITEMS.[ORDER#], ITEMS.[ITEM#], ITEMS.COLOR, ITEMS.SIZE,
ParseSize([items]![size]) AS ParsedSize
FROM ITEMS
WHERE (((ParseSize([items]![size]))=[Enter Size]));

where ParseSize is a function I wrote which strips the " X " from a string
like "38 X 32" and returns "3832". The query runs fine if I don't try to
prompt for user input, but throws an error otherwise. The code for the
function is below:
Click to expand...

In A2002 and later you can finesse the whole issue by using the
builtin Replace() function. Note that the ! is probably inappropriate
in this context - it's used for form controls; for table field
reference use . instead. Try

SELECT ITEMS.[ORDER#], ITEMS.[ITEM#], ITEMS.COLOR, ITEMS.SIZE,
Replace([items].[size], " X ", "") AS ParsedSize
FROM ITEMS
WHERE (((ParseSize([items].[size]))=[Enter Size]));
 
What is the error?

Try declaring the parameter type.

PARAMETERS [Enter Size] Text;
SELECT ITEMS.[ORDER#], ITEMS.[ITEM#], ITEMS.COLOR, ITEMS.SIZE,
ParseSize([items]![size]) AS ParsedSize
FROM ITEMS
WHERE (((ParsedSize([items]![size]))=[Enter Size]));
 
Back
Top