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.