Too few parameters Error 3061 in SQL

[lizray]

I have a project for a local Church and am constructing a recordset from an SQL statemant, but when I add the Where and Order by clauses, I get the message "Too few parameters, Expected 3" can anybody please help??. Here is the SQL:
strSQL = "SELECT ChurchAndPriest.Church, ChurchAndPriest.PlaceOfBaptism, Baptisms.BookNum, Baptisms.EntryNum, Baptisms.FamilyName, Baptisms.GivenNames,"
strSQL = strSQL & " Baptisms.DateOfBirth, Baptisms.DateOfBaptism, Baptisms.Notes, Baptisms.BookNum*4000+Baptisms.EntryNum AS BapNumber FROM ChurchAndPriest, Baptisms"
strSQL = strSQL & " WHERE BapNumber Between [FirstEntry] And [LastEntry]"
strSQL = strSQL & " ORDER BY BapNumber;"
FirstEntry and LastEntry are Textboxes.

 

[JBinQLD]

G'day fella,

This line is wrong:
strSQL = strSQL & " WHERE BapNumber Between [FirstEntry] And [LastEntry]"

Should be
strSQL = strSQL & " WHERE BapNumber Between #" & [FirstEntry] & "# And #" & [LastEntry] & "#"

OR

strSQL = strSQL & " WHERE BapNumber Between " & [FirstEntry] & " And " & [LastEntry]

Depending on data type - use # if dates

That will take care of two missing params

To isolate problems like these make the query and then add the parameters manually until you identify the drama.

Sorry for being so vague, it's my bed time now, good luck

 

[lizray]

Thankyou JBinQLD. That solves 2 of the errors, the only problem now seems to be in using BapNumber in either the Where or Order by statements....Any Ideas

 

[dhookom]

It looks like you are also missing a join in your FROM clause. I'm not sure if this is intentional but it could cause issues with duplicate records.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Is BapNumber really a field of ChurchAndPriest or Baptisms?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lizray]

BapNumber is a new field generated from 2 fields from the "Baptisms" table using the AS statement

 

[dhookom]

Try:

WHERE Baptisms.BookNum*4000+Baptisms.EntryNum Between [FirstEntry] And [LastEntry]

Duane
Hook'D on Access
MS Access MVP

 

[lizray]

yes that works (with the & " around firstentry etc), but is it not possible to use the generated "BapNumber" field in the where and order by clauses ?

 

[dhookom]

Jet is a little more picky with derived aliases than some other SQL dialects. Aren't you okay with using the expression?

Duane
Hook'D on Access
MS Access MVP

 

[lizray]

Yes, thank you it is now all working. I was just trying to add to my understanding of SQL

 

[Golom]

There are a few places in Access SQL where you can use a field alias in other clauses but, unfortunately, this isn't one of them.

Access evaluates SQL statements in approximately this order
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
- SELECT

When evaluating the WHERE clause, the field alias defined in the SELECT is not yet known so you must reuse the calculation of the field rather than its name.

 

[lizray]

Thank you Golom. I now understand the situation.