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
Solved

SQL Where Statement

Posted on 2013-06-22
8
392 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 69

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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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
 

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 69

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 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