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?
 
PatHartmanConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
HOTWATTAuthor Commented:
Thanks for the info. I have joined the tables together but where do I insert the expression Format([Number], "0000000")
0
 
PatHartmanConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.