Too few parameters error with date range

[moishy]

Here you go:

Private Sub cmdEmail_Click()

    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")
        Case "AllUpdates"
            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])<>"""")"
            strSQL = strSQL & "[Temp].[DateUpdated] Between " & [Forms]![frmDateRange]![txtFrom] & " And " & [Forms]![frmDateRange]![txtTo]
         Case "SelectedUpdates"
            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") 
        Case Else
            strSQL = ""
    End Select


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

    While Not rs.EOF
        rpt.Filter = "[B].[ID] = " & rs!ID

        rpt.FilterOn = True

        ' Set name of file
        strFile = Format(Date, "mm.dd.yyyy") & " - " & rs!Name & ".pdf"

        ' Set path of file
        strPath = GetSpecialFolder(CSIDL_LOCAL_APPDATA) & "\MyTemp\"

        ' Open the report in hidden state so it applies all settings (vba) set on report events
        DoCmd.OpenReport strReport, acViewPreview, , , , acHidden

        ' For debuging purposes
        Debug.Print "Name: " & rs!Name & "                      " & "Email: " & rs!Email

        ' Save to pdf
        '        DoCmd.OpenReport "rptRealGrouped", acViewPreview ', , , , acHidden
        ' The next line will only work in Access 2010 (or 2007 if Microsoft Pdf Addin was installed)
        DoCmd.OutputTo acOutputReport, "rptRealGrouped", acFormatPDF, strPath & strFile

        '        ' Comment the previous line and uncomment the next for backwards compatibility
        '        ' i.e. Acccess 2007 (without microsoft pdf addin)or earlier
        '        'Call PrintReportAsPDFwithBullZip(strReport, , strPath, strFile)
        '        DoCmd.Close acReport, "rptRealGrouped", acSaveNo

        ' Set full path (including name) of saved pdf
        strFullPath = strPath & strFile

        '''        ' Send mail
        '''        Call SendEmail(rs!Email, "", , , , , , , , , , , , , strFullPath)

        'delete the pdf
        Kill strPath & strFile

        rs.MoveNext
    Wend

    'Cleanup
    rs.Close
    Set rs = Nothing

    '        DoCmd.Close acForm, Me.Name

End Sub

Thanks for your invaluable help.

 

[vbaInet]

Good... but where's your attempt on this?

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

And like I keep telling you, you're doing too many things at once. Get individual parts working first, then integrate. Your level of expertise will dictate how much you can do in one go. So on that note, let's get the recordset (above) working and looping correctly then we can worry about what goes in it.

Oh by the way, what's Temp? A TempVar?

 

[moishy]

I thought that that's what I did with the above rs.

Temp is an alias for a query.

 

[vbaInet]

Let's break it down until small parts.

Is your recordset returning the records you expect? Also I would change your While...Wend loop to a Do While...Loop loop.

For the final piece of the puzzle, have a look at this thread (post #5):

http://www.access-programmers.co.uk/forums/showthread.php?t=204478

 

[moishy]

The recordset is returning the contacts as expected. The problem is that it returns each contact more than once.

This is my latest attempt:

Private Sub cmdEmail_Click()

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

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

    Select Case Me.OpenArgs
        Case "AllUpdates"
            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])<>"""")"
        Case "SelectedUpdates"
            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")
        Case Else
            strSQL = ""
    End Select

    ' For debuging purposes
    Debug.Print strSQL

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

    Do Until rs.EOF
        rpt.Filter = "[ID] = " & rs!ID & " AND [DateUpdated]  Between #" & [Forms]![frmDateRange]![txtFrom] & "# And #" &  [Forms]![frmDateRange]![txtTo] & "#"
        rpt.FilterOn = True

        ' For debuging purposes
        Debug.Print "Name: " & rs!Name & "                    " & "Email: " & rs!Email

        rs.MoveNext
    Loop

    'Cleanup
    rs.Close
    Set rs = Nothing

End Sub

 

[vbaInet]

Why are you pulling from a report's record source? That record source is returning duplicate records because of your join or each contact may have more than one email address/phone number.

Pull directly from the Contacts table and you should have no duplicates.

 

[moishy]

You're right about using the report record source (of course!).

The problem is that DateUpdated is in a different table than the rest of the info, and it has more than one value per contact.

 

[vbaInet]

So work on getting that query distinct before attempting to code anything. If it has more than one DateUpdated per contact then I supppose you want the Max of it.

 

[moishy]

That's what I'm trying to do, but I can't figure out how to use Max() with my strSQL.

 

[vbaInet]

Baby steps, first of all do it in a query, get it working then copy across the SQL statement.

 

[moishy]

That's how I started, but when I try this I get an error: You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function.

SELECT DISTINCT tblBuyers.ID, tblBuyers.Email,  Nz([tblBuyers].[FirstName],"") & " " &  Nz([tblBuyers].[Surname],"") AS Name, max(tblApartments.DateUpdated)
FROM tblApartments, tblBuyers INNER JOIN tblRequests ON tblBuyers.ID = tblRequests.BuyerID
WHERE (((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],""))<>"");

 

[vbaInet]

If it says that it means you need to put Group By in the Totals row under the field.

 

[moishy]

Ok, following you're advice I finally managed to get the SQL to return the expected records.

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],""))<>"") AND ((Max(tblApartments.DateUpdated)) Between [Forms]![frmDateRange]![txtFrom] And [Forms]![frmDateRange]![txtTo]));

Now I'm back to square one.
When I add this line after the previous I get the two few parameters error expected two.

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

 

[vbaInet]

Have you checked that both controls are returning values? I mean have you verified it in code using Debug.Print or Msgbox?

 

[moishy]

Yes I checked that the controls are returning the proper values using "?" when the code broke.

If the date filter part is 'inside' or 'outside' the strSQL I get the Too few parameters error, and if the filter is 'outside' of the strSQL and I rem out the datefilter line I get a 3075 runtime error. (the error message is attached).

Here is the full strSQL:

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]))"

 

[vbaInet]

It will be easier if you upload a cut-down version of your db so I can take a look.

 

[moishy]

Never mind I got it, I passed the forms!ect. as a string.

 

[vbaInet]

I was just about to look into your problem and just realised that you had removed the db.

Glad you got it sorting anyway.