Question on SELECT statement syntax / Error 3061

[Rob Ivy]

Hello. First I must say a huge "thank you" to all the Access MVPs and
other experts on these forums. Your willingness to share your valuable
knowledge and expertise has helped me greatly and is profusely
appreciated. I have often found solutions from problems encountered by
others and answered by you, but this is my first post. I thought it
fitting to preface my question with this word of thanks.

Now - to the story. I have about 7 years experience developing
intermediate Access applications but have only in the last year
ventured into using VBA beyond standard forms coding and simple public
functions. My current dilemma involves what I think to be a syntax
error I must be making in a SELECT statement when trying to open a
recordset in code. Although I have tried a dozen or more variations,
the function continues to return "Run-time Error 3061 / Too few
parameters: Expected 1". I am running Access 2000, and attach my code
below. The recordset is pulled from the table "People" in which the
fields I have designated are actual fields and are spelled correctly (I
have read that misidentifying fields can yield a 3061 error.) I am
very interested in finding out where I have gone wrong (in the SELECT
statement or anywhere else in the function for that matter since I am
new at this) as I have been spinning my wheels on this for hours on
end. Please let me know if you need more data in order to comment.
Many thanks in advance.

Public Function GetDemographic(strFID As Integer) As Integer

Dim strSQL As String
Dim rs As Recordset
Dim strHead As String
Dim strGroup As String
Dim intAge As Integer
Dim dtmDate As Date
strHead = "Head"
dtmDate = Date

Do
strSQL = "SELECT MaritalStatus, DOB FROM People WHERE FamilyPosition =
" & _
strHead & " And FamilyID =" & Trim(Str(strFID))

CurrentDb.OpenRecordset (strSQL)

intAge = DateDiff("yyyy", BOD, dtmDate) + _
(dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
Day(dtmBD)))

Select Case MaritalStatus
Case "Single"
Select Case intAge
Case 0 To 11
strGroup = "Minor Child"
Case 12 To 17
strGroup = "Youth"
Case 18 To 21
strGroup = "College Age"
Case 22 To 34
strGroup = "Single Adults Under 35"
Case 35 To 110
strGroup = "Single Adults 35+"
Case Else
strGroup = Unknown
End Select
Case "Married"
Select Case intAge
Case 0 To 39
strGroup = "Married Under 40"
Case 40 To 59
strGroup = "Married 40-59"
Case 60 To 110
strGroup = "Married 60+"
Case Else
strGroup = Unknown
End Select
Case "Widow(er)"
strGroup = "Widow(er)/Single Parent"
Case "Sigle Parent"
strGroup = "Widow(er)/Single Parent"
Case Else
strGroup = Unknown
End Select

rs.MoveNext
Loop Until rs.EOF

rs.Close
Set rs = Nothing

End Function

 

[Tim Ferguson]

Although I have tried a dozen or more variations,
the function continues to return "Run-time Error 3061 / Too few
parameters: Expected 1". I am running Access 2000, and attach my code
below.

strSQL = "SELECT MaritalStatus, DOB " & _
" FROM People " & _
" WHERE FamilyPosition = " & strHead & _
" AND FamilyID =" & Trim(Str(strFID))

Now, guessing that FamilyPosition is a text value, and FamilyID is a
numeric, the database will see something like:

WHERE FamilyPosition = father AND FamilyID = 1207

so it will assume that father is a field in the recordset, which it
isn't. This is what I think you want it to see:

WHERE FamilyPosition = "father" AND FamilyID = 1207

(the numeric does not need delimiters, but might need formatting in some
circumstances). To get this, you'll need some code like

strSQL = "etc.... " & _
" WHERE FamilyPosition = """ & strHead & """" & _

or even better,

strSQL = "etc.... " & _
" WHERE FamilyPosition = " & SQLQuote(strHead) & _


If the doubled-up quote marks are confusing, try hanging around here for
a couple of days: it comes up regularly.

Best wishes


Tim F

 

[Stefan Hoffmann]

hi Rob,

[..]

Do
strSQL = "SELECT MaritalStatus, DOB FROM People WHERE FamilyPosition =
" & _
strHead & " And FamilyID =" & Trim(Str(strFID))

CurrentDb.OpenRecordset (strSQL)

You don't assign the recordset to the later used recordset variable rs.
And you have to open it outside the loop.

[..]
rs.MoveNext
Loop Until rs.EOF

mfG
--> stefan <--

 

[Rob Ivy]

Stefan and Tim, profuse thanks. It works! I also discovered that I
wasn't using "rs!" in front of the fields I was using from the
recordset.

All the best, -Rob

 

[Rob Ivy]

Stefan and Tim, profuse thanks. It works! I also discovered that I
wasn't using "rs!" in front of the fields I was using from the
recordset.

All the best, -Rob