Public Functions in SQL statements...

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
Click to expand...

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.
 
Back
Top