Solved

SQL Statement

Posted on 2014-12-18
9
117 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 47

Expert Comment

by:Dale Fye (Access MVP)
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 50

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 50

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 36

Accepted Solution

by:
PatHartman earned 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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