Formatting SQL properly

I was given this SQL to help me with a report I am working on in Access. Only thing is this does not seem to be formatted correctly for access SQL. I am receiving errors using this code. Can anybody give me a hand formatting this correctly?


SELECT
    IV_ENTITY_ID,
    IV_NUMBER,
    IV_DATE,
    IV_CUKEY,
    CU_NAME,
    IV_TYPE,
    IV_SUB_TOTAL,
    MX_DATE,
    MX_AMT,
    MX_TYPE
FROM IV
LEFT OUTER JOIN CU ON CU_KEY = IV_CUKEY
LEFT OUTER JOIN MV ON MV_INV_NO = RIGHT('0000000'+ CONVERT(VARCHAR,IV_NUMBER),7)
LEFT OUTER JOIN MX ON MX_SEQNUM = MV_MXSEQNUM
HOTWATTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Juan OcasioApplication DeveloperCommented:
SELECT
    IV.ENTITY_ID,
    IV.NUMBER,
    IV.DATE,
    IV.CUKEY,
    CU.NAME,
    IV.TYPE,
    IV.SUB_TOTAL,
    MX.DATE,
    MX.AMT,
    MX.TYPE
FROM IV
LEFT OUTER JOIN CU ON CU.KEY = IV.CUKEY
LEFT OUTER JOIN MV ON MV.INV_NO = RIGHT('0000000'+ CONVERT(VARCHAR,IV.NUMBER),7)
LEFT OUTER JOIN MX ON MX.SEQNUM = MV.MXSEQNUM
0
ste5anSenior DeveloperCommented:
It's not the formatting, it's the syntax. Access (ACE/JET SQL) requires parentheses for the JOIN levels. Also there is no CONVERT in Access. And you missed the dots instead of the underlines.

So: Where do you want to run this SQL statement?
0
PatHartmanCommented:
When you don't know the syntax, use the QBE to build the SQL.  Add the three tables to the QBE.  Draw the join lines.  Double click on the center of the join lines to change the type to Left (but read the dialog carefully because they might need to be right joins if you put the tables in to the grid out of order.

This expression -- RIGHT('0000000'+ CONVERT(VARCHAR,IV.NUMBER),7)
should be
Format([Number], "0000000")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

HOTWATTAuthor Commented:
Thanks for the info. I have joined the tables together but where do I insert the expression Format([Number], "0000000")
0
PatHartmanCommented:
You need to switch to SQL View and modify the join

SELECT tblDefinedFields.FieldID, tblDefinedFields.FieldName, tblDefinedFields.FieldDescription, tblBookmarks.DocID
FROM tblDefinedFields Left JOIN tblBookmarks ON tblDefinedFields.FieldID = Format(tblBookmarks.FieldID, "0000000");
0
ste5anSenior DeveloperCommented:
tblDefinedFields.FieldID = Format(tblBookmarks.FieldID, "0000000");

Open in new window

This is in any relational design not necessary. Sure about the data types?
0
Bill PrewCommented:
Coming to this question late, but are we sure that:

IV_ENTITY_ID

should be:

IV.ENTITY_ID

It's certainly possible IV_ENTITY_ID is indeed the valid column name for that column in the IV table.  I have worked on large systems where that standard was followed...

@HOTWAT, when you look at the tables, what are the actual column names, do they have the table name at the beginning of each column, or not?


»bp
0
HOTWATTAuthor Commented:
Thanks for the help! I got it working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.