I was trying to use x-family functions in loops and that required me to use variable in the xlookup statements. I am not having favourable results so far. While debugging, I stumbled upon apparent inconsistency when using variable in xlookup function. In the following, you would expect to have the same results, but no... Q2 is a number field with value like 20040228001 (11 digits to be exact)

var n as Int = 999999999999

WriteLn ("Value without variable = " + @xlookupr (@fn, 999999999999, "Vital!Q2", "Date1"))

writeLn ("Value with Variable = " + @xlookupr (@fn, n, "Vital!Q2", "Date1"))

The first statement consistently produced the right results, while the second one did not return the value at all or when I increased 9s in the n values to say 14 9s, it gave me sometimes value which is not the highest. That to some extent explained why my loop function failed to work.

Is there something I am doing wrong?

Erika,

I tried that, it does not gets any value just like before.  The hard 9999999999999 is a number as it is not put within the " ",  and variable n is also a number. I believe, the problem might be related to how Integers are stroed in the system like 12.000000 for 12.  One can change the display by changing display format but that would not change how it is stored internally.

For me this is an academic issue, as I think, I am leaning more towards going different route.

Quote:


We are dealing here with xlookupR statement, so the  appropriate format should be Number, I think. This is important issue because in loops, in order to go up and down, one has to be dealing with numbers and if we have issue with numbers, it will limit the use of x-family functions in loop to certain extent. Erika, thank you for offering to look into this.

Quote:


Mark,

Now it makes sense as to why it did not work. I have been making Unique sequential Numbers Using @num (@date) + 3 digits sequential number. So for a day it gives me 999 numbers which I can read and make sense.  The numbers will be 20040301001,  20040301002....etc. but that seems to be putting me over the Integer capacity. Well, I could be cutting down the first two digits and make 9 digits to bring it within the limit of Integer value, and since Y2K problem is behind us, I should be fine for another 96 years and that will perhaps out-live all of us.  Or I could settle for having just 99 unique sequential numbers for day and leave the year part untruncated and it will be good for another 110 years. What I was trying to do, was to locate the highest number and the counting down in the loop.

Eventhough I worked out an alternate method, this knowledge in the background will definitely help in the future. Thank you very much.

Thanks Mark. I will remember that.