can't change anything in query property sheet (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 19, 2013
Messages
16,555
format is a property and as such it does not get exported to excel, only the underlying value gets exported. If you do export 'with formatting' that is referring to character format - bold/italic/font/size etc.

So if you are using the format function - that converts the underling value to text - and that sorts in a completely different way - April comes before March for example

This seems a completely different question to the original post
 

PNEfinney

New member
Local time
Today, 13:21
Joined
Oct 3, 2019
Messages
19
Appreciate your help, though I disagree that its entirely a different question.

Whenever I create a query from a table, the query will not allow me to amend the field properties, nor does it bring in the format from the table I am querying on. All I can see is the blacked out version I posted before.

In past reports, I have been able to wrap the field queries in a format( , "Currency") type formula for currency and percentage points and this has resolved it. When I Copy and Paste (not export) the report maintains all the formatting in Excel that has been added by the formula.

This particular report, I am backwards engineering a set of reports someone else has created, manually, in EXCEL, which is why I need to convert the full date eg. 20/12/2019 into DEC 20. I have done a formatting query for from the field next to it (now improved, thanks to a suggestion earlier in the thread), but after running, copying and pasting into EXCEL, the pre-set pivots are showing all the MMM YY columns out of order. The data copied from ACCESS has the format in EXCEL as 'General', which leads me to believe that the query has created the detail as Short Text, but I cant check or amend that as the options are blanked out.

Without being able to change the properties, because the properties table is blanked out, I am unable , without formatting that column in EXCEL for 9 reports every month, to have the pivots order themselves properly.

This is an issue I have had to find workarounds for for about 2 years and cant find any other reference apart from this thread, but the solution doesn't seem to be working.

I am running ACCESS through Office 365 on a work account.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 19, 2013
Messages
16,555
suggest upload a copy of a db where this behaviour is apparent. with regards the excel issue that is a separate problem. The format function returns text and with the best will in the world, excel is not going to recognise 'Jan 20' as a date.
 

PNEfinney

New member
Local time
Today, 13:21
Joined
Oct 3, 2019
Messages
19
ok, cant upload anything as it is sensitive data.

Ill keep trawling the internet.
 

Users who are viewing this thread

Top Bottom