Need Help with my Execute statement


#1

I’m using the execute statement for the first time building a multi=field Find/Select statement. It seems to work as long as all the fields are text type. But I have a stockno field that is numeric and is giving me grief.
I initialize (in .Initialize) a fileglobal srchstockno to “”. On a form I have a textedit superobject to capture the variable srchstockno The option to use the real StockNo field’s attributes is ON. StockNo is numeric with no decimals. I put 89450 in the TDSO and enter a the procedure that’s building the formula. Like myexc = "Select StockNo contains " + srchstockno. When I message it, it says, Select StockNo contains 89450. I copied the code in the pdf reference for execute that tests for error and get an A-OK. It says Select StockNo contains 89450 But when I run it, I get a type mismatch, Numeric used when text was expected.

I could change my construction to say Select Str(StockNo) contains “89450” if that is necessary.

I’m guessing that if I have to select/find on a date field, I’ll have similar grief.

So my construction of formula works great with multiple text type fields - the variables Find, Select, OR, AND lay in as they should. Works great. What’s the trick to building a formula that with EXECUTE a CONTAINS on a numeric field?

I tried the same contains in the regular Find/select dialog under the Search Menu and Panorama was happy with that


#2

I tried it with the str(stockno) construction mentioned previously

IF srchstockno <> ""
exformula = "Find str(StockNo) contains " + chr(34) + srchstockno + chr(34)
ENDIF
execute exformula

I no longer get the type mismatch warning, but it also doesn’t Find the record


#3

Okay, looks like the
IF srchstockno <> ""
exformula = "Find str(StockNo) contains " + chr(34) + srchstockno + chr(34)
ENDIF

Does work. So the trick was to turn the numeric field into text with the str() function. I’m guessing the use of DatePattern would come into play if I were dealing with dates.

So myvar = “Find StockNo contains 89450” then execute myvar didn’t work but myvar = "Find str(StockNo) contains " + car(34) + srchstockno + car(34) and execute myvar did work. Why didn’t it like the other myvar?


#4

Paul
Trying to use the contains operator on a numeric field does not make much sense. This would hold true for other operators like beginswith, endswith , like etc. If you think about it, these would only work with text . Does a number contain another number and, if so, would it mean that it is larger than that number or equal to that number or divisible by that number? This is why you need to convert the argument to text.

The reason the test you ran with the message code seemed to work is because Panorama X has a much more intelligent engine to better guess your intended use of the values as text or numeric. Jim noted this as a feature and stated that we will most likely not have to worry about using str( or val( functions in most cases. I think this is what caused you the confusion when the message statement converted the values for you.


#5

Assuming this is in the right category, this is a Pan 6 question, not Pan X. The message code worked because srchstockno was a variable containing text, and there wasn’t any type mismatch. The Select statement was a type mismatch, because it was trying to compare the number in the StockNo field to the text in the srchstockno variable.

Dave


#6

I guess I didn’t take note of the proper category. :confused:


#7

I didn’t quite get it right either. I forgot to take into account that this was an execute statement. The select was trying to do a text comparison on two numbers.

Dave


#8

Yes, this a Pan6 issue. I understand the weirdness of doing a contains on a number field. But I tried it in the Select/Find dialog under the Search menu and it worked as expected - no mismatch there.

I was building a select or find sequence I put into a variable and then executed the variable. There can be up to five different field comparisons connected with an “or” or an “and”. four of the five are text. When I saw the number field, I though about changing that to an equals but there was an agreement in design to do a “contains”.

It sounds like you are saying that if I had constructed Find StockNo Equals 89450 it would have worked like that.

I was mislead on the “contains” because it worked in the Search menu Find/Select dialog. BUT … When I recorded it - what a concept (when will I ever learn) - and played back the recording, it showed:

Field "StockNo"
Select str(StockNo) contains “2”

Which is the same construction I came up with. So Pan6 was smart enough (i.e. Jim was smart enough) to see the “contains” and do the conversion behind the scenes in the regular Find/Select dialog.

In the future, when I want to build an execute statement, I’ll turn on the recorder and do what I intend the execute to do. Then look at the recording in a procedure to see how the string is suppose to be constructed.

So this issue is solved.