locked
Format of SharePoint list not implemented in Access query, report and linked table. RRS feed

  • Question

  • HI,

    I have a list with few columns with multiple line of text. For example the below Status column has information and i have formatted it so that, each information is a new line.

    Now i link this list with my access 2010 and when i open the table the format is not at all present:

    When i make a query, form or report out of these tables; the format is same as the table (not correct).

    Can anyone please help me in telling me a way of how to have the same format in linked table and the access report.

    Please note the list's status column is a "Plain text" with multiples line of text.

    Regards,

    SAN


    Santhiya

    Tuesday, May 7, 2013 6:04 AM

Answers

  • Santhiya,

    what if you try this calculated field in a query:

    =Replace([Status], Chr(10), Chr(10) & Chr(13))


    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, May 15, 2013 10:09 AM
  • So for this formatting stuff to work, one has to first format properly in the SharePoint list. Give an extra space after each line, space highlighted in yellow and for and empty line click on enter twice (Enter shown in red arrow)

    If we follow all this, the report looks like the example below.

     


    Santhiya

    Thursday, May 16, 2013 5:33 AM
  • I don't know where the issue was, but we have solved it using Nz([Status],"-") instead of just Nz([Status]).

    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, May 15, 2013 5:08 PM

All replies

  • 1) I have a list with few columns with multiple line of text. For example the below Status column has information and i have formatted it so that, each information is a new line.

    2) When i make a query, form or report out of these tables; the format is same as the table (not correct).

    3) Can anyone please help me in telling me a way of how to have the same format in linked table and the access report.

    1) You are saying 'lines of text' so is that a 'record' per line or one record with carriage returns and line feeds?

    2) What tables as you showed a single field (column)?

    3) What is the SQL of the query you are using?

    Tuesday, May 7, 2013 6:22 AM
  • 1) You are saying 'lines of text' so is that a 'record' per line or one record with carriage returns and line feeds?

    Yes, each information is in a new line with carriage return and line feed.

    2) What tables as you showed a single field (column)?

    Its a project table in SharePoint with many columns having Project name, ID, Desc, Status, Owner etc. So i face this formatting difficulty in all those columns which are chosen "Multiple lines of text and Plain Text".

    3) What is the SQL of the query you are using?

    I link SharePoint list with access 2010. And then i make a query out of the table. Please see in normal view how the formatting looks in SharePoint:

    But when i open the table in Access, it shows like this:

    The formatting is disturbed.

    I then make a query:

    SELECT [PDPL].[Code], [PDPL].[Name], [PDPL].[Markets], [PDPL].[Description], [PDPL].[SalesTgtY3], [PDPL].[IncrGM%], [PDPL].[%Cannibalization], [PDPL].[Invstmnts], [PDPL].[ExtDevCost], [PDPL].[Phs], [PDPL].[Priority], [PDPL].[PrjctType], [PDPL].[Cmplx], [PDPL].[EstEndPh3], [PDPL].[Re-EstEndPh3], [PDPL].[Status], [PDPL].[PrjctOwnr], [PDPL].[Modified], [PDPL].[Company Name], [PDPL].[Segment],[PDPL].[Currency], [PDPL].[DASName]
    FROM [PDPL];
    

    and the formatting in Query is as below:

    And when i make the report out of this query, the formatting is as below:

    Hope you can see the difference now.

    So i wish to have carriage return and line feeds in my text of information and i do not know how to maintain the format.

    Can you please help?

    Thanks

    SAN



    Santhiya

    Tuesday, May 7, 2013 6:46 AM
  • Seems to me they are the same except for column width with word wrap.

    Do a replace in a query to see how many returns and how many line feeds there are in the record.

    Tuesday, May 7, 2013 7:22 AM
  • Dear Karl,

    Sorry but i dint understand what you meant by: "Do a replace in a query to see how many returns and how many line feeds there are in the record."


    Santhiya

    Tuesday, May 7, 2013 7:50 AM
  • I confirm the behaviour. The most annoying thing is that DLookUp in the Immediate window returns the correct formatting with proper amount of new lines. But the same DLookUp function in a report calculated field returns incorrect formatting.

    Here are some scenarios in reports:

    Direct binding to the field:

    1. Text Format = Plain Text - no new lines at all, nothing between different lines of text.

    2. Text Format = Rich text - a space instead of each new line.

    Calculated field:

    1. DLookUp

    1.1 Text Format = Plain Text - no new lines at all, nothing between different lines of text.

    1.2 Text Format = Rich text - a space instead of each new line.

    2. PlainText(DLookUp)

    2.1 Text Format = Plain Text - a space instead of each new line.

    2.2 Text Format = Rich text - a space instead of each new line.

    3. PlainText(Field itself)

    3.1 Text Format = Plain Text - a space instead of each new line.

    3.2 Text Format = Rich text - a space instead of each new line.

     


    Andrey V Artemyev | Saint-Petersburg, Russia

    Tuesday, May 7, 2013 6:37 PM
  • In a query design view add a field in a blank column like this --

    NumberOfReturns:  Len([TextField]) - Len(Replace([TextField], Chr(13), ""))

    NumberOfLinefeeds:  Len([TextField]) - Len(Replace([TextField], Chr(10), ""))

    Tuesday, May 7, 2013 6:44 PM
  • Andrey,

    Are you saying you set the control's format on the Report, to Rich Text, and it still failed to format correctly?

    Does it make a difference if the field is directly bound to the Tables?

    The Tables Field is formatted to Memo?


    Chris Ward

    Tuesday, May 7, 2013 6:57 PM
  • Chris,

    yes, still failed. Direct binding is described in the first scenario or do you mean anything else? Yes, and it isn't be another way because it's a linked table, thus I can't change any table/field property. It is Long Text in A2013, but it's the same as Memo.

    This SharePoint continues to make me angry everyday. Now this Office 365 site just don't want to be connected to Access but still works well in a browser. So tests are delayed for unknown amount of time.


    Andrey V Artemyev | Saint-Petersburg, Russia

    Tuesday, May 7, 2013 7:03 PM
  • Ugh :<

    Chris Ward

    Tuesday, May 7, 2013 7:50 PM
  • Andrey,

    Yes you are right, we cannot do any changes in the linked table or report of access 2010, and moreover changing column property in SharePoint from plain text to rich text does not serve the purpose either.

    The formatting works well if i "Import the source data into a new table in the current database". But this is increasing the work load. So for one list this method is okay, but for many lists we have to only link the SharePoint list to access.


    Santhiya

    Wednesday, May 8, 2013 4:55 AM
  • Karl,

    Thanks for the explanation.

    I added the fields in my Query design view.

    For my example, i get results as:  NumberOfReturns as "0" and NumberOfLinefeeds as "16"


    Santhiya

    Wednesday, May 8, 2013 5:05 AM
  • Andrey,

    Is there any way to format the Memo field in report's Status property? Can we use the Expression Builder from control source and do the formatting?

    Is there any way???


    Santhiya

    Wednesday, May 8, 2013 5:42 AM
  • Wednesday, May 8, 2013 8:35 AM
  • Santhiya,

    no, I tried 8 different combinations described in my post, nothing works.

    NumberOfLinefeeds as "16"

    That's it! Chris, what can we do with this? Replace with Chr(10) & Chr(13)?

    And the new information which may finally crash the brain. I don't know whether the issue in SP2010 or with Office 365, but it works without these problems in A2010 + on-premise SP2007 in both a table and a report.


    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, May 8, 2013 8:53 AM
  • Any progress?

    Andrey V Artemyev | Saint-Petersburg, Russia

    Tuesday, May 14, 2013 7:48 AM
  • Santhiya,

    what if you try this calculated field in a query:

    =Replace([Status], Chr(10), Chr(10) & Chr(13))


    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, May 15, 2013 10:09 AM
  • Andrey,

    I tried, in some items the formatting is absolutely correct, each information in a new line. But there are no line spaces.

    In few items formatting is little distorted. I think we are close to success, somewhere little minor corrections and we are all done I hope.


    Santhiya

    Wednesday, May 15, 2013 10:51 AM
  • Andrey,

    Bravo!!

    It worked, I tried to edit in SharePoint list by adding some spaces and extra lines and it reflected in Access 2010 Report. Formatting for memo is possible by putting "Replace([Status], Chr(10), Chr(10) & Chr(13))" in my Query, instead of the Status column.

    So all memo field can be formatted with this formula.

    You did it again.

    Thank you so much.


    Santhiya

    Wednesday, May 15, 2013 11:02 AM
  • That's why I hate SharePoint more and more every day. :)

    Santhiya, I'm very glad you've finally done it! This will be a lesson for me for the future as well.


    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, May 15, 2013 11:18 AM
  • Andrey,

    There is one more problem now. If status column is Empty, it shows an error.

    In such situations, can we modify the replace formula?

    Regards,

    Santhiya


    Santhiya

    Wednesday, May 15, 2013 11:21 AM
  • It's not a problem after all this mess. :)

    Replace(Nz([Status]), Chr(10), Chr(10) & Chr(13))


    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, May 15, 2013 11:23 AM
  • Status is empty in Query but in Report it shows "Error"


    Santhiya

    Wednesday, May 15, 2013 11:41 AM
  • Santhiya,

    do you mean adding Nz() function doesn't work?

    If so (but it really shouldn't), you can try the whole statement like

    IIf(IsNull([Status]),"",Replace([Status], Chr(10), Chr(10) & Chr(13))

    Anyway, I don't see any reason for this error rather than trying to run Replace90 function against Null value.


    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, May 15, 2013 12:49 PM
  • Hi Andrey,

    Both the solutions work well in Query, the field is empty and there is no #ERROR# message.

    But when I refresh my report it still shows me #ERROR# message.

    What can be done?


    Santhiya

    Wednesday, May 15, 2013 2:08 PM
  • Very strange, I can't reproduce the behaviour, works fine for me.

    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, May 15, 2013 4:36 PM
  • I don't know where the issue was, but we have solved it using Nz([Status],"-") instead of just Nz([Status]).

    Andrey V Artemyev | Saint-Petersburg, Russia

    Wednesday, May 15, 2013 5:08 PM
  • So for this formatting stuff to work, one has to first format properly in the SharePoint list. Give an extra space after each line, space highlighted in yellow and for and empty line click on enter twice (Enter shown in red arrow)

    If we follow all this, the report looks like the example below.

     


    Santhiya

    Thursday, May 16, 2013 5:33 AM
  • I am sorry, little change in the answer, adding a space after each line is not necessary. One can just press enter for sentence to start in a new line and for empty line they can press enter twice.

    Santhiya

    Thursday, May 16, 2013 10:14 AM