Select statement in VBA

G

Guest

I have the following Select statament in VBA which is returning a Run-Time
error 3061. The final intent is to populate a continuous form with the
records set however, for now I just need help getting the Select statement to
execute.

The Sub stops on the line -- Set rst = db.OpenRecordset(strSQL)

The value in stSearch looks like 28-1-2-23

Thanks

Private Sub Territory_Exit(Cancel As Integer)
Dim stSearch As String
Dim strSQL As String
Dim intSearchlen as Long
Dim db As Database
stSearch = Me.Region.Value & "-" & Me.Area.Value & "-" & Me.District.Value _
& "-" & Me.Territory.Value
intSearchLen = Len(stSearch)
strSQL = "SELECT FY2006_Staging_Adjustments.[Adjustment Reference Number],
FY2006_Staging_Adjustments.[Adjustment Measure Amount]" _
& " FROM FY2006_Staging_Adjustments" _
& " WHERE (Right((FY2006_Staging_Adjustments.Gaining),intSearchLen) = " &
stSearch & ");"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
MsgBox ("Reference #: " & rst.Fields.[Adjustment Reference Number])
rst.MoveNext
Loop
End If
..Close
End With
Set rst = Nothing
Set db = Nothing
End Sub
 
J

Jeff L

In your where clause, stSearch is a String value and thus needs to have
quotes around it. By the way, I'm not as good as other people and
don't know errors by their error number. When you post, it is always
good to include the error message for mere mortals like myself. :)
 
G

Guest

Jeff,

Do you mean just adding quotes around stSearch as shown below? Wii that not
compare the left side of the where clasue to teh string stSearch rather than
the value of the variable stSearch?

Thanks

strSQL = "SELECT FY2006_Staging_Adjustments.[Adjustment Reference Number],
FY2006_Staging_Adjustments.[Adjustment Measure Amount]" _
& " FROM FY2006_Staging_Adjustments" _
& " WHERE (Right(FY2006_Staging_Adjustments.Gaining,intSearchLen) = " &
"stSearch" & ");"

Jeff L said:
In your where clause, stSearch is a String value and thus needs to have
quotes around it. By the way, I'm not as good as other people and
don't know errors by their error number. When you post, it is always
good to include the error message for mere mortals like myself. :)

I have the following Select statament in VBA which is returning a Run-Time
error 3061. The final intent is to populate a continuous form with the
records set however, for now I just need help getting the Select statement to
execute.

The Sub stops on the line -- Set rst = db.OpenRecordset(strSQL)

The value in stSearch looks like 28-1-2-23

Thanks

Private Sub Territory_Exit(Cancel As Integer)
Dim stSearch As String
Dim strSQL As String
Dim intSearchlen as Long
Dim db As Database
stSearch = Me.Region.Value & "-" & Me.Area.Value & "-" & Me.District.Value _
& "-" & Me.Territory.Value
intSearchLen = Len(stSearch)
strSQL = "SELECT FY2006_Staging_Adjustments.[Adjustment Reference Number],
FY2006_Staging_Adjustments.[Adjustment Measure Amount]" _
& " FROM FY2006_Staging_Adjustments" _
& " WHERE (Right((FY2006_Staging_Adjustments.Gaining),intSearchLen) = " &
stSearch & ");"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
MsgBox ("Reference #: " & rst.Fields.[Adjustment Reference Number])
rst.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing
End Sub
Click to expand...
Click to expand...
 
E

Edward Reid

Rafi said:
Do you mean just adding quotes around stSearch as shown below?
Click to expand...

No, what Jeff means is that in string you store in the strSQL variable
has to have quotes around the 28-1-2-23 -- "28-1-2-23" rather than just
28-1-2-23. Put a

MsgBox strSQL

after the assignment to strSQL and it should become a lot clearer to
you. The relevant lines of code need to be

&
" WHERE (Right((FY2006_Staging_Adjustments.Gaining),intSearchLen) = ' "
& stSearch & "');"

You'll have to read that carefully, as it differs from your orignal
only by the addition of two single quotes.

Edward
 
G

Guest

Edward,

I have modified my code as you suggested however I am still experiencing the
same run-time error 3061 (Too few parameters, expected 1.)

My new where clause looks like

& " WHERE (Right((FY2006_Staging_Adjustments.Gaining),intSearchLen) = '" &
stSearch & "');"

Thanks
 
E

Edward Reid

Rafi,

Did you put in the MsgBox display that I recommended? I think you'll
see the error pretty quickly if you do.

Also, I went to Google and did a search on

3061 site:microsoft.com

and the first hit gave some really good clues -- in fact, it was after
glancing through that article that I went back and saw the other error
in your SQL.

I could tell you, but you'll learn a lot more by going through these
steps. Post again if you do those two things and still can't figure
out.

Edward
 
G

Guest

Edward,

I did put the MsgBox in my code and after fooloing around with it I came up
with the following syntax resulting in the variable stSearch showing as a
literal string: "28-1-2-23". The new code looks like

strSQL = "SELECT FY2006_Staging_Adjustments.[Adjustment Reference Number],
FY2006_Staging_Adjustments.[Adjustment Measure Amount]" _
& " FROM FY2006_Staging_Adjustments" _
& " WHERE (Right((FY2006_Staging_Adjustments.Gaining),intSearchLen) = """ &
stSearch & """);"

I have also looked at the google atricle however I was not able to recognize
the second error in my code.

Thanks
 
J

Jeff L

Here is what I meant:
WHERE (Right((FY2006_Staging_Adjustments.Gaining),intSearchLen) =
[SingleQuote][DoubleQuote] & stSearch & [DoubleQuote][SingleQuote]);"

Resulting in:

strSQL = "SELECT FY2006_Staging_Adjustments.[Adjustment Reference
Number],
FY2006_Staging_Adjustments.[Adjustment Measure Amount]" _
& " FROM FY2006_Staging_Adjustments" _
& " WHERE (Right((FY2006_Staging_Adjustments.Gaining),intSearchLen) =
'" &
stSearch & "');"

I would try using the MsgBox for debugging purposes like the other
person suggested too.
 
E

Edward Reid

intSearchLen is known in the procedure, not to the SQL processor. Yet
if you look at the MsgBox output, you'll see that the generated SQL
contains the word intSearchLen.

Edward
 
G

Guest

Edward and Jeff,

Thanks for all the help. After some tweaking I am almost where I need to
be. Here is a sample of my code. Any ideas on how to use the result of the
select as the recordsource for a form?

Public Sub Territory_Exit(Cancel As Integer)
Dim stSearch As String
Dim strSQL As String
Dim Counter As Long
Dim Q As Long
Dim db As Database
stSearch = Me.Region.Value & "-" & Me.Area.Value & "-" & Me.District.Value &
"-" & Me.Territory.Value
Q = Me.Quarter.Value
intSearchLen = Len(stSearch)
MsgBox (stSearch & " Length: " & intSearchLen)
strSQL = "SELECT Distinct Qry_FY06Adj.Quarter, Qry_FY06Adj.Ref,
Qry_FY06Adj.Measure_Amt" _
& " FROM Qry_FY06Adj" _
& " WHERE Qry_FY06Adj.Quarter =" & Q & " and " _
& "(Right((Qry_FY06Adj.Gaining)," & Len(stSearch) & ") = """ & stSearch &
"""" _
& " Or Right((Qry_FY06Adj.losing)," & Len(stSearch) & ") = """ & stSearch &
""");"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
If Not (.EOF And .BOF) Then
Counter = 1
Do Until .EOF
MsgBox ("Record = " & Counter & " /" & rst.Fields("Quarter") & " / " &
rst.Fields("Ref") & " / " & rst.Fields("Measure_Amt"))
Counter = Counter + 1
rst.MoveNext
Loop
End If
..Close
End With
Set rst = Nothing
Set db = Nothing
Counter = 1
End Sub
 
J

Jeff L

I believe the syntax is Forms!FormName.RecordSource = strSQL. Your
form must be open for that to work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Currenct issue with a text box 1
Changing font in rich text table 4
Requery not working 8
Email a group based on records in a form 11
If I DON'T put this lass MSGBOX in the code, it moves off my found record!! 1
Deleting Records 4
dialog box 1
Excel Export Filtered Form Data To Excel 0

Top