Solved

SQL Where Statement

Posted on 2013-06-22
8
387 Views
Last Modified: 2013-06-24
Experts,
I am writting a SQL statement and having issues getting the WHERE statement coded correctly.

This is my code...
strSQLWhere = "WHERE (([PS_Main_Data Query]![System] = '" & Trim(strSystem) & "))'"

This is the result of what I have now...
WHERE (([PS_Main_Data Query]![System] = 'Butler))';

This is what i need...
 WHERE (([PS_Main_Data Query]![System] = "Butler"))

Not sure what I have wrong...The (') and (") seem to be my issue.

Thanks,
Bob
0
Comment
Question by:RobertStamm
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 84
ID: 39267927
Try this:

trSQLWhere = "WHERE (([PS_Main_Data Query]![System] = ' " & Trim(strSystem) & "  '   ))'"

I inserted a single quote immediately BEFORE the last two close parentheses. I included some whitespace so you can see what I did, but you can remove that after verifying it works.
0
 

Author Comment

by:RobertStamm
ID: 39267943
Still not the result I need...This is what yourstatement does...
 WHERE (([PS_Main_Data Query]![System] = ' Butler  '   ))';

I need the where to look like htis to work correctly in the rowsource.
WHERE (([PS_Main_Data Query]![System] = "Butler"))

FYI...I am assigning this to the RowSource of a combobox after created,
cboPSName.RowSource = strSQL
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39267967
Don't think double quotes are correct - the single ones enclose a SQL string literal. Just removing the spaces added intentionally and for demonstration purposes should be the solution (after removing the errornous last single quote, of course):
strSQLWhere = "WHERE (([PS_Main_Data Query]![System] = '" & Trim(strSystem) & "'))"

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39268010
no points.  You need to remove the spaces from LSMs code (he added those so you could actually see and distinguish the single quotes, and advised you to remove the spaces).  Should look like:

trSQLWhere = "WHERE (([PS_Main_Data Query]![System] =  " & Trim(strSystem) & "'))'"
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Closing Comment

by:RobertStamm
ID: 39270824
Thanks...That worked!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39270947
I'm a little surprised you didn't award any points to LSM, as he posted the first correct solution (having added spaces only to make it more readable).
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39270955
LSM's answer still had the original closing single quote wrong.
0
 

Author Comment

by:RobertStamm
ID: 39271025
I copied and pasted both.  Qlemo's worked and LSM's did not.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now