query in VBA

[MarkWright]

Hello,
I have a a table with the fields: ProduktNr, testnr, Ueb.
I have to sort the field to show certain information that is filtered into a formular.
I have -
PNr tnr U
9110 71 z
9026 108 z
9026 167 z
9049 167 z
what I need is a way to show only 9110 and 9049. I have made 2 queries to show eather 9110,9026.9049 Or only 9026 but I have not found a way to not to show 9026 with the other two numbers.
I have to show this information in a formular and have been trying to use a recordset but have not been having any luck.
so far I have been trying with this code to get something to work but am running into walls everyway I turn. I really need some help or some advice on what I could do.
Thanks in advance
Mark
code -->
'**show Z
rstZ.Open "SELECT PBS_TestzyclusProblemNr.testnr, " & _
" PBS_TestzyclusProblemNr.ProblemNr, PBS_Produkt.Kurzname, PBS_Probleme.Release," & _
" PBS_TestzyclusProblemNr.Uebernehmen " & _
" FROM dbo.PBS_TestzyclusProblemNr INNER JOIN dbo.PBS_Probleme ON" & _
" dbo.PBS_TestzyclusProblemNr.ProblemNr = dbo.PBS_Probleme.ProblemNr " & _
" INNER Join dbo.PBS_Produkt ON " & _
" dbo.PBS_Probleme.ProduktNr = dbo.PBS_Produkt.ProduktNr" & _
" WHERE (PBS_Produkt.Kurzname = '" & Me!Produkt & "') AND" & _
" (PBS_Probleme.Release = '" & Me!Release1 & "')AND " & _
" (dbo.PBS_TestzyclusProblemNr.Uebernehmen = 'Z')", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'***mit Z and J -- J shown
' Me.RecordSource = "SELECT qryTeytzyclusProblemNrZ.Kurzname," & _
' " qryTeytzyclusProblemNrZ.Release, qryTestzyclusProblemNrJ.Uebernehmen," & _
' " qryTestzyclusProblemNrJ.testnr, qryTestzyclusProblemNrJ.ProblemNr," & _
' " qryTeytzyclusProblemNrZ.Uebernehmen AS Expr1 " & _
' " FROM dbo.qryTestzyclusProblemNrJ RIGHT OUTER JOIN " & _
' " dbo.qryTeytzyclusProblemNrZ ON " & _
' " dbo.qryTestzyclusProblemNrJ.ProblemNr = dbo.qryTeytzyclusProblemNrZ.ProblemNr" & _
' " WHERE (dbo.qryTestzyclusProblemNrJ.Uebernehmen = 'J')" ', CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rstZ.BOF And rstZ.EOF = True Then
MsgBox "Kein ProblemNr gefunden "
DoCmd.close
Exit Sub

Else
Do While Not rstZ.EOF

prb = DLookup("[ProblemNr]", "PBS_TestzyclusProblemNr", "[ProblemNr] = '" & rstJ!ProblemNr & "'&quot
If prb = True Then

'no idea what yet
End If


rstZ.MoveNext
Loop

 

[VBslammer]

You may be able to do it with the 'IN' clause:

  WHERE 
    (dbo.PBS_TestzyclusProblemNr.Uebernehmen='Z') 
  AND 
    (dbo.PBS_TestzyclusProblemNr.ProduktNr IN (9110,9049));

VBSlammer

Unemployed in Houston, Texas

 

[MarkWright]

Thanks VBslammer,
The only thing is I have to scann a table not just the two numbers. But I'll give it a try and see what happens.

Mark

 

[VBslammer]

You could exclude just the unwanted numbers in the same fashion using negation:

  'using a list of exclusions.
  AND NOT
    (dbo.PBS_TestzyclusProblemNr.ProduktNr IN (9026,XXXX));
 
  'using a single exclusion.
  AND NOT
    (dbo.PBS_TestzyclusProblemNr.ProduktNr = 9026);

VBSlammer

Unemployed in Houston, Texas