|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 28th, 2004, 03:13 PM
|
|||
|
|||
Print report based on multiple criteria of the same control.
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 
|
|
#2
September 28th, 2004, 03:31 PM
|
|||
|
|||
|
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 -- Duane Hookom MS Access MVP -- "Curt Hand" wrote in message ... 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
|
|
#3
September 28th, 2004, 03:49 PM
|
|||
|
|||
|
Thanks Duane.
This worked. Curt "Duane Hookom" wrote: 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 -- Duane Hookom MS Access MVP -- "Curt Hand" wrote in message ... 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
|
|
#4
September 28th, 2004, 05:21 PM
|
|||
|
|||
|
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 "Curt Hand" wrote: Thanks Duane. This worked. Curt "Duane Hookom" wrote: 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 -- Duane Hookom MS Access MVP -- "Curt Hand" wrote in message ... 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
|
|
#5
September 28th, 2004, 06:57 PM
|
|||
|
|||
|
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 Hookom MS Access MVP "Curt Hand" wrote in message ... 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 "Curt Hand" wrote: Thanks Duane. This worked. Curt "Duane Hookom" wrote: 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 -- Duane Hookom MS Access MVP -- "Curt Hand" wrote in message ... 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
|
|
#6
October 1st, 2004, 03:05 PM
|
|||
|
|||
|
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 "Duane Hookom" wrote: 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 Hookom MS Access MVP "Curt Hand" wrote in message ... 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 "Curt Hand" wrote: Thanks Duane. This worked. Curt "Duane Hookom" wrote: 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 -- Duane Hookom MS Access MVP -- "Curt Hand" wrote in message ... 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
|
|
#7
October 1st, 2004, 08:24 PM
|
|||
|
|||
|
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" wrote in message news  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 "Duane Hookom" wrote: 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 Hookom MS Access MVP "Curt Hand" wrote in message ... 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 "Curt Hand" wrote: Thanks Duane. This worked. Curt "Duane Hookom" wrote: 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 -- Duane Hookom MS Access MVP -- "Curt Hand" wrote in message ... 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
|
|
#8
October 4th, 2004, 02:53 PM
|
|||
|
|||
|
Duane,
Thanks alot this was exactly what I needed. You have been most helpful. Curt "Duane Hookom" wrote: 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" wrote in message news 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 "Duane Hookom" wrote: 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 Hookom MS Access MVP "Curt Hand" wrote in message ... 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 "Curt Hand" wrote: Thanks Duane. This worked. Curt "Duane Hookom" wrote: 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 -- Duane Hookom MS Access MVP -- "Curt Hand" wrote in message ... 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
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How do I print the details view | David | Running & Setting Up Queries | 5 | August 28th, 2004 12:17 AM |
| How to assign value to a report control? | Peter | General Discussion | 3 | June 29th, 2004 11:17 AM |
| Sum data based on multiple criteria | Don Guillett | General Discussion | 6 | June 22nd, 2004 04:50 PM |
| Countif function based on multiple criteria | Craig Mowbray | Worksheet Functions | 4 | September 28th, 2003 10:42 PM |
Linear Mode
