[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

Too few parameters error

I'm trying to set and open a DAO recordset based on a SQL string.

The SQL string must be filtered to only include results that match a date range entered on a different form.

This is the strSQL (which filters properly):
strSQL = "SELECT DISTINCT tblBuyers.ID, tblBuyers.Email, Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],"""") AS Name, Max(tblApartments.DateUpdated) AS MaxOfDateUpdated" _
         & " FROM tblApartments, tblBuyers INNER JOIN tblRequests ON tblBuyers.ID = tblRequests.BuyerID" _
         & " GROUP BY tblBuyers.ID, tblBuyers.Email, Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],"""")" _
         & " HAVING (((tblBuyers.Email) Is Not Null And (tblBuyers.Email)<>"""") AND ((Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],"""")) Is Not Null And (Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],""""))<>"""")"
strSQL = strSQL & " AND ((Max(tblApartments.DateUpdated)) BETWEEN [Forms]![frmDateRange]![txtFrom] AND [Forms]![frmDateRange]![txtTo]))"

Open in new window


I get the Too few parameters expected two error. All forms and controls are named properly, and return the expected values.

I rem out the "strSQL = strSQL &" line I get a 3075 runtime error.Error message
0
MacroShadow
Asked:
MacroShadow
2 Solutions
 
mbizupCommented:
You need to seperate out form references when writing queries with vba:

strSQL = "SELECT DISTINCT tblBuyers.ID, tblBuyers.Email, Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],"""") AS Name, Max(tblApartments.DateUpdated) AS MaxOfDateUpdated" _
         & " FROM tblApartments, tblBuyers INNER JOIN tblRequests ON tblBuyers.ID = tblRequests.BuyerID" _
         & " GROUP BY tblBuyers.ID, tblBuyers.Email, Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],"""")" _
         & " HAVING (((tblBuyers.Email) Is Not Null And (tblBuyers.Email)<>"""") AND ((Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],"""")) Is Not Null And (Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],""""))<>"""")"
strSQL = strSQL & " AND ((Max(tblApartments.DateUpdated)) BETWEEN #" & [Forms]![frmDateRange]![txtFrom] & "# AND #" &  [Forms]![frmDateRange]![txtTo] & "#))"

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>strSQL = strSQL & " AND ((Max(tblApartments.DateUpdated)) BETWEEN [Forms]![frmDateRange]![txtFrom] AND [Forms]![frmDateRange]![txtTo]))"
The date references are within the double-quotes, so you are passing it LITERALLY.
Way to go eh.

To eyeball this before it actually executes, add the below line right below the code you posted, step-through your code, and right after it executes copy-paste what appears in the Immediate window (aka Ctrl-G), paste it in an open query, and see if it executes:

Debug.Print strSQL

I believe you want this...

strSQL = strSQL & " AND ((Max(tblApartments.DateUpdated)) BETWEEN #" & [Forms]![frmDateRange]![txtFrom] & " # AND #" & [Forms]![frmDateRange]![txtTo] & "#))"
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now