Use a function in a query

[L'apprentis]

I have written a simple function that I would like to use in a query. The function would return a value for each record where the parameters equals 2 different fields of the same record.

Private Function FctConvertInch([B]LineDim[/B] As String, [B]LineShape [/B]As String)

'To Convert a String to a value and then convert that value from mm to inches
'Lines dimension = 00.0000x00.0000 if line is flat
'Lines dimension = 00.0000 if line is round
Dim StNum1 As String
Dim StNum2 As String
Dim DbNum1 As Double
Dim DbNum2 As Double

If LineShape = "round" Then
'"CDbl" convert a string to Double number
FctConvertInch = CDbl([LineDim]) / 25.4
ElseIf LineShape = "flat" Then
StNum1 = Left([LineDim], 7)
StNum2 = Right([LineDim], 7)
DbNum1 = CDbl(StNum1)
DbNum2 = CDbl(StNum2)
FctConvertInch = (DbNum1 / 25.4) & "x" & (DbNum2 / 25.4)
End If
End Function

I have put the function in a new module, and have try to use the function in a query with the fields LineDim and LineShape , I've also added the field FctConvertInch(LineDim,LineShape) but it doesn't work. Am I using the function the the correct way?

 

[Newman]

I am not 100% sure, but I think that you should say «Public Function» instead of «Private Function».

 

[L'apprentis]

Thanks for your repy, I have indeed change my function to a public function, but when I try to run the query, it tells me that the function is undefined.
I have put the SQL below to show what I have done:

[B]SELECT[/B] TblLine.LineID, TblLine.LineType, TblLine.LineDim, TblLine.LineShape,
           FctConvertInch([LineDim],[LineShape]) AS Conv 
[B]FROM [/B]TblLine 
[B]GROUP BY[/B] TblLine.LineID, TblLine.LineType, TblLine.LineDim, TblLine.LineShape,
           FctConvertInch([LineDim],[LineShape]);

 

[StevenS]

i'm not sure but maybe this helps...

SELECT TblLine.LineID, TblLine.LineType, TblLine.LineDim, TblLine.LineShape,
FctConvertInch([LineDim],[LineShape]) AS Conv
FROM TblLine
GROUP BY TblLine.LineID, TblLine.LineType, TblLine.LineDim, TblLine.LineShape;

i think you can't use an expression in the group-by part of the statement

 

[Rich]

I don't think it's a good idea to have the named arguments the same as your field names

 

[L'apprentis]

Cheers Stevens, unfortunately, I still get the same error message without the GROUP BY Function of the SQL.

 

[L'apprentis]

I see what your saying Rich, so how do I get my fields to be equal to the parameters?

 

[Rich]

You still add the field names when using the function in the query

FctConvertInch(Lindims As String, Linshpe As String)


FctConvertInch([LineDim],[LineShape]) AS Conv

 

[L'apprentis]

Yo Rich, thanks for your advice, I just found what was wrong with the function with a bit of more searching in the forum: My module was saved with the same name than the function, I got it working now.

 

[Brianwarnock]

Yo Rich, thanks for your advice, I just found what was wrong with the function with a bit of more searching in the forum: My module was saved with the same name than the function, I got it working now.

Damn, I just got it working on a little test and was about to suggest that , oh well it filled my lunch break.

Brian

 

[Bat17]

And I was wondering if something simpler might work
Private Function FctConvertInch(LineDim As String)
FctConvertInch = Eval(Replace(LineDim , "x", "*")) * 25.4
End Function

Peter