Microsoft KB Archive/287463

= ACC2002: Empty Result Set When You Run a Scalar Function Before You Close It =

Article ID: 287463

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287463



Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
When you create or modify a scalar function, and you then try to run the function without first closing it, the scalar function returns an empty column in the result set and displays the scalar function name in quotation marks as the column name. After you close the function and then run it from the Database window, the result set is populated, and the scalar function name, no longer with quotation marks, appears as the column name.



CAUSE
With scalar functions that have not been closed after initially being created or modified, Access improperly creates and displays the function column name, including quotation marks. This problem occurs when either a new scalar function has just been created or when an existing scalar function has been modified, and you are transitioning from Design to Datasheet view.



RESOLUTION
Before you run a newly created scalar function or a modified scalar function, close the scalar function. Then, either reopen the scalar function or run the scalar function from the Database window.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce the Behavior
 Open the sample project NorthwindCS.adp connected to Microsoft SQL Server 2000.  On the Queries tab, click New. Click Create Text Scalar Function, and then click OK. Type or paste the following code: CREATE function fn_scalar_1 (@parm int) returns bigint as begin return @parm * 5000 end  On the Query menu, click Run. Click Yes to accept the default function name. When you are prompted, enter 2 for the parameter value. Note that a datasheet that contains no value appears. Additionally, the column heading for the function is &quot;fn_scalar_1&quot;, including the quotation marks, instead of only the name, fn_scalar_1.</li> Save and then close the function.</li> Run the function to ensure it executes correctly.</li> Switch the function to Design view.</li> Change the multiplier in the function from 5000 to 50000.</li> Run the function again. Click OK to save the function.</li> Enter a value for the parameter. Note that again the datasheet is blank. However, if you close and re-run the function, it executes properly.</li></ol>

<div class="references_section">