Solved

SQL Statement

Posted on 2014-12-18
9
118 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 37

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

737 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