I would like to use an unbound form so that users can specify various criteria for a report. Then I would like the users to be able to click on a button and open up the report with their data. The problem is that I can get the report to filter on *some* of the criteria, but not on others. Specifically, I can get the report to filter on date alone, but not in conjunction with any other criteria. Everything else seems to work. I can't figure out what I am doing wrong! I put this code behind a cmd button on the form:
Private Sub cmdrunrpt_Click()
Dim mySql As String
'this creates a dynamic SQL statement that changes with each new criteria
Dim Addand As String
Addand = ""
'This will be added after the first criterion
mySql = "SELECT * FROM [letter log] WHERE "
If Nz(Forms!frmnewrpt!txtdate1) <> "" And Nz(Forms!frmnewrpt!txtdate2) <> "" Then
mySql = mySql & "[Date of action] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2"
Addand = "AND "
End If
If Nz(Forms!frmnewrpt!txtmedrec) <> "" Then
mySql = mySql & Addand & "[Medical Record #] Like '*" & Forms!frmnewrpt!txtmedrec & "*' "
Addand = "AND "
End If
If Nz(Forms!frmnewrpt!cbolettertype) <> "" Then
mySql = mySql & Addand & "[Type of letter] Like '*" & Forms!frmnewrpt!cbolettertype & "*' "
Addand = "AND "
End If
If Nz(Forms!frmnewrpt!txtLastName) <> "" Then
mySql = mySql & Addand & "[Patient Last Name] Like '*" & Forms!frmnewrpt!txtLastName & "*' "
Addand = "AND "
End If
If Nz(Forms!frmnewrpt!txtFirstName) <> "" Then
mySql = mySql & Addand & "[First Name] Like '*" & Forms!frmnewrpt!txtFirstName & "*' "
End If
DoCmd.OpenReport "rptletterdate", acViewPreview, mySql
DoCmd.Close acForm, "frmnewrpt"
End Sub
TIA
LQ
[This message has been edited by LQ (edited 10-10-2001).]
Private Sub cmdrunrpt_Click()
Dim mySql As String
'this creates a dynamic SQL statement that changes with each new criteria
Dim Addand As String
Addand = ""
'This will be added after the first criterion
mySql = "SELECT * FROM [letter log] WHERE "
If Nz(Forms!frmnewrpt!txtdate1) <> "" And Nz(Forms!frmnewrpt!txtdate2) <> "" Then
mySql = mySql & "[Date of action] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2"
Addand = "AND "
End If
If Nz(Forms!frmnewrpt!txtmedrec) <> "" Then
mySql = mySql & Addand & "[Medical Record #] Like '*" & Forms!frmnewrpt!txtmedrec & "*' "
Addand = "AND "
End If
If Nz(Forms!frmnewrpt!cbolettertype) <> "" Then
mySql = mySql & Addand & "[Type of letter] Like '*" & Forms!frmnewrpt!cbolettertype & "*' "
Addand = "AND "
End If
If Nz(Forms!frmnewrpt!txtLastName) <> "" Then
mySql = mySql & Addand & "[Patient Last Name] Like '*" & Forms!frmnewrpt!txtLastName & "*' "
Addand = "AND "
End If
If Nz(Forms!frmnewrpt!txtFirstName) <> "" Then
mySql = mySql & Addand & "[First Name] Like '*" & Forms!frmnewrpt!txtFirstName & "*' "
End If
DoCmd.OpenReport "rptletterdate", acViewPreview, mySql
DoCmd.Close acForm, "frmnewrpt"
End Sub
TIA
LQ
[This message has been edited by LQ (edited 10-10-2001).]