troubleshooting Question

OutputTo Excel but Excel thinks a number is text

Avatar of Laurence Martin
Laurence MartinFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft OfficeMicrosoft AccessMicrosoft ExcelVBA
8 Comments1 Solution97 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros