I have run into a problem using a text string as the key value while doing either lookup( or superlookup(. The same arrangement of text works as it should when it is used in a Find/Select from the data sheet. The equality or inequality tests that I have run in code also give the correct results. I will not be surprised if this is due to something that I have missed, and I am curious as to what it might be. In the meantime, I will just apply stripchar( to condense the text to a simpler form.
This turned up as I continue to flatten out an old database application that consists of many separate single purpose databases. There are different types of data used as key values between some of the different pairs of databases. The key values that are giving me problems now are file numbers that identify different case histories. These numbers are typically in the form of 0-0-00, with an occasional letter before or after the numeric part of the file number, such as W1-0-45a. Once I modify the text format of the key values and get the lookup function to work with these file names, I will use the lookup statements in a procedure to move the data in the database that is being searched into the main database, thus eliminating one more ancillary database.
This is an example of a lookup that does work with the main database and another database that contains names and addresses. In this case, the key values are integers.
A lookup of the same construction, but using integers with hyphens, as text, does not work for me, giving me the error “superlookup( function failed to match.” If I remove the non-numeric characters (0-0-05 becomes 0005), this statement runs without error.
R = superlookup(“Different Database”, |||FileNumber = ««FileNum»»|||, |||”Data to retrieve, in date format”|||)
I would like to know if this is a known problem. If it is not, I would welcome any information about how to resolve this without the added step of first modifying the key value text format.
Panorama should not care what kind of characters are in the text. There is nothing special about letters vs. numbers vs. punctuation symbols or anything else.
I wonder if perhaps the data has somehow gotten some invisible characters into it that are causing the problem. Perhaps in stripping out the dashes you are also stripping out invisible characters that are actually causing the problem.
Thank you for your insight. You have much more experience with this than I do. I guess it would be pretty hard for me to investigate this further. Perhaps if I could look at the binary code of what is stored in the cells, but that is over my head. I will strip out the dashes and move on.
A few minutes after I replied, the bulb went on over my head. The text data that I was using for a key, such as 0-0-00, was not the problem. The dashes were not interfering at all. When I looked again at the test comparisons that I was running, I realized that I was misinterpreting the error message that the procedure was giving, “superlookup( function failed to match.” I had the idea that it was telling me that something in the structure of the superlookup( formula was incorrect, and the only place that I could see in the statement where a mismatch could occur was in the comparison of the two key values. While I was messing about with single stepping through the loop in the procedure, I finally realized was that the error occurred while searching the first record, because the two key values were not equal from the start and the program had stopped on the error. So, after adjusting my lookup statements to include a default value in case of an error, the blasted procedure ran without a hitch. It all works fine now.
R = superlookup(“Different Database”, |||FileNumber = ««FileNum»»|||, |||”Data to retrieve, in date format”|||, “default”, “”)
The reason that I did not run into this problem before was because the last two databases that I tried this on had exactly the same number of records and those were in the same order, including the listing of the key values. The two databases that gave me the problem were very different, with the first one having twice as many records as the database that was searched.
In the future, I will not be leaving out a default value in my search statements.
It does help to talk to someone else when a problem comes up.