PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 06/12/2015 1:32 PM by  dcaiani
MS Addins Query GLAMOUNTS amounts in excel as text
 3 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Sherry Shimek
Private
Private
Advanced Member
(94 points)
Advanced Member
Posts:42


Send Message:

--
03/25/2015 12: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
    Private
    Private
    Veteran Member
    (2959 points)
    Veteran Member
    Posts:1041


    Send Message:

    --
    04/01/2015 7: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 - Supply Chain Analyst - Materials Management - MidMichigan Health
    Sherry Shimek
    Private
    Private
    Advanced Member
    (94 points)
    Advanced Member
    Posts:42


    Send Message:

    --
    04/01/2015 12: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
    Private
    Private
    Veteran Member
    (126 points)
    Veteran Member
    Posts:48


    Send Message:

    --
    06/12/2015 1: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"

    You are not authorized to post a reply.