"Table not found" in a custom function

[Mike Becker]

I am using a function that Ira listed on the forum several years ago that will determine the median value for a field.

The syntax checks out just fine but when I try to use the function in a calculated field in a report I get the error message "Table not found".

The name of the table is managerial data. I've tried all of the following but still am unsuccesful.

managerial data
managerial_data
managerial data.dbf
c:/temp/managerial data
c:/temp/managerial data.dbf

I've also tried the above options surrounded by quotation marks.

Does anyone have a feel for why the table can not be found?

Thanks,

Mike Becker

[Tom Cone Jr]

RE:

Mike,

Either the name of the table is wrong or your path is wrongly identified.

If you're hardcoding a physical path the backslash character causes a bit of trouble. In Alpha Five it's the symbol used for line continuation in the code editor. Substitute the Ascii equivalent, like this:

"C:" + chr(92) + "Program Files" + CHR(92) + "A5V5"

If this doesn't help show us your script.

Incidentally, I don't think you should be using forward slashes anywhere in a path statement, such as you did in your message.

-- t

[Mike Becker]

RE:

Tom,

Thanks for your quick reply. Sorry that I couldn't get back to you until after work today.

I was able to get the table recognized. However, I'm getting a new error message.

command: indx=Tbl.query_create()
filter expression not logical.

I'm using the function in a report and as Ira mentions in the below message I should be able to use one of the group breaks as the filter. In this case the group break is a character field "Stmnt_No". Just a note: "Rep_Score" is a numeric field.

So, I'm using the function as follows:

Median("managerial data.dbf", "Stmnt_No", "Rep_Score")

Thanks,

Ira's post starts HERE....................................



Headline: Faster Median Function with Filter
Message #: 00055054 Posted on: 09/01/1999 at 10:24:49
Author: Ira J. Perlow ([email protected])

Here is a Median function that is much faster and by specifying a filter, can compute the Median on a subset of records. For reports, just make the filter equal to the group break value.

function Median as N(tablename as C,filter as c, fieldname as C)

fil=UPPER(ALLTRIM(filter))
IF fil=".T.".or.fil=""
query.filter = ""
ELSE
query.filter = filter
END IF

' Perform query to filter and sort records
query.description = "Median Calculation"
query.order = fieldname
query.options = "I"

' Open table, do query and get count
tbl=table.open(tablename)
indx=Tbl.Query_Create()
count=indx.records_get()

' Exit with 0 value if no records
IF count=0
median=0
END
END IF




' Save original Index
orgindx=tbl.Index_Primary_Get()

' Set Query to new index for fetches
Tbl.Index_Primary_Put(indx)

' Fetch to middle of count
tbl.fetch_first()
i=1

j=int((count-1)/2)+1
while i
tbl.fetch_next()
i=i+1
end while

' Get the median value
fld=tbl.field_get(fieldname)
median = fld.Value_Get()

' Average adjacent values if count is even
IF mod(count,2)=0
tbl.fetch_next()
median = (median+fld.Value_Get())/2
END IF

' Restore original index
Tbl.Index_Primary_Put(orgindx)

' Close Table
tbl.close()

END

end function


Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

P.S. look at the capital letters in the e-mail address above :-)

[Stan Mathews]

RE:

I believe you are mininterpreting

"make the filter equal to the group break value"

try

Median("managerial data.dbf", "Stmnt_No = "+Stmnt_No, "Rep_Score")

if stmnt_no is numeric

Median("managerial data.dbf", "Stmnt_No = ' "+Stmnt_No+ " ' ", "Rep_Score")

if it is character.

[Stan Mathews]

RE:

stritran("mininterpreting","ni","si") = "misinterpreting"

[Mike Becker]

RE:

Hi Stan,

Thanks for your response. I really am pretty uneducated when it comes to xbasic so I do appreciate your assistance.

I tried your suggestion and it still would not evaluate. You had:

Median("managerial data.dbf", "Stmnt_No = "+Stmnt_No, "Rep_Score")

I changed it and was able to get it to evaluate without errors but it just gives me a value of 0 for every line in the report. Stmnt_No is a character field. I changed it to:

Median("managerial data.dbf", "Stmnt_No = '+Stmnt_No'", "Rep_Score")

Do you have any idea why it returns a value of zero?

Thanks, Mike

[Stan Mathews]

RE:

You have to phrase it correctly.

A filter such as this could be written.

"Stmnt_No = '123456'"

If you "drop in" the current value of a field or variable, etc. you have to supply the " ' " on either side of the character value. (The filter must consist of concatenated parts which constitute a character string.) The expression could be re-written

"Stmnt_No = ' " + "123456" + " ' "
substituting the field name
"Stmnt_No = ' " + stmnt_no + " ' "

Your expression is probably evaluating to something like

"Stmnt_No = 'stmnt_no' "

looking for stmnt_no equal to the characters "stmnt_no", which finds no matches and the median for such is zero.

[Mike Becker]

RE:

Stan,

I tried using
"Stmnt_No = ' " + stmnt_no + " ' "
and it still evaluates to zero.

I'm stuck and will try reading or searching the archive here to see if I can learn a little more before posting again.

Thanks, for your help.

Mike

[bestview]

Re: Median Function

I believe you are mininterpreting

"make the filter equal to the group break value"

try

Median("managerial data.dbf", "Stmnt_No = "+Stmnt_No, "Rep_Score")

if stmnt_no is numeric

Median("managerial data.dbf", "Stmnt_No = ' "+Stmnt_No+ " ' ", "Rep_Score")

if it is character.

Stan:

Back in 2004 you offered help for a function in Alpha5 for computing the median in a series of numbers.

I'm trying to do such a calculation now in multiple series of numbers in responses to a survey. I have downloaded Ira Perlow's code into a function, but so far I have been unable to make it work. My main problem seems to be in getting the filter correct. In trying to follow subsequent advice posted by you, I have come up with the following:

Median("newqesreesp","Question_N = ' " + "Question_N" + " ' ","Ans1score")



newqesreesp is the name of the table; Question_N is the character field that the group breaks on; and Ans1score is the field name with the numbers for which I'm trying to find the median.



I have been able to get this as far as computing to 0 (at least got by the error messages), but have not been able to make it work correctly.

Any help would be most appreciated.

--Al

[Stan Mathews]

Re: "Table not found" in a custom function

Al,

Note the difference between my suggestion and your expression.

Median("newqesreesp","Question_N = ' " + [B][COLOR="Red"]"[/COLOR][/B]Question_N[B][COLOR="red"]"[/COLOR][/B] + " ' ","Ans1score")

With the quotes you are filtering for

Question_N = the text Question_N ("Question_N")

remove the quotes and Question_N refers to the current value of Question_N.