Hi All,
I have a report that is functioning well, however it contains a NL("Rows") function. The function runs well, however the results then remain if you run it again.
I.e. first time you run it it'll return:
A
B
C
Second time
A
B
C
A
B
C
Third time
A
B
C
A
B
C
A
B
C
etc.
How do I resolve this without having to delete the results (other than the first line) each time?
Thanks
Sounds like your report is corrupted. The leading cause of this is non-Jet Keywords in column A and row 1. With the behavior you are seeing, I wager there are non-Jet keywords in column A.
Steps to hopefully resolve the issue:
1. Put the report in Design mode.
2. Delete all values in column A and Row 1. (Don't delete rows, delete the cell contents)
2. Select a cell in your report and hit CTRL+End. This will take you to what Excel perceives as the last cell with data in it. With corruption, often it will take you thousands of rows down and perhaps many columns beyond your last column. Make note of the cell address.
3. Find the first row after your report data. Select that row plus all the rows one past the "last cell" found in the previous step. Example: if Excel thinks cell M24000 is the last cell, select up to row 24001.
4. Delete these rows.
5. Repeat the process for excess columns.
6. Save a copy of the report, close Excel, and then reopen the copy.
7. Test.
If you were able to remove the corruption, it will work as you would expect to only return ABC every time, not ABC, then ABCABC, then ABCABCABC, etc. If you still get the same behavior as before, I would recommend working with your partner or Jet Support to review the document for other issues, but the final answer may be to rebuild the report from scratch.
I responded last week but the response is still pending. Sounds like your report is corrupted. The leading cause of this is non-Jet Keywords in column A and row 1. With the behavior you are seeing, I wager there are non-Jet keywords in column A.
I would provide the report to Support for assistance. (I tried putting a solution in the previous message but I think it was too long)
Thank you!
I had a formula in there to auto hide some of the rows if the values were 0, it clearly wasn't working fully, I moved it to a second column which is Hide+? and now it works perfectly!
Thank you for your assistance.
That's great! It is a pretty common misconception that you can put an Excel formula in column A or row 1 as long as the result is a Jet Keyword. In reality, only Jet Keywords are allowed in those regions. At run-time, =IF statements are not Jet keywords which can cause what you saw in your report. Conditional hide should always go in column B and row 2.