Too few parameters error with date range

[moishy]

I'm trying to set a DAO recordset to the following query:

SELECT DISTINCT Temp.ID, Temp.DateUpdated FROM (SELECT DISTINCTROW B.ID,[P].[DateUpdated] FROM (tblBuyers AS B tblApartments AS P)as Temp WHERE (([Temp].[DateUpdated]) Between ([Forms]![frmDateRange]![txtFrom]) And ([Forms]![frmDateRange]![txtTo]))

But I get the too few parameters Error.

The form is open (and visible), the controls are named properly.

I tried wrapping the form references in the Eval() function:

WHERE (([Temp].[DateUpdated]) Between Eval('[Forms]![frmDateRange]![txtFrom]') And Eval('[Forms]![frmDateRange]![txtTo])')

But it didn't make a difference.

Help sorting this out will be greatly appreciated!

 

[vbaInet]

DAO recordsets don't like when your parameters are referring to form controls. You need to remove the parameters from the query and add it in your OpenRecordset code.

 

[moishy]

How do I add it to the OpenRecordset?

 

[vbaInet]

Show me your code.

 

[moishy]

    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim rpt As Report

    Dim strFile As String
    Dim strPath As String
    Dim strReport As String
    Dim strFullPath As String


    DoCmd.OpenReport "rptRealGrouped", acViewPreview, , , acHidden
    Set rpt = Reports("rptRealGrouped")

    strReport = CStr(rpt.Name)

    Select Case Me.OpenArgs
        Case "Print All"
            strSQL = "SELECT DISTINCT Temp.ID, Temp.Email, Temp.Name " _
                     & "FROM (" & Replace(rpt.RecordSource, ";", "") & ")as Temp" _
                     & " WHERE (((Temp.Email) Is Not Null And ([Temp].[Email])<>"""") AND (([Temp].[Name]) Is Not Null And ([Temp].[Name])<>""""))"
        Case "Print Selected"
            strSQL = "SELECT DISTINCT Temp.ID, Temp.Email, Temp.Name " _
                     & "FROM (" & Replace(rpt.RecordSource, ";", "") & ")as Temp" _
                     & " WHERE (((Temp.Email) Is Not Null And ([Temp].[Email])<>"""") AND (([Temp].[Name]) Is Not Null And ([Temp].[Name])<>"""")) And " _
                     & Replace(gWhere, "B.ID", "Temp.ID")
        [COLOR=Red]Case "AllUpdates"[/COLOR]
             strSQL = "SELECT DISTINCT Temp.ID, Temp.Email, Temp.Name, Temp.DateUpdated " _
                      & "FROM (" & Replace(rpt.RecordSource, ";", "") & ") as Temp" _
                      & " WHERE (([Temp].[Email]) Is Not Null And ([Temp].[Email])<>"""") AND (([Temp].[Name]) Is Not Null And ([Temp].[Name])<>"""")" _
                      & " AND (([Temp].[DateUpdated]) Between Eval([Forms]![frmDateRange]![txtFrom]) And Eval([Forms]![frmDateRange]![txtTo]))"
        [COLOR=Red]Case "SelectedUpdates"[/COLOR]
             strSQL = "SELECT DISTINCT Temp.ID, Temp.Email, Temp.Name, A.DateUpdated " _
                      & "FROM (" & Replace(rpt.RecordSource, ";", "") & ")as Temp, tblApartments as A" _
                      & " (((Temp.Email) Is Not Null And ([Temp].[Email])<>"""") AND (([Temp].[Name]) Is Not Null And ([Temp].[Name])<>"""")) " _
                      & "WHERE " & Replace(gWhere, "B.ID", "Temp.ID") _
                      & "AND ((([Temp].[DateUpdated]) Between [Forms]![frmDateRange]![txtFrom] And [Forms]![frmDateRange]![txtTo]))"
        Case Else
            strSQL = ""
    End Select

    Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

 

[vbaInet]

You're already doing something similar. Add it in the WHERE part.

 

[moishy]

I must be missing something, does OpenRecordset hava a where clause?

 

[vbaInet]

You're building your SQL string in code before passing it to the OpenRecordset method.

 

[moishy]

Of course I am. How else would I open the recordset?

Once the recordset is open how do I filter it?

 

[moishy]

Do you mean using a clone?

 

[moishy]

Will this work:

    Set rsUnfiltered = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
     rsUnfiltered .Filter = "[DateUpdated] >= " &  [Forms]![frmDateRange]![txtFrom] & " AND [DateUpdated] <= " &  [Forms]![frmDateRange]![txtTo]

 

[vbaInet]

You would have to try it to know moishy.

 

[moishy]

What did you have in mind?

 

[vbaInet]

moishy, try what you have and if it doesn't work or it errors let us know. Asking questions like "will this work" is not the way to go.

 

[moishy]

Using the sql in a query filters fine, running it in code doesn't filter, a problem I have in both cases is, it will loop for the number of different DateUpdated values per contact instead of once per contact.

 

[vbaInet]

Let me clarify some points:

1. I mentioned you should move the form references from the query and use it in your recordset code instead. If you look in your SQL string you still have the form reference inside the SQL statement rather than outside of it. E.g. it should be:

strSQL = strSQL & "[SomeField] BETWEEN " & Forms!FormName!Textbox1 & " AND " & Forms!FormName!Textbox2

... can you see that the reference is outside of the SQL statement and returning a value.
2. What are you doing with the Filter part?

 

[moishy]

1. I don't see how the form reference is 'outside' the SQL statement, the reference is concatenated to the strSQL.
When I tried it I got a 3075 runtime error: Syntax error (missing operator) in query expression: WHERE (([Temp].) Is Not Null And ([Temp].[Email])<>"") AND (([Temp].[Name]) Is Not Null And ([Temp].[Name])<>"")[Temp].[DateUpdated] Between 26/08/2012 And 26/08/2012

The reference returned a value before too.

2. I want [B]one [/B]report per contact filtering the records so only records that have DateUpdated [B]between [/B]the dates entered on the form will show up.

 

[vbaInet]

1. It is outside of the SQL statement because it is not referenced directly by the recordset. When compiled the value is returned and concatenated to the SQL statement. Debug.Print your SQL statement to see what was typed wrong.

2. Get 1 working first before forging ahead. You're doing too many things at once.

 

[moishy]

Ok, I got the SQL string right the problem was it was missing a ampersand between "And ([Temp].[Name])<>"")" and "[Temp].[DateUpdated]".

Back to the second issue.
I want one report per contact, filtering the records so only records that have DateUpdated between the dates entered on the form will show up, now it produces a report for each DateUpdated value.

 

[vbaInet]

Can I see your full code.

Create a recordset based on the contact, loop through that recordset and for each ContactID perform whatever action you wish to perform.