Export PDF

[June7]

Remove that last quote mark. It is not in my example.

[ingarchsf]

ok, sorry!

Now.. it works only if I write all the three parameters.. but.. if I use only one parameter, it tells "missing operator"..
is there any way to solve? like.. the "Like... & "*"" that we put previously in the query?

[June7]

I don't use dynamic parameter queries. And the LIKE with wildcard only works with text fields, not number type fields.

I would use VBA code to conditionally build the filter string. Review: http://www.allenbrowne.com/ser-62code.html

[ingarchsf]

I don't use dynamic parameter queries. And the LIKE with wildcard only works with text fields, not number type fields.

I would use VBA code to conditionally build the filter string. Review: http://www.allenbrowne.com/ser-62code.html

uhm.. but previously inside the query worked also for numeric field!!

btw.. I've understood through that link that I should use an "if".. can I use the "if.. then" inside WHERE? I remember that in Pascal syntax it was possible..

[June7]

If used with number field, LIKE 1* should return 1, 10, 11, 100, etc. If your data never exceeds the number 9, then the LIKE and wildcard is fine.

I think IIf() in criteria of query object is possible but tricky. I don't use dynamic parameters in queries at all.

[ingarchsf]

uhm... ooooooooooooook..
so.. any way to let the CODE know that "if you don't see any value in that field, don't worry.. you could use the other two!!!" :P

[June7]

Post 63 references an Allen Browne article with example VBA code to accomplish that.

[ingarchsf]

Post 63 references an Allen Browne article with example VBA code to accomplish that.

June7.. I don't know how to use it!! I could read it as much as I want, but I have no idea of how to use it!..

[June7]

Did you read the Search Criteria article that Allen References at the top of his page? It has a sample database you can download to learn from.

[ingarchsf]

Did you read the Search Criteria article that Allen References at the top of his page? It has a sample database you can download to learn from.

yes, I've read it.. but.. I don't know where to put any of those.. how to edit any of those.. I have 3 fields..

[June7]

Allen's example builds criteria to set form Filter property. The code is in a button Click event. Did you explore the sample database?

Adapt code to your requirements. What is it you do not understand about programming with VBA and adapting example code? Haven't you been able to adapt other example code, such as from database provided by ssanfu? Why can't you do that with Allen's example? Use whatever parts you need. You need 3 parameters then you need 3 If Then structures. Instead of setting form Filter property, use the constructed criteria string in the WHERE clause of the SQL statement for the recordset.

Use whichever of Allen's If Then examples are appropriate and fill in the blanks:

Private Sub ExportPDF_Click()
Dim ...

If ... Then
...
End If

If ... Then
...
End If

If ... Then
...
End If

    'See if the string has more than 5 characters (a trailing " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen > 0 Then
        'Yep: there is something there, so remove the " AND " at the end.
        strWhere = "WHERE " & Left$(strWhere, lngLen)
    End If
    strSQL = "Select Selezione_corso_2014.[Cognome], Selezione_corso_2014.[Data_modulo] From Selezione_corso_2014 " & strWhere & ";"
    Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)  
    With MyRS
        ...
    End With
...
End Sub

[ingarchsf]

uhm.. problem: impossible to find the method (syntax error) on Me.qncorso1 (number) inside the second If.. [and the same on Me.qnmodulo1 (number) inside the third If..

Private Sub ExportPDF_Click()
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim strSQL As String
    Dim strRptName As String
    Dim sCriteria As String
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.

    Set MyDB = CurrentDb
    strRptName = "Attestati_2014"
    
    If Not IsNull(Me.qcognome1) Then
        strWhere = strWhere & "([Cognome] Like ""*" & Me.qcognome1 & "*"") AND "
    End If
    
    If Not IsNull(Me.qncorsol) Then
        strWhere = strWhere & "([N_corso] = " & Me.qncorsol & ") AND "
    End If
    
    If Not IsNull(Me.qnmodulol) Then
        strWhere = strWhere & "([N_modulo] = " & Me.qnmodulol & ") AND "
    End If
    
    'See if the string has more than 5 characters (a trailing " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen > 0 Then
        'Yep: there is something there, so remove the " AND " at the end.
        strWhere = "WHERE " & Left$(strWhere, lngLen)
    End If
    
    'OLD SQL
    '    strSQL = "Select Selezione_corso_2014.[Cognome], Selezione_corso_2014.[Data_modulo] From Selezione_corso_2014;"
    ' this query selects all records with the surname entered in Me.txtCognome   (Form1.txtCognome)
    ' You would need to modify this to maybe include dates if you didn't want to re export previously
    ' exported records.
    strSQL = "Select Selezione_corso_2014.[Cognome], Selezione_corso_2014.[Data_modulo] From Selezione_corso_2014 " & strWhere & ";"
    ' strSQL = strSQL & " WHERE Cognome ='" & Me.qcognome1 & "' AND N_corso =" & Me.qncorso1 & " "
    Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
'    Application.Echo False
    
    With MyRS
        Do While Not MyRS.EOF
            'create the criteria string              '   "Cognome" is a text field, "Data_modulo" is a date field.
            sCriteria = "[Cognome]='" & ![Cognome] & "' and [Data_modulo] = #" & ![Data_modulo] & "#"
            DoCmd.OpenReport strRptName, acViewPreview, , sCriteria
            
            'now output the report
            DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\Attestati\" & ![Cognome] & "_" & Format(![Data_modulo], "YYYYMMDD") & ".pdf", False
            DoCmd.Close acReport, strRptName
            .MoveNext
        Loop
    End With
'    Application.Echo True
    MyRS.Close
    Set MyRS = Nothing
    MsgBox "Done"
End Sub

[June7]

N_corso and N_modulo are number type fields? I don't see anything wrong with the code syntax. But I thought modulo was a date/time type?

[ingarchsf]

no, only number fields, no date fields!

[June7]

Then why is Data_modulo formatted as a date in the report criteria?

Still no idea why the code errors. If you want to provide latest version of db ...