Print report based on multiple criteria of the same control.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Office XP Pro 2002 and I need some help on a report.

I want to print the report based on one Test# or 2 Test#'s. I can get it to
print the one Test# but when I enter the second Test# it prints all Test#'s.

This is the code I have written:
Dim strSQL As String
Dim strWhere As String

'Build the wherecondition to use in the OpenReport method.
If Not IsNull(FirstTest) Then
If IsNull(SecondTest) Then
strWhere = strWhere & " TestNo = " & "'" & FirstTest & "'"
ElseIf Not IsNull(SecondTest) Then
strWhere = strWhere & " TestNo = " & "'" & FirstTest & "'" & "
OR " & "'" & SecondTest & "'"
End If
End If
I would appreciate any help I can get on this question.

Thanks,
Curt
 
If Not IsNull(FirstTest) Then
If IsNull(SecondTest) Then
strWhere = strWhere & " TestNo = " & "'" & FirstTest & "'"
ElseIf Not IsNull(SecondTest) Then
strWhere = strWhere & " TestNo = '" & FirstTest & "' OR TestNo =
'" & SecondTest & "'"
'or this syntax
strWhere = strWhere & " TestNo IN ('" & FirstTest & "', '" &
SecondTest & "')"
End If
End If
 
Thanks Duane.

This worked.

Curt

Duane Hookom said:
If Not IsNull(FirstTest) Then
If IsNull(SecondTest) Then
strWhere = strWhere & " TestNo = " & "'" & FirstTest & "'"
ElseIf Not IsNull(SecondTest) Then
strWhere = strWhere & " TestNo = '" & FirstTest & "' OR TestNo =
'" & SecondTest & "'"
'or this syntax
strWhere = strWhere & " TestNo IN ('" & FirstTest & "', '" &
SecondTest & "')"
End If
End If
Click to expand...
 
Duane,

I used the IN part of the code and it works for 2 TestNo's but when I tried
to add a ThirdTest I get all the tests. What I am trying to do is to be able
to pull up one test and print or 1,2,3,4,etc TestNo's and print report.

Here is the code I wrote to try and get 3 TestNo's.

If Not IsNull(FirstTest) Then
If IsNull(SecondTest) Then
If IsNull(ThirdTest) Then
strWhere = strWhere & " TestNo = " & "'" & FirstTest & "'"
ElseIf Not IsNull(SecondTest) Then
If IsNull(ThirdTest) Then
strWhere = strWhere & " TestNo IN ('" & FirstTest & "' ,
'" & SecondTest & "')"
ElseIf Not IsNull(ThirdTest) Then
strWhere = strWhere & " TestNo IN ('" & FirstTest & "' ,
'" & SecondTest & "' , '" & ThirdTest & "')"
End If
End If
End If
End If

I would appreciate it if you could help me out in this.

Thanks,
Curt
 
Add a debug.print to check your where clause
If Not IsNull(FirstTest) Then
If IsNull(SecondTest) Then
If IsNull(ThirdTest) Then
strWhere = strWhere & " TestNo = " & "'" & FirstTest & "'"
ElseIf Not IsNull(SecondTest) Then
If IsNull(ThirdTest) Then
strWhere = strWhere & " TestNo IN ('" & FirstTest & "' ,
'" & SecondTest & "')"
ElseIf Not IsNull(ThirdTest) Then
strWhere = strWhere & " TestNo IN ('" & FirstTest & "' ,
'" & SecondTest & "' , '" & ThirdTest & "')"
End If
End If
End If
End If
Debug.Print strWhere
 
Duane,

Thanks, I got it to work.
I do have one more question concerning this. It is possible to have as many
as 20 different TestNo's to be printed on one report. I would like to know if
there is some way to write the code to achieve this without creating a nested
IF statement that would be enormous?

Curt
 
You could name your text boxes like
txtTestNo1
txtTestNo2
txtTestNo3
etc
Then use code to loop through all of these
Dim strWhere as String
Dim intNo as Integer
strWhere = " [TestNo] In ('-999'"
'caution air coding
For intNo = 1 to 20
If Not IsNull(Me("txtTextNo" & intNo)) Then
strWhere = strWhere & ",'" & Me("txtTextNo" & intNo) & "' "
End If
Next
strWhere = strWhere & ")"
 
Duane,

Thanks alot this was exactly what I needed. You have been most helpful.

Curt

Duane Hookom said:
You could name your text boxes like
txtTestNo1
txtTestNo2
txtTestNo3
etc
Then use code to loop through all of these
Dim strWhere as String
Dim intNo as Integer
strWhere = " [TestNo] In ('-999'"
'caution air coding
For intNo = 1 to 20
If Not IsNull(Me("txtTextNo" & intNo)) Then
strWhere = strWhere & ",'" & Me("txtTextNo" & intNo) & "' "
End If
Next
strWhere = strWhere & ")"


--
Duane Hookom
MS Access MVP
--

Curt Hand said:
Duane,

Thanks, I got it to work.
I do have one more question concerning this. It is possible to have as many
as 20 different TestNo's to be printed on one report. I would like to know if
there is some way to write the code to achieve this without creating a nested
IF statement that would be enormous?

Curt
Click to expand...
Click to expand...
 
Back
Top