Avatar of Laurence Martin
Laurence Martin
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

OutputTo Excel but Excel thinks a number is text

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.
Microsoft AccessVBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon