Using a Form to Open Custom Report

LQ

Registered User.
Local time
Today, 20:54
Joined
Apr 5, 2001
Messages
145
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).]
 
This is a good time for you to get familiar with the debugger, if you haven't used it already. Put a breakpoint in your code, before you do the openreport, and look at the values of mySQL and your form fields

I'd also change your code from:
If Nz(Forms!frmnewrpt!txtLastName) <> "" Then
to something like:
If len(Me.txtLastName) > 0 Then

Checking the length of a text field is a pretty reliable method of seeing whether the user has entered anything.

Finally, why do you keep resetting Addand? Set it up as a CONST or just hardcode the "AND". This just clutters up your code.
 
Thanks for the suggestion, Chris! I took your advice and used a breakpoint and the debugger. That allowed me to finally figure out that I had left off a space at the end of

mySql = mySql & "[Date of action] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2 "

And can I set Addand as a constant or hard code it if it is not always the same? In the first instance, it is blank, and then from then on it is "AND ". I am a self-taught newbie at this, so I may not have understood your point correctly.

Anyway, thanks for helping me solve a problem that was REALLY aggravating me!

LQ
 
I guess I usually use a little different approach, maybe no better.

I set up a little field, called "strFirstWhere", and initially set to "Y". My basic SQL would not have the "WHERE" in it, because maybe the user wouldn't specify any criteria at all. Then my logic would be roughly like this (and believe me, this ain't VBA
wink.gif
)

mySQL = "SELECT * FROM SOMETABLE"
' and I would repeat the following stuff
' for each criteria field
If somecriteriaisentered then
If strFirstWhere = "Y" then
strFirstWhere = "N"
mySQL = mySQL & " WHERE " & whateverelse
Else
mySQL = mySQL & “AND “ & whateverelse
End If
End If


[This message has been edited by Chris RR (edited 10-11-2001).]
 

Users who are viewing this thread

Back
Top Bottom