General Inquiries

Sage 300 CRE, using MS Query

  • 1.  Sage 300 CRE, using MS Query

    Posted 13 days ago
    I have the ODBC Drivers set up, and am using MS Query to pull data from Sage 300 CRE.

    I want to create a searchable by date check register from Query.

    The difficulty is the word "Check".
    I am using the file APM_MASTER__INVOICE_PAYMENT file, and would like to return the column .Check.

    However, Check is a command in SQL, and produces a Syntax Error.

    I have tried .[Check], [APM_MASTER__INVOICE_PAYMENT],[Check], Check_Number, (which is not a column), and Check_Number.

    No solution so far.

    Has anyone solved this riddle?

    thank you,
    Joe Harper


    ------------------------------
    Joseph Harper CPA, CCIFP
    Cfo
    Greater Dayton Construction Group
    Beavercreek OH
    (740) 607-1449
    ------------------------------


  • 2.  RE: Sage 300 CRE, using MS Query

    Posted 13 days ago
    Joseph,

    I'd suggest looking for other syntax errors in your query. I can query columns labeled Check with no issue.

    A searchable and interactive check register is a great use of Power Query and Power Pivot. You could expand it by joining related tables to be able to filter by commitment number, job number, etc.

    Feel free to contact me if you need assistance resolving this query issue. I'd be happy to assist at no charge.

    Best,

    Kevin Halme
    Constructive Tech Solutions
    Omaha, NE
    www.consttech.solutions


  • 3.  RE: Sage 300 CRE, using MS Query

    Posted 11 days ago
    Thank you!

    ------------------------------
    Joseph Harper CPA, CCIFP
    Cfo
    Greater Dayton Construction Group
    Beavercreek OH
    (740) 607-1449
    ------------------------------



  • 4.  RE: Sage 300 CRE, using MS Query

    Posted 12 days ago
    Have you tried setting it up as an alias, such as:

    SELECT [APM__MASTER__INVOICE_PAYMENT].[Check] AS AliasCheckField FROM APM__MASTER__INVOICE_PAYMENT]

    Sorry, I do not run Sage 300, but mess with SQL queries a bit.

    ------------------------------
    Alex Lee CPA/CITP, CCIFP
    Treasurer
    Roy T. Goodwin Contractors, Inc.
    Nashville TN
    (615) 242-5448
    ------------------------------



  • 5.  RE: Sage 300 CRE, using MS Query

    Posted 11 days ago
    Thank you!

    ------------------------------
    Joseph Harper CPA, CCIFP
    Cfo
    Greater Dayton Construction Group
    Beavercreek OH
    (740) 607-1449
    ------------------------------



  • 6.  RE: Sage 300 CRE, using MS Query

    Posted 12 days ago
    Hey Joseph,

    Can you try select * from APM_MASTER__INVOICE_PAYMENT?

    Thanks,

    ------------------------------
    Bruce Orr
    Chief Data Scientist
    Pronovos - Construction Analytics
    Atlanta GA
    bruce.orr@pronovos.com
    (678) 908-0087
    ------------------------------



  • 7.  RE: Sage 300 CRE, using MS Query

    Posted 11 days ago
    Thank you!

    ------------------------------
    Joseph Harper CPA, CCIFP
    Cfo
    Greater Dayton Construction Group
    Beavercreek OH
    (740) 607-1449
    ------------------------------



  • 8.  RE: Sage 300 CRE, using MS Query

    Posted 12 days ago
    Joe -

    This is a long standing glitch with MS Query that dates back quite a few years.  The Sage KnowledgeBase has an old article on it (Article ID 28308).  It arises when using MS Query as the ODBC interface since check is a reserved word.  To compare, if you did the same query using MS Access it normally works fine since you are not using MS Query as the gateway.

    There are several workarounds involving custom descriptions or using a different ODBC driver setting for that particular query, however Kevin hit on the best solution.  You alias the field names (and the table name as well if if includes CHECK in the title) and it normally works fine.  You do this in the External Data Properties of the query in Excel.  It's fairly easy however it's more detail than you'd want to cover here.  Feel free to contact me and I can walk you through the process.





    ------------------------------
    Michael Suhovecky
    Treasurer
    Brier Hill Associates
    Dublin OH
    (614) 439-2352
    msuhovecky@brierhill.net
    ------------------------------



  • 9.  RE: Sage 300 CRE, using MS Query

    Posted 11 days ago
    Thank you!!  I am going to try to create the alias'.  I may need more help, and I will search for the old record.

    ------------------------------
    Joseph Harper CPA, CCIFP
    Cfo
    Greater Dayton Construction Group
    Beavercreek OH
    (740) 607-1449
    ------------------------------



  • 10.  RE: Sage 300 CRE, using MS Query

    Posted 12 days ago
    If you use the following syntax you will receive a warning from SQL but it will work.  The warning can be bypassed -
    MASTER_APM_INVOICE_PAYMENT.[Check]

    Good Luck!

    ------------------------------
    Doreen Bell
    Director of Business Systems
    Tilson Technology Management
    Portland ME
    (207) 772-3427
    ------------------------------



  • 11.  RE: Sage 300 CRE, using MS Query

    Posted 11 days ago
    Thank you!! and Thank you!!

    ------------------------------
    Joseph Harper CPA, CCIFP
    Cfo
    Greater Dayton Construction Group
    Beavercreek OH
    (740) 607-1449
    ------------------------------