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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.