Remove that last quote mark. It is not in my example.
Remove that last quote mark. It is not in my example.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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?
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
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
uhm.. but previously inside the query worked also for numeric field!!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
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..
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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
Post 63 references an Allen Browne article with example VBA code to accomplish that.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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:
Code: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
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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..
Code: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
Last edited by ingarchsf; 09-01-2014 at 07:40 AM.
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?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
no, only number fields, no date fields!
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 ...
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.