<div>
I suspect Excel is looking at the first few rows of data in the column and has found an entry that is &quot;&quot;&nbsp;so is setting the whole column to text.
</div>


Training Director

Laurence Martin

<div>
The column is formatted to General, but it's true that the first few entries are &quot;&quot;.<br />
<br />
I've changed the query to put 0 in if there's no date of birth, now all rows are showing the error flag.
</div>


<div>
Your expression does return a string, so no wonder that Excel reads it as such:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42814936-1"><span>IIf(IsNull([dob]),&quot;&quot;,FormatNumber(getage([dob]),0)) AS Age</span>
</code></pre>
Adjust it to return a number:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42814936-2"><span>IIf([dob]) Is Null, Null, GetAge([dob])) AS Age</span>
</code></pre>
or:<br />

<pre><code class="code-1 nolinks" id="code-20-42814936-3"><span>IIf([dob]) Is Null, 0, GetAge([dob])) AS Age</span>
</code></pre>
</div>


<div>
Yes, I've done that adjustment (but kept isnull([dob])) but Excel still insists it's text, whether I use 0 or Null.
</div>


<div>
IsNull() is an Access function that replaces Null with whatever you specify as the number, string, or date to use in place of Null. &nbsp;The contents of the IFF() function make no sense using IsNull().<br />
<br />
Building on Gustav's earlier function:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42815192-1"><span>IIf([dob]) Is Null, Null, VAL(GetAge([dob]))) AS Age</span>
</code></pre>
The Val() function will change any number-as-text to a number type. &nbsp;You want the result of the IFF() function to be Null or a number - no quotes around the number.
</div>


<div>
Sorry... I was thinking IsNull() (for SQL Server) instead of Nz() (for Access) - one of the hazards of the game. &nbsp;But my code above should work.
</div>


<div>
ok. &nbsp;I write before thinking.... &nbsp;You want the result to be null or a number. &nbsp;Just make sure the IFF() function returns Null or a number not surrounded by quote characters (which would indicate a string).
</div>


<div>
<div class="content wysiwyg-content">
I am exporting a number from Access 2010 to Excel and Excel is labelling the cells with the error Number Stored as Text.<br />
<br />
The export VBA uses:<br />
DoCmd.OutputTo acOutputQuery, &quot;Beneficiaries Report Anonymised&quot;, acFormatXLSX, , True<br />
<br />
The query field in question is:<br />
IIf(IsNull([dob]),&quot;&quot;,Forma<wbr />tNumber(ge<wbr />tage([dob]<wbr />),0)) AS Age<br />
<br />
[dob] is the person's date of birth<br />
<br />
The GetAge function is:<br />
<br />
Public Function GetAge(pDOB As Date, Optional pDate As Date = 0) As Integer<br />
<br />
'get age as of a specified date, or today<br />
&nbsp; &nbsp;GetAge = 0<br />
&nbsp; &nbsp;If Nz(pDOB, 0) = 0 Then Exit Function<br />
&nbsp; &nbsp;If pDate = 0 Then pDate = Date<br />
<br />
&nbsp; &nbsp;GetAge = DateDiff(&quot;yyyy&quot;, pDOB, pDate) + (pDate &lt;&nbsp;DateSerial(Year(pDate), Month(pDOB), Day(pDOB)))<br />
End Function<br />
<br />
When the number gets to Excel it is on the right of the cell, so that indicates it's a number, but it persists with the little green error flag.<br />
<br />
Am I missing something, or will I have to write some code to clear the error in Excel.
</div>
</div>


I am exporting a number from Access 2010 to Excel and Excel is labelling the cells with the error Number Stored as Text.

The export VBA uses:
DoCmd.OutputTo acOutputQuery, "Beneficiaries Report Anonymised", acFormatXLSX, , True

The query field in question is:
IIf(IsNull([dob]),"",FormatNumber(getage([dob]),0)) AS Age

[dob] is the person's date of birth

The GetAge function is:

Public Function GetAge(pDOB As Date, Optional pDate As Date = 0) As Integer

'get age as of a specified date, or today
   GetAge = 0
   If Nz(pDOB, 0) = 0 Then Exit Function
   If pDate = 0 Then pDate = Date

   GetAge = DateDiff("yyyy", pDOB, pDate) + (pDate < DateSerial(Year(pDate), Month(pDOB), Day(pDOB)))
End Function

When the number gets to Excel it is on the right of the cell, so that indicates it's a number, but it persists with the little green error flag.

Am I missing something, or will I have to write some code to clear the error in Excel.

OutputTo Excel but Excel thinks a number is text

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.