Prev
Next
Forums User Experience Lawson Add-Ins for Microsoft Office

MS Addins Query GLAMOUNTS amounts in excel as text

 3 Replies
 0 Subscribed to this topic
 1 Subscribed to this forum
Sort:
Author
Messages


Sherry Shimek











Advanced Member



Posts: 43




Advanced Member



3/25/2015 4:08 PM

    We are querying the GLMAMOUNTS table using Lawson MS Addins MS Excel is treating all the monthly gl amounts as text.

    We have tried setting the format tab to Accounting1-4 and applying to all rows, but this doesn't affect the amounts only the other fields such as the company and other accounting string numbers.

    Is there a way in either MS Addins query wizard or excel to default these to numbers.  In large queries, using excel to change convert to numbers takes quite a bit of time.

    Thanks,

    Sherry Shimek

    Catholic Health Initiatives

    Sherry Shimek Catholic Health Initiatives Englewood CO


    JonA











    Veteran Member



    Posts: 1163




    Veteran Member



    4/1/2015 11:01 AM
      I don't know why that would happen. I've queried the table with and without the Accounting formatting and all the amounts display as numbers for me. But if you need to convert a column of text to numbers you can use the Text to Columns wizard in Excel. Highlight a column in Excel and go to Data > Text to Columns, Click Finish. Any cell in that column that looks like a number will convert to a number. You can only convert one column at a time.
      Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health


      Sherry Shimek











      Advanced Member



      Posts: 43




      Advanced Member



      4/1/2015 4:35 PM

        Thank you.  My guess is that MS Excel formats the amounts fields based on some rule but we cannot find it yet.

        We use the Text to Columns but in hundreds of thousands of rows of data an multiple periods of data that takes time we would prefer to conserve. 

        Thanks for your response.

         

        Sherry Shimek Catholic Health Initiatives Englewood CO


        dcaiani











        Veteran Member



        Posts: 52




        Veteran Member



        6/12/2015 5:32 PM

          I've never experianced numbers coming out as text.

          Try Leaving the format at "none".  Then click on "clear worksheet before send"

          If you still get numbers coming out as text just highlight the row(s) and at the top of that highlighted row Excel will give you a little error box - use the drop down and click "convert to number"