• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

SQL Where Statement

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
RobertStamm
Asked:
RobertStamm
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
RobertStammAuthor Commented:
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
 
QlemoC++ DeveloperCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Dale FyeCommented:
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
 
RobertStammAuthor Commented:
Thanks...That worked!
0
 
Dale FyeCommented:
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
 
QlemoC++ DeveloperCommented:
LSM's answer still had the original closing single quote wrong.
0
 
RobertStammAuthor Commented:
I copied and pasted both.  Qlemo's worked and LSM's did not.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now