Apache OpenOffice Community Forum

My database table includes fields that are currency number format. I run my queries from Calc via connected database/registered database, F4. In the top section showing the database, I select the query from listing in left side, it populates the right side of the top section with the data results. This includes the fields that are shown as currency ($ USD) just fine. The top section results are correct in appearance for all fields/columns.

Next step is to 'copy' the query results into the Calc sheet. I grab the query name and drag it to the sheet and drop it. The sheet is then fully populated with the data results for that query; however, the fields/columns that are currency show up as numeric only, not currency. Would be helpful to have the same formatting carry over into the Calc sheet. Of course I can change the columns format to currency where needed. It's tedious and time consuming to have to do this when these queries are run daily, and one database may have 15 queries all run into separate Calc sheets, with multiple currency formatted columns.

I have tried everything I can think of, but nothing seems to help to keep the original format when copied to the Calc sheet. The table(s) fields are all set for currency, the queries fields are set for currency also.

If anyone has an idea of how I can get the database query results to carry over to Calc and keep field/column format, please respond. Thanks in advance.

Hello,

usually I use two sheets in this cases.
One gets the raw data by query,
the second either just copy the data from the first by firmula or modifies this, if necessary.
The second sheet can hold formatting,
wich is not changed by refreshing the data.

J.

Thank you Mountaineer, It's a lot of copying. I'll see if this is faster than simply modifying the columns format on all the sheets.

[Tutorial] Using registered datasources in Calc
Check "keep formatting" in the extra options of the linked database range.

Villaroy, thank you, this is a much better idea, however, still doesn't work.

"Properties of linked import ranges in a spreadsheet:
menu:Data>Define..., pick your range hit button [More]
- Contains column labels [always true for imported ranges]
- Insert or delete cells [on refresh adjust adjacent formulas to the new size of an imported range. Should be set by default but isn't]
- Keep formatting [... of the spreadsheet cells on refresh. Should be set by default but isn't]
- Don't save imported data [save space and enforce a manual refresh after loading]
After changing a property, hit [Modify], then [OK]
Edit: Since AOO 4.1 "Keep formatting" and "Insert or delete cells" are checked by default. This means that import ranges do work out of the data source box. "

I'm running version 4.1.11, the 'keep formatting' was not checked by default. I've checked/selected keep formatting for the db range, hit modify... saved the file, then tried to drag a fresh new query result to the calc.... still not keeping the currency columns, they are just numeric again. May be a bug/ er.. feature... may be because I am still on old windblows 7 here, but technically the underlying os shouldn't make that much a difference in this particular thing, wouldn't think, but have certainly seen much stranger things happen in versionitis. (ex software engineer here, OO was originally our product long ago, haha)

Villaroy, sorry spoke too soon. This seems to have resolved it!! Thanks. I had to change my Calc form after selecting 'keep formatting', once I reformatted the columns as currency, and saved the form, now it's working as it should. You're my hero!