Access Data retrieved using ADODB loses formatting.

[gmaxey]

Hi,

I have a form created in Microsoft Word that I populate with data from either and Excel Worksheet or Access Table. Some the data columns are formatted as currency and percentages. Using Excel and the following connection string, the data is returned in the correct format e.g., 50% or $123.34


fcnConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strFileName & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";" 'Excel 12.0 Xml;HDR=YES;IMEX=1

Adding the IMEX=1 results in the actual formatted data being returned.

However, with Access using:


fcnConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & strFileName

The data loses its formatting e.g., 50% appears as 0.5 and $123.34 appears as 123.34

Is there some way to get data using ADODB such that the format of the data in the record is returned?

Thanks.

[OBP]

This may be better as a question on the Word Forum.
Does the Word VBA also have an extensed properties for Access?
I do have one suggestion, but it depends on if you will be doing any calculations with the data in word.
Before transferring the data you could place the formatted data in Access in to a temporary table that uses strings instead of number values.
Thus it would be stored as 50% rather than 0.5 etc.

[gmaxey]

OBP,

I don't know what "extensed properties for Access" is. So I can't answer your question. Word or Excel, it seems the issue is finding the right connection string (like adding IMEX=1 to the Excel connection string) so the data returned is as it is displayed in the Access table.

[OBP]

Sorry that should have been extended properties.
I do not know much VBA for manipulating Access from outside of it, which is why I suggested the Word forum.
Have you conducted a search of the Word Forum for doing this?

[gmaxey]

OBP,

Yes, I've looked through both Word and Excel groups but can't find any connection string that includes extended properties like "IMEX=1" for Access so that the data returned is converted to a string value representing the same value displayed in the database.

Thanks.

[OBP]

What happens if you use a query instead of the table, you can reinforce the formatting in the query or even convert it to a text string.

[gmaxey]

OBP,

Yes, that would probably work. I'm not very familiar with Access at all (just use it as a data source). I've cobbled together the following SQL, which does result in the data coming over ADODB as strings:

SELECT Table1.Code, Table1.Name, Table1.Rank, Format([Percentage],"Fixed") AS [Percent], Format([Value],"$#,##0.00") AS Val
FROM Table1;

The problem is that in order to create the query, the column names "Percentage" and "Value" are changed to "Percent" and "Val" (can't find a way to keep the same column names).

Any suggestions for a better query structure? Thanks.

[OBP]

I know it is a bit around the houses but you could base another query on your new one and change the names back, the second one may not need the formatting.
Your string only has to call the second query and it will run the first one automatically.

[gmaxey]

OBP, I couldn't figure out how to do what you suggested. When I attempted to create the new query based off the first query, it kept changing the column name to Expr1 and Expr2 and wouldn't let me rename them "Percentage" and "Value"

[OBP]

Here is an example
Original SQL
SELECT [File Paths].FilePath, [File Paths].FileName, [File Paths].keyfield, [File Paths].[FileName] AS FileName1
FROM [File Paths];
New SQL
SELECT [File Paths Query].[FilePath], [File Paths Query].[keyfield], [File Paths Query].[FileName1], [File Paths Query].[FileName1] AS FileName
FROM [File Paths Query];

It switches the Field name FileName to Filename1 and then from Filename1 to Filename.

[gmaxey]

OBP,

Thanks for all of your help. I decided it was easiest just to rename my base table fields "xPercentage" and "xCost" and then use as single Query using the field names required (changed Value to Cost as Value must be a reserved named):


SELECT Table1.Code, Table1.Name, Table1.Rank, Format(Str([Table1.xPercentage]), "0.0#%") AS [Percentage], Format([Table1.xCost],"$#,##0.00") AS [Cost]
FROM Table1;

All seems to work for my purposes. Thanks again!1