Re: Public Functions in SQL statements...

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

--
HTH,
George


"Steve P" <SteveP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:94D2104C-8DB9-4587-B241-13AC90B5CE95@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: what is wrong with this statement
    ... What are you doing to _execute_ the SQL? ... where strMyQuery is a string/text variable containing the query you want to ... Query Builder. ...
    (comp.databases.ms-access)
  • Re: question about writing SQL
    ... I right-clicked my TableAdapter and selected Add Query... ... | the Use SQL ... Statements radio button | Delete. ... So I clicked the Query Builder button because I want to change the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Problem with a SQL...
    ... don't include those table in the Query Builder. ... What wrong with my SQL? ... INNER JOIN (main1 INNER JOIN class_ads ON (main1.id_main = ...
    (microsoft.public.inetserver.asp.db)
  • Re: VB.NET 2005 TableAdapter
    ... query and parameter query by "Query Builder". ... I can build a single parameter or multi parameters SQL Query by using ... Is that anyway to code the above SQL by "Query Builder" in TableAdapter ... Sure, you can build your sql statement (however, please use Parameters ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: VB.NET 2005 TableAdapter
    ... query and parameter query by "Query Builder". ... I can build a single parameter or multi parameters SQL Query by using ... say if the User select ClientCode and OrderType buttom on UI, ... Is that anyway to code the above SQL by "Query Builder" in TableAdapter ...
    (microsoft.public.dotnet.framework.adonet)