Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Statement

Posted on 2014-12-18
9
Medium Priority
?
121 Views
Last Modified: 2014-12-18
I hate coding....Someone help

strSQL = "SELECT DISTINCT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.BC1Chng1, Final_Table.BC1Chng2, Final_Table.BC1Chng3, Final_Table.BC1Chng4, Final_Table.BC1Chng5, Final_Table.BC1Chng6, Final_Table.BC1Chng7, Final_Table.TotalBC1, Final_Table.BC2Chng1, Final_Table.BC2Chng2, "
Final_Table.BC2Chng3, Final_Table.BC2Chng4, Final_Table.BC2Chng5, Final_Table.BC2Chng6, Final_Table.BC2Chng7, Final_Table.TotalBC2, Final_Table.BC3Chng1, Final_Table.BC3Chng2, Final_Table.BC3Chng3, Final_Table.BC3Chng4, Final_Table.BC3Chng5, Final_Table.BC3Chng6, Final_Table.BC3Chng7, Final_Table.TotalBC3, Final_Table.BC4Chng1, Final_Table.BC4Chng2, Final_Table.BC4Chng3, Final_Table.BC4Chng4, Final_Table.BC4Chng5, Final_Table.BC4Chng6, Final_Table.BC4Chng7, Final_Table.TotalBC4, Final_Table.[Adjust Category], Final_Table.Remarks, Final_Table.UpdatedBy, Final_Table.UpdatedDate"
strSQL = strSQL & "FROM Final_Table INNER JOIN dbo_tblOrgLook_master ON (Final_Table.CostCen = dbo_tblOrgLook_master.CostCenter) AND (Final_Table.PEC = dbo_tblOrgLook_master.PEC) WHERE dbo_tblOrgLook_master.Analyst " & Analyst & " AND dbo_tblOrgLook_master.Org " & Org & " AND dbo_tblOrgLook_master.CostCenter " & CostCenter & " AND dbo_tblOrgLook_master.Fund " & Fund & " AND dbo_tblOrgLook_master.PEC " & PEC & " ORDER BY Final_Table.[Item Number:]"

Open in new window


For some reason this does not work?
0
Comment
Question by:Jass Saini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40507785
Define "does not work".

Does it give you an error message or is it not returning the records you expect.  

If the former, please post the error message.  If the latter, can you describe what you want the query to do.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40507786
At least you missed a Space here:

strSQL & " FROM ...

/gustav
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40507788
try inserting a space before "FROM Final"

strSQL = strSQL & "FROM

should be
----------------------------v-----
strSQL = strSQL & " FROM
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 48

Expert Comment

by:Dale Fye
ID: 40507805
My guess is that it has to do with your Where clause, which looks like:

"WHERE dbo_tblOrgLook_master.Analyst " & Analyst & " AND dbo_tblOrgLook_master.Org " & Org & " AND dbo_tblOrgLook_master.CostCenter " & CostCenter & " AND dbo_tblOrgLook_master.Fund " & Fund & " AND dbo_tblOrgLook_master.PEC " & PEC

Generally, you would need to put an = sign in there somewhere, and for fields that are non-numeric, you would have to offset the value with quotes.  So, assuming that Org is a string and the others are all numeric, it might look like:

" WHERE dbo_tblOrgLook_master.Analyst = '" & Analyst & "' " _
& " AND dbo_tblOrgLook_master.Org =" & Org _
& " AND dbo_tblOrgLook_master.CostCenter =" & CostCenter _
& " AND dbo_tblOrgLook_master.Fund =" & Fund _
& " AND dbo_tblOrgLook_master.PEC =" & PEC
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40507806
how are the variables Analyst, Org, CostCenter, Fund, PEC defined?
there should be an equal sign before them and should be treated depending on the Data Type
i.e.,
if Analyst is Number
WHERE dbo_tblOrgLook_master.Analyst = " & Analyst & "

if Analyst is Text
WHERE dbo_tblOrgLook_master.Analyst = '" & Analyst & "'

etc...
0
 

Author Comment

by:Jass Saini
ID: 40507959
Sorry guys..line 2 is red....
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40507982
You miss between line 1 and 2:

" & _
"

and the previously mentioned missing Space on line 3:

strSQL = "SELECT DISTINCT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.BC1Chng1, Final_Table.BC1Chng2, Final_Table.BC1Chng3, Final_Table.BC1Chng4, Final_Table.BC1Chng5, Final_Table.BC1Chng6, Final_Table.BC1Chng7, Final_Table.TotalBC1, Final_Table.BC2Chng1, Final_Table.BC2Chng2, " & _
"Final_Table.BC2Chng3, Final_Table.BC2Chng4, Final_Table.BC2Chng5, Final_Table.BC2Chng6, Final_Table.BC2Chng7, Final_Table.TotalBC2, Final_Table.BC3Chng1, Final_Table.BC3Chng2, Final_Table.BC3Chng3, Final_Table.BC3Chng4, Final_Table.BC3Chng5, Final_Table.BC3Chng6, Final_Table.BC3Chng7, Final_Table.TotalBC3, Final_Table.BC4Chng1, Final_Table.BC4Chng2, Final_Table.BC4Chng3, Final_Table.BC4Chng4, Final_Table.BC4Chng5, Final_Table.BC4Chng6, Final_Table.BC4Chng7, Final_Table.TotalBC4, Final_Table.[Adjust Category], Final_Table.Remarks, Final_Table.UpdatedBy, Final_Table.UpdatedDate"
strSQL = strSQL & " FROM Final_Table INNER JOIN dbo_tblOrgLook_master ON (Final_Table.CostCen = dbo_tblOrgLook_master.CostCenter) AND (Final_Table.PEC = dbo_tblOrgLook_master.PEC) WHERE dbo_tblOrgLook_master.Analyst " & Analyst & " AND dbo_tblOrgLook_master.Org " & Org & " AND dbo_tblOrgLook_master.CostCenter " & CostCenter & " AND dbo_tblOrgLook_master.Fund " & Fund & " AND dbo_tblOrgLook_master.PEC " & PEC & " ORDER BY Final_Table.[Item Number:]"

Open in new window

0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40508030
Unless there is some reason for this query to be embedded, you will have better success using the QBE to build SQL statements if you are unfamiliar with SQL syntax.  You can have criteria that references forms to supply variable values. The WHERE clause will end up looking like:

WHERE dbo_tblOrgLook_master.Analyst = Forms!YourForm!Analyst AND dbo_tblOrgLook_master.Org = Forms!YourForm!Org AND dbo_tblOrgLook_master.CostCenter = Forms!YourForm!CostCenter AND dbo_tblOrgLook_master.Fund = Forms!YourForm!Fund  AND dbo_tblOrgLook_master.PEC = Forms!YourForm!PEC

If you use embedded SQL, always build it into a variable as you are doing.  Then, if it isn't running correctly, you can put a stop in the code and print the contents of the variable to the immediate window.  Usually simply seeing the string is usually enough.  If you can't "see" the issue, copy the string from the immediate window and paste it into the QBE and run it from there.  In almost all cases, you will get different and usually better error messages.

PS, each embedded SQL string must be compiled and an execution plan generated every time the string runs whereas, querydefs are compiled the first time they run and the execution plan is saved for later use.  So, it is more efficient to use querydefs to avoid the overhead of constantly recreating an execution plan.  Of course if you only run the query once per day, the hit is miniscule but if you run the query multiple times, you are simply adding unnecessary overhead.
0
 

Author Closing Comment

by:Jass Saini
ID: 40508057
Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question