Power Query Formatted Text
Hi all,
Excuse my ignorance but until today, I was not aware that Excel had a query function. I have an Excel file and an Access file that I can use interchangeably with a Word Add-In. The add-in pulls data from a selected record in the Excel or Access file and puts the record field result in a document content control.
One short coming with the process is that when I extract the data from either and Access table or Excel file using ADODB where the column in the record source is formatted as Date, Percentage, Currency etc. the result in the Word form is the not the "literal" value displayed in the data source e.g., Saturday, 18 November 2023 is displayed as 11/18/2023, 10% is displayed as .10, $45.30 is displayed as 45.3
I can work around this issue in Access by creating a query that returns a text value formatted as either a long date, a percentage, or currency amount. When the data is extracted from the query using ADODB, the "literal" value is retrieved. I'm attaching and image of the query datasheet view and SQL view.
I understand that it is possible to create a query in Excel as well. I have managed to create a query, but I can't figure out how (if it is even possible) to do something similar with the Excel. Where my Excel query returns the date, percentage, currency values of its parent worksheet as text string values formatted as long date, percentage and currency.
Any help much appreciated.
|