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.

Attached Images

	Sample Data.png (30.4 KB, 7 views)
	Sample Data SQL.png (21.4 KB, 7 views)

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

Greg, are your destination cells formatted correctly?

Apparently not. When I create the query, the values are converted to "General" formatting. I can apply "Date", "Percentage" and "Currency" but that puts me back in the same position before I started. I need the values to be "text" formatted as long dates, percentages or currency.

Will attach a sample file if you think you can help. Thanks

Attached Files

Data Formatting Demo.xlsx (22.3 KB, 1 views)

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

What I did:

1.click on From Data Range to open Power Query and set source

2. click on Add Column > Custom Column - enter alias field name in place of "Custom" then enter expression: Number.ToText([Cost],"C")

Review
Number.ToText - Function | Power Query How
How do you cast an integer to a string in Excel's PowerQuery - Stack Overflow

This issue is resolved see: [SOLVED] ADODB Get actual displayed values
The solution is a workaround using an Excel Power Query.

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/