SQL and Code

[BF]

After asking a few questions on this forum about different procedures it has become evident that one must have some knowledge about inserting SQL into code. Although I understand the concept, I'm having difficulty figuring out where to put all those d@mn quotes. Can someone point out a good reference or give a quick tutorial on this process and also help me with the code below? For example:<br>
<br>
Private Sub Form_Current()<br>
Dim db As Database, rst As Recordset, SQL As String<br>
Set db = CurrentDb<br>
<br>
SQL = &quot;SELECT [OfficerID], [LName], [FName], [MI], [Rank], [Unit]&quot;<br>
SQL = SQL & &quot;FROM OfficerQ WHERE [RaterNum] Or [IntNum] Or [SeniorNum] = &quot; & Me!RankBoxNum<br>
Set rst = db.OpenRecordset(SQL)<br>
<br>
Me![RaterSubform].Form.RecordSource = SQL<br>
<br>
rst.Close<br>
db.Close<br>
<br>
end sub<br>
<br>
This is attached to a main form current event. My desire is for the results to go into a subform called &quot;RaterSubForm&quot;. The main form is called OfficerF. When I run it I'm getting an error on the Me![RaterSubForm].Form.RecordSource line. However, I feel I may also have some syntax errors in the SQL string. The SQL statement will run against a query called raterQ that is built from two tables.<br>
<br>
Please don't tell me to go to the Access help file. Been there! Done that!<br>
<br>
Thanks<br>
BF <p>BF<br><a href=mailto:g43@tc3net.com>g43@tc3net.com</a><br><a href= > </a><br>

 

[DougP]

You do<br>
First of all the Error will tell you what is wrong look at it very closely.<br>
I press Alt-PrtScrn and paste it in Paint Brush then print it out.<br>
<br>
Also put a stop on the Forms! (Press F9 on the line) line <br>
then Press ctrl-G (pop up Debug window)<br>
In the bottom of debug, type in ?SQL (thats means Print SQL)and press the enter key. It will print out what is in the SQL statement<br>
you can examine it, and the Error message you recieve, and figure out what is wrong.<br>
Or paste it here an we will tell you.<br>
I just put it in my sample db<br>
and I see that there is no space after [Unit] before the From<br>
[Unit]FROM OfficerQ WHERE <br>
this may or maynot be the problem<br>
I need the Exact Error to be sure.<br>
<br>
-----------------------------------------<br>
Yes if you can write SQL in your sleep you will be an Access Jedi, Luke<br>
<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JimHorton]

BF,<br>
The first error I see is a missing space. When you concat, always use a leading space on the next line (Notice the space before the FROM):<br>
SQL = &quot;SELECT [OfficerID], [LName], [FName], [MI], [Rank], [Unit]&quot;<br>
SQL = SQL & &quot; FROM OfficerQ WHERE [RaterNum] Or [IntNum] Or [SeniorNum] = &quot; & Me!RankBoxNum<br>
Set rst = db.OpenRecordset(SQL)<br>
<br>
Secondly, I'm not sure if it's really a problem, but I strongly recommend not using the keyword SQL as a variable--same rules apply for any keywords. It's just a good habit to avoid that.<br>
<br>
Third, in an OR clause, you need to complete each individual clause:<br>
&quot; WHERE [RaterNum] = &quot; & Me!RankBoxNum & &quot; Or [IntNum] =&quot; & Me!RankBoxNum & &quot; Or [SeniorNum] = &quot; & Me!RankBoxNum<br>
<br>
Try that, let me know if it helps...<br>
--Jim

 

[elizabeth]

1) SQL statements end in semicolons.<br>
<br>
2) If your confusion is about the quotes, maybe the problem isn't just building SQL statements, but building lengthy string expressions in the code window.<br>
<br>
The 2 ways to do this (I never mix, way too confusing) are <br>
a) build individual lines, and then keep doing <br>
strSQL = strSQL & &quot; ...&quot;<br>
on each line. <br>
<br>
b) use the line continuation character (underscore with a space before it). The underscore continues a line, but it can't be used *within* a pair of opening & closing quotes. In other words, if you use an open quote on a line, you have to put a corresponding closing quote on that same line, before you use the underscore. <br>
<br>
Here's an example I found under Access Help I searched under &quot;SQL&quot;, then selected the topic &quot;Build SQL Statements That Include Variables and Controls&quot;. BTW, there's a semicolon at the end, in here it looks like a comma to me.<br>
<br>
Dim dbs As Database, qdf As QueryDef, strSQL As String<br>
Set dbs = CurrentDb<br>
strSQL = &quot;SELECT * FROM Orders WHERE OrderDate&quot; _<br>
& &quot;&gt; #&quot; & Forms!Orders!OrderDate & &quot;#;&quot;<br>
Set qdf = dbs.CreateQueryDef(&quot;SecondQuarter&quot;, strSQL)<br>
<br>
Here's an example of the same thing using the other method.<br>
<br>
Dim dbs As Database, qdf As QueryDef, strSQL As String<br>
Set dbs = CurrentDb<br>
strSQL = &quot;SELECT * FROM Orders WHERE OrderDate&quot;<br>
strSQL = strSQL & &quot;&gt; #&quot; & Forms!Orders!OrderDate & &quot;#;&quot;<br>
Set qdf = dbs.CreateQueryDef(&quot;SecondQuarter&quot;, strSQL)<br>
<br>
Hope this helps