"Too Few Parameters" Error Message

[Sherwood]

Greetings,
I am receiving the following error message when attempting to use the
code below:

Error Message - "Too few parameters. Expected 1."

My Code is as follows:

strSQLBorrowerLastName = "((([Loan].[BorrowerLastName]) Like ""*"" &
[Forms]![frmSearch]![txtBorrowerLastName] & ""*"" OR
([Forms]![frmSearch]![txtBorrowerLastName]) Is Null))"

strSQL = strSQLBorrowerLastName

strSQLTotal = "Select [Loan].[BorrowerLastName],[Loan].[BorrowerFirstName],
[Loan].[DepositDate], [Loan].[Branchcode], [Loan].[PropertyState],
[Loan].[LoanRepresentative], [Loan].[LoanNumber] FROM [Loan] WHERE " & strSQL

Set qdf = db.CreateQueryDef("qryTotalRecordsFound", strSQLTotal)

With qdf
'// The line below is where the error occurs.
Set rstTemp = .OpenRecordset(dbOpenSnapshot)
rstTemp.MoveFirst
nbr_of_records = rstTemp.RecordCount
rstTemp.Close
Set rstTemp = Nothing
End With

.....

Similar code seemed to work for me under Access 97, but under Access 2000
this fails. [Note: I tried using the "Eval" function, but was unable to get
the syntax correct for the "OR" portion of the SQL. If I don't use the OR
clause, the error message doesn't appear. However, I need to have the OR
clause so that the proper records are returned if a user leaves a field
null]. Any suggestions?

Thanks in advance!

Sherwood

 

[PC Datasheet]

Try this -----

strSQLBorrowerLastName = "[Loan].[BorrowerLastName] Like '*'" &
[Forms]![frmSearch]![txtBorrowerLastName] & "'*' OR " &
[Forms]![frmSearch]![txtBorrowerLastName] & "Is Null"

 

[Randy Balbuena]

Greetings,
I am receiving the following error message when attempting to use the
code below:

Error Message - "Too few parameters. Expected 1."

My Code is as follows:

strSQLBorrowerLastName = "((([Loan].[BorrowerLastName]) Like ""*"" &
[Forms]![frmSearch]![txtBorrowerLastName] & ""*"" OR
([Forms]![frmSearch]![txtBorrowerLastName]) Is Null))"

strSQL = strSQLBorrowerLastName

strSQLTotal = "Select
[Loan].[BorrowerLastName],[Loan].[BorrowerFirstName],
[Loan].[DepositDate], [Loan].[Branchcode], [Loan].[PropertyState],
[Loan].[LoanRepresentative], [Loan].[LoanNumber] FROM [Loan] WHERE " &
strSQL

Set qdf = db.CreateQueryDef("qryTotalRecordsFound", strSQLTotal)

With qdf
'// The line below is where the error occurs.
Set rstTemp = .OpenRecordset(dbOpenSnapshot)
rstTemp.MoveFirst
nbr_of_records = rstTemp.RecordCount
rstTemp.Close
Set rstTemp = Nothing
End With

....

Similar code seemed to work for me under Access 97, but under Access 2000
this fails. [Note: I tried using the "Eval" function, but was unable to
get
the syntax correct for the "OR" portion of the SQL. If I don't use the OR
clause, the error message doesn't appear. However, I need to have the OR
clause so that the proper records are returned if a user leaves a field
null]. Any suggestions?

Thanks in advance!

Sherwood

Sherwood, please verify every field used on your query code (letter by
letter, space by space) against the original fields on the Loan table. Also
have your string (strSQLBorrowerLastName) formatted as followes:

"( ((TableField1) Like ""*" & FormField1 & "*"") OR ((TableField1) Is
Null) )"

If you are just trying to find out if there is records for the borrower's
Lastname, you DON'T need to create a physical saved query. Create a subform
for the frmSearch form and change its RowSource property with the
strSQLTotal:

Me.SubForm.Form.RecordSource = strSQLTotal

 

[Sherwood]

Thanks for the syntax below. However, when I run it, I receive the following
error:

Run-time error '3075':

Syntax error (missing operator) in query expression
'[Loan].[BorrowerLastName] Like '*'Lugaro'*' OR Lugaro is Null'.

Any suggestions?

Thanks.

Sherwood

Try this -----

strSQLBorrowerLastName = "[Loan].[BorrowerLastName] Like '*'" &
[Forms]![frmSearch]![txtBorrowerLastName] & "'*' OR " &
[Forms]![frmSearch]![txtBorrowerLastName] & "Is Null"


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Greetings,
I am receiving the following error message when attempting to use the
code below:

Error Message - "Too few parameters. Expected 1."

My Code is as follows:

strSQLBorrowerLastName = "((([Loan].[BorrowerLastName]) Like ""*"" &
[Forms]![frmSearch]![txtBorrowerLastName] & ""*"" OR
([Forms]![frmSearch]![txtBorrowerLastName]) Is Null))"

strSQL = strSQLBorrowerLastName

strSQLTotal = "Select [Loan].[BorrowerLastName],[Loan].[BorrowerFirstName],
[Loan].[DepositDate], [Loan].[Branchcode], [Loan].[PropertyState],
[Loan].[LoanRepresentative], [Loan].[LoanNumber] FROM [Loan] WHERE " & strSQL

Set qdf = db.CreateQueryDef("qryTotalRecordsFound", strSQLTotal)

With qdf
'// The line below is where the error occurs.
Set rstTemp = .OpenRecordset(dbOpenSnapshot)
rstTemp.MoveFirst
nbr_of_records = rstTemp.RecordCount
rstTemp.Close
Set rstTemp = Nothing
End With

....

Similar code seemed to work for me under Access 97, but under Access 2000
this fails. [Note: I tried using the "Eval" function, but was unable to get
the syntax correct for the "OR" portion of the SQL. If I don't use the OR
clause, the error message doesn't appear. However, I need to have the OR
clause so that the proper records are returned if a user leaves a field
null]. Any suggestions?

Thanks in advance!

Sherwood

 

[Douglas J. Steele]

Your quotes are wrong (and were wrong in what PC Datasheet posted)

strSQLBorrowerLastName = "[Loan].[BorrowerLastName] Like '*" &
[Forms]![frmSearch]![txtBorrowerLastName] & "*' OR " &
[Forms]![frmSearch]![txtBorrowerLastName] & " Is Null"

Note that I removed a single quote after the first asterisk and before the
second asterisk.

If there's a chance that the last name is going to include an apostrophe
(O'Brien), you'll need to double it up:

strSQLBorrowerLastName = "[Loan].[BorrowerLastName] Like '*" &
Replace([Forms]![frmSearch]![txtBorrowerLastName], "'", "''") & "*' OR " &
[Forms]![frmSearch]![txtBorrowerLastName] & " Is Null"

Alternatively, you could use " as the delimiter:

strSQLBorrowerLastName = "[Loan].[BorrowerLastName] Like " & Chr$(34) & "*"
&
[Forms]![frmSearch]![txtBorrowerLastName] & "*" & Chr$(34) & " OR " &
[Forms]![frmSearch]![txtBorrowerLastName] & " Is Null"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the syntax below. However, when I run it, I receive the following
error:

Run-time error '3075':

Syntax error (missing operator) in query expression
'[Loan].[BorrowerLastName] Like '*'Lugaro'*' OR Lugaro is Null'.

Any suggestions?

Thanks.

Sherwood

Try this -----

strSQLBorrowerLastName = "[Loan].[BorrowerLastName] Like '*'" &
[Forms]![frmSearch]![txtBorrowerLastName] & "'*' OR " &
[Forms]![frmSearch]![txtBorrowerLastName] & "Is Null"


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Greetings,
I am receiving the following error message when attempting to use the
code below:

Error Message - "Too few parameters. Expected 1."

My Code is as follows:

strSQLBorrowerLastName = "((([Loan].[BorrowerLastName]) Like ""*"" &
[Forms]![frmSearch]![txtBorrowerLastName] & ""*"" OR
([Forms]![frmSearch]![txtBorrowerLastName]) Is Null))"

strSQL = strSQLBorrowerLastName

strSQLTotal = "Select [Loan].[BorrowerLastName],[Loan].[BorrowerFirstName],
[Loan].[DepositDate], [Loan].[Branchcode], [Loan].[PropertyState],
[Loan].[LoanRepresentative], [Loan].[LoanNumber] FROM [Loan] WHERE " & strSQL

Set qdf = db.CreateQueryDef("qryTotalRecordsFound", strSQLTotal)

With qdf
'// The line below is where the error occurs.
Set rstTemp = .OpenRecordset(dbOpenSnapshot)
rstTemp.MoveFirst
nbr_of_records = rstTemp.RecordCount
rstTemp.Close
Set rstTemp = Nothing
End With

....

Similar code seemed to work for me under Access 97, but under Access 2000
this fails. [Note: I tried using the "Eval" function, but was unable

to
get

the syntax correct for the "OR" portion of the SQL. If I don't use the OR
clause, the error message doesn't appear. However, I need to have the OR
clause so that the proper records are returned if a user leaves a field
null]. Any suggestions?

Thanks in advance!

Sherwood

 

[Rick Brandt]

Thanks for the syntax below. However, when I run it, I receive the following
error:

Run-time error '3075':

Syntax error (missing operator) in query expression
'[Loan].[BorrowerLastName] Like '*'Lugaro'*' OR Lugaro is Null'.

Any suggestions?

There should not be single quotes between the asterisk and Lugaro. It needs to
evaluate to '*Lugaro*'.

 

[Dirk Goldgar]

Your quotes are wrong (and were wrong in what PC Datasheet posted)

strSQLBorrowerLastName = "[Loan].[BorrowerLastName] Like '*" &
[Forms]![frmSearch]![txtBorrowerLastName] & "*' OR " &
[Forms]![frmSearch]![txtBorrowerLastName] & " Is Null"

Note that I removed a single quote after the first asterisk and
before the second asterisk.

If there's a chance that the last name is going to include an
apostrophe (O'Brien), you'll need to double it up:

strSQLBorrowerLastName = "[Loan].[BorrowerLastName] Like '*" &
Replace([Forms]![frmSearch]![txtBorrowerLastName], "'", "''") & "*'
OR " & [Forms]![frmSearch]![txtBorrowerLastName] & " Is Null"

Alternatively, you could use " as the delimiter:

strSQLBorrowerLastName = "[Loan].[BorrowerLastName] Like " & Chr$(34)
& "*" &
[Forms]![frmSearch]![txtBorrowerLastName] & "*" & Chr$(34) & " OR " &
[Forms]![frmSearch]![txtBorrowerLastName] & " Is Null"

I may be overlooking something, but I think there's still a separate
problem, after the quotes are fixed. The way the SQL criterion is being
assembled, what will happen if
[Forms]![frmSearch]![txtBorrowerLastName] is actually Null? I believe
you will get this value for strSQLBorrowerLastName:

[Loan].[BorrowerLastName] Like "**" OR Is Null

Running a SELECT statement with that criterion will raise run-time error
'3075', "Syntax error (missing operator) in query expression".

Instead, I'd recommend testing whether
[Forms]![frmSearch]![txtBorrowerLastName] is Null before building the
criterion, using logic along these lines:

With Forms!frmSearch!txtBorrowerLastName
If IsNull(.Value) Then
' apply no criterion on BorrowerLastName
strSQLBorrowerLastName = vbNullString
Else
' use value entered as loose criterion
strSQLBorrowerLastName = _
"[Loan].[BorrowerLastName] Like " & _
Chr$(34) & "*" & .Value & "*" & Chr$(34)
End If
End With

Then later on in your code, only build the WHERE clause when you
actually have a criterion to apply:

If Len(strSQLBorrowerLastName) > 0 Then
strSQL = " WHERE " & strSQLBorrowerLastName
End If

strSQLTotal = _
"SELECT " & _
"BorrowerLastName, BorrowerFirstName, " & _
"DepositDate, Branchcode, PropertyState, " & _
"LoanRepresentative, LoanNumber " & _
"FROM Loan " & _
strSQL

 

[Douglas J. Steele]

message I may be overlooking something, but I think there's still a separate
problem, after the quotes are fixed. The way the SQL criterion is being
assembled, what will happen if
[Forms]![frmSearch]![txtBorrowerLastName] is actually Null? I believe
you will get this value for strSQLBorrowerLastName:

[Loan].[BorrowerLastName] Like "**" OR Is Null

Running a SELECT statement with that criterion will raise run-time error
'3075', "Syntax error (missing operator) in query expression".

I think you're right, Dirk. I was thinking of the way of naming parameters
in queries, rather than building up the SQL.