SQL Query to change field properties in Table from text to number/currency in query results?

[DanInAccess]

Hello All,

For a little over one year, I have been working mostly in pre-made forms in an Access 2013 database at my company with no view of underlying tables and queries. Recently I was given Administrator access to all of the underlying tables, queries, forms and reports. I would like to begin building my own queries that will be connected to Excel using PowerPivot. (New boss is more comfortable using Excel Pivot tables as a dashboard.)
For the last twelve years our company has used a Copy/Paste workflow to get a full table (@2100 rows) into Excel.
I have become fascinated with the power of queries and see them as a more elegant and efficient method to pull in this data as we only use a few select fileds/columns in our reports.

Problem: All fileds needed for export are formatted as short text in the table. Even those containing dollar values. Example entries for the fields are: N/R, CMPLT, FY22, FY23, DFRD, or $6,753.

Question: Is it possible to write an SQL query that returns every record but disregards the "real" text entries and only captures the fields in a record that has a dollar value and changes the format property to "Currency?"

I've been told there is no chance to change the formatting properties of the underlying table.

This is my first post and I apologize for being so long-winded.

Thank you,
Dan

[Micron]

Probably several things you can do, depending on the factors involved.
If you link the spreadsheet(s) as a table(s) you will always have a link to current data. Then create a make table query (that you use once) to create a table that contains only the fields you want. After this, you check that Access correctly formatted the fields (e.g. currency as number & currency type) according to your needs. Thereafter you use an append and/or update query to keep the Access table current. From that you develop other queries, forms and reports.

If it is a different spreadsheet every day/week/etc. that will be more difficult. You import but I suspect it would be a manual operation as the sheet or workbook names likely change every time. After the import, you can still update/append as above. It is said that Access decides on a data type based on about the 1st 8 rows, so you might import as text anyway.

If you're saying that a sheet column might have currency formatted as text but also other values that are not currency (N/R, CMPLT, FY22, FY23, DFRD, or $6,753.), then Automation between Access and Excel is one solution, albeit a complicated one. I see that as having to examine every cell value in a column in order to see if it begins with $ or some similar test.

Alternatively, an Access query could call a function which can determine if the value resembles currency but code would still be required. What that looks like would depend on where the data is. IMO, best scenario would be sheets linked as tables.

I've been told there is no chance to change the formatting properties of the underlying table.

Are they OK with you spending several hours developing something to deal with that rather than formatting a sheet column in a matter of seconds? That seems absurd, but then again I don't know what you know about the business.

[Gicu]

To change the data types in your queries you would simply use the type conversion functions:
https://support.microsoft.com/en-us/...3-87ac8d1a2202
So your short text fields that hold currency data would look like this:
Amount_Currency:CCur([AmountShortText]) - where [AmountShortText] is your table field

To export your data to Excel and create the pivot table on it you might want to have a look at my free utility:
http://forestbyte.com/ms-access-util...able-designer/

Cheers,