Public Functions in SQL statements...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Manipulating MS Access records with excel VBA + ADO
    ... Data - Import External Data - New Database query ... Select the control button view dataor edit query. ... Look for SQL button to get the SQL ... Take the SQL statements and put into a string by adding double quotes. ...
    (microsoft.public.excel.programming)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)