General Inquiries

Sage 300 CRE, using MS Query

  • 1.  Sage 300 CRE, using MS Query

    Posted 11-26-2018 14:15
    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 11-26-2018 17:52
    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-28-2018 11:26
    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 11-27-2018 07:04
    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-28-2018 11:27
    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 11-27-2018 09:14
    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-28-2018 11:27
    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 11-27-2018 09:37
    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-28-2018 11:28
    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 11-27-2018 10:01
    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-28-2018 11:29
    Thank you!! and Thank you!!

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



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

    Posted 12-19-2018 09:27
    I still have not cracked this.  I have tried the brackets.  I have tried setting up an alias.

    I will admit it is likely user error.  I know that.

    I think I need a book on SQL programming!!!

    If anyone wants to write the SQL code for the Sage 300 CRE query for either the APM_MASTER_CHECK file to get Check, Check Date, Amount and Vendor fields in one return (able to be limited by check date range), or the APM_MASTER_INVOICE_PAYMENT to get the Vendor, Check, Amount Paid and Date Stamp in a query (again able to be limited by the dates stamp range) I would love to see it.





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



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

    Posted 12-19-2018 10:04
    This is the SQL statement from Crystal pulling those fields

    SELECT "APM_MASTER__CHECK"."Check", "APM_MASTER__CHECK"."Check_Date", "APM_MASTER__CHECK"."Amount", "APM_MASTER__CHECK"."Vendor"
    FROM "APM_MASTER__CHECK" "APM_MASTER__CHECK"



    ------------------------------
    William Kormoski
    President, Sage 300 CRE Consultant
    Kormoski & Associates LLC
    Mount Juliet TN
    (615) 351-4953
    ------------------------------



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

    Posted 12-20-2018 14:19
    Bill -

    You are correct in the syntax for Crystal and it's very similar for Access.  I believe the problem in this case is that Joe's trying to bring the end result into Excel, which normally uses MS Query.  It's the MS Query query portion of the chain that can't handle the reserved word "Check" and causes the crash and burn.  You can solve the Excel version by using aliases for the tables and fields names and using the backtick symbol when dealing with the Check field (see separate posting).

    Given how important MS Query is when working with Excel, I'm always amazed how little documentation exists for it.  Most of the time, it's trial, error and ulcers.

    ------------------------------
    Michael Suhovecky
    Treasurer
    Brier Hill Associates
    Dublin OH
    (614) 439-2352
    ------------------------------



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

    Posted 12-20-2018 14:01

    Joe –

     

    Here is the SQL statement that will return the results you want.  From your example, it looks like you have your ODBC data source set to Standard Descriptions, so I've used that methodology.  I copied this out of an actual query I use, so I can confirm it works.  Copy it into the connection string box of the Excel Query:

     

    SELECT

    APInfo.Bank_Account as "Bank Account",

    APInfo.`Check` as "Check No",

    APInfo.Check_Date as "Check Date",

    APInfo.Amount as "Amount",

    APInfo.Vendor as "Vendor"

    FROM APM_MASTER__CHECK APInfo

    WHERE (APInfo.Check_Date>=?) AND (APInfo.Check_Date<=?)

     

    Some notes:

    1. Because "CHECK" is a reserved word, you can't use the default table name APM_MASTER_CHECK.  You get around this by using an alias in the FROM statement (In this example, I used "APInfo").  You then use the alias in lieu of the table name in the SELECT and WHERE clauses.

     

    1. I also used aliases for the field names in the SELECT statement.  In this situation, you surround the alias names in double quotes.

     

    1. The tricky part in this example is the APInfo.Check field (the alias for the APM_MASTER_CHECK.Check field).  MS Query will error out attempting to read the field before it can be aliased because it contains the word "Check".  You get around this by enclosing the word "Check" using the backtick symbol (`), usually found on keyboards to the left of the number 1 key.

     

    1. Because checks for all bank accounts are stored in APM_MASTER_CHECK, I added the Bank Account field to the above example since you'll likely want to filter by account.  You could also include it in the WHERE statement.

     

    Hope that helps.

     

    Michael Suhovecky

    Brier Hill Associates, LLC

    614-439-2352 (Cell)

    614-467-3741 (Fax)

    msuhovecky@brierhill.net