Solved

SQL Where Statement

Posted on 2013-06-22
8
394 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
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 85
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 70

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 48

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
 

Author Closing Comment

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

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 70

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

Independent Software Vendors: 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!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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