Solved

SQL Where Statement

Posted on 2013-06-22
8
390 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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.

867 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

24 Experts available now in Live!

Get 1:1 Help Now