Public Functions in SQL statements...

[Guest]

Hi Access programming gurus,
I have written a public function in the VBE and am trying to use it in a
query I'm creating.

I have been getting my SQL statements by first building a query in the
regular query builder, converting the query to SQL, copying the SQL and using
the statement in VBA code to add variables and such.

I have created a public function that I'd like to use to evaluate some
fields in a newly created table and keep getting an error message saying that
the function is invalid...the thing is, the function is showing up in the
expression builder, but when I run the query in the query builder, I keep
getting an error message saying that the function is invalid...

....what am I doing wrong?



Here's my function code:

Option Compare Database
Option Explicit

Function ScoreChangeCalc(v_SCORE_CT As Integer, v_SCORE_PY As Integer)
'CURRENT Score listed first
'PLANYEAR score listed second

Select Case v_eval

Case v_SCORE_CT = 0 And v_SCORE_PY > 0

ScoreChangeCalc = -1

Case v_SCORE_CT = 0 And v_SCORE_PY = 0

ScoreChangeCalc = 0

Case v_SCORE_CT < v_SCORE_PY

ScoreChangeCalc = (-(v_SCORE_PY - v_SCORE_CT) / v_SCORE_PY)

Case Else

ScoreChangeCalc = ((v_SCORE_CT - v_SCORE_PY) / v_SCORE_CT)

End Select


End Function

 

[Roger Carlson]

Showing both the function and the SQL would help anyone attempting to answer
this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[George Nicholson]

Select Case v_eval
What is v_eval? Its not declared or assigned a value.

Just a guess but

Select Case True

seems like it might be what you want.

- Be sure you have covered all division by zero possibilites. If CT can't be
less than zero you are OK.
- Good idea to specify what data type you want the function to return:
Function ScoreChangeCalc(v_SCORE_CT As Integer, v_SCORE_PY As
Integer) As Single

 

[fredg]

Hi Access programming gurus,
I have written a public function in the VBE and am trying to use it in a
query I'm creating.

I have been getting my SQL statements by first building a query in the
regular query builder, converting the query to SQL, copying the SQL and using
the statement in VBA code to add variables and such.

I have created a public function that I'd like to use to evaluate some
fields in a newly created table and keep getting an error message saying that
the function is invalid...the thing is, the function is showing up in the
expression builder, but when I run the query in the query builder, I keep
getting an error message saying that the function is invalid...

...what am I doing wrong?

Here's my function code:

Option Compare Database
Option Explicit

Function ScoreChangeCalc(v_SCORE_CT As Integer, v_SCORE_PY As Integer)
'CURRENT Score listed first
'PLANYEAR score listed second

Select Case v_eval

Case v_SCORE_CT = 0 And v_SCORE_PY > 0

ScoreChangeCalc = -1

Case v_SCORE_CT = 0 And v_SCORE_PY = 0

ScoreChangeCalc = 0

Case v_SCORE_CT < v_SCORE_PY

ScoreChangeCalc = (-(v_SCORE_PY - v_SCORE_CT) / v_SCORE_PY)

Case Else

ScoreChangeCalc = ((v_SCORE_CT - v_SCORE_PY) / v_SCORE_CT)

End Select

End Function

Where did this come from?
Select Case v_eval

You passed the function v_SCORE_CT and v_SCORE_PY
but your Select Case is looking for v_eval.

 

[Roger Carlson]

::Blush::

Now how did I miss that you actually posted the code? Danged if I know.
Sorry.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L