Hi all,i will really appreciate if someone could help me out.I have a query named RequirementQuery and here is it's structure
SELECT Requirements.RequirementId, Components.ComponentTag, Products.ProductTag, Releases.ReleaseVersion, Statusses.StatusTag, RequirementCategories.RequirementCategorySynopsis, RequirementCategories.RequirementCategoryL1, RequirementCategories.RequirementCategoryL2, RequirementCategories.RequirementCategoryL3, Requirements.RequirementDescription, Objects.Object
FROM (((Releases INNER JOIN ((Products INNER JOIN (((Components INNER JOIN (Requirements INNER JOIN ComponentRequirements ON Requirements.RequirementNumber = ComponentRequirements.RequirementNumber) ON Components.ComponentNumber = ComponentRequirements.ComponentNumber) INNER JOIN Statusses ON Requirements.StatusNumber = Statusses.StatusNumber) INNER JOIN ProductRequirements ON Requirements.RequirementNumber = ProductRequirements.RequirementNumber) ON Products.ProductNumber = ProductRequirements.ProductNumber) INNER JOIN RequirementReleases ON Requirements.RequirementNumber = RequirementReleases.RequirementNumber) ON Releases.ReleaseNumber = RequirementReleases.ReleaseNumber) INNER JOIN RequirementCategories ON Requirements.RequirementCategoryNumber = RequirementCategories.RequirementCategoryNumber) INNER JOIN ObjectRequirements ON Requirements.RequirementNumber = ObjectRequirements.RequirementNumber) INNER JOIN Objects ON ObjectRequirements.ObjectNumber = Objects.ObjectNumber
WHERE (((RequirementReleases.RequirementToBeSupported)=True));
From the above query i generated this report :" RequirementQuery_Report" and i have a command button that opens this report according certain conditions.
My report hodls all information regarding the requirements in my database such as the products,status,and release version( this last three are listed in a listbox each)
so whenever i select a certain product,with a certain status and a certain release value i will get the report.
But my problem is, some requirements have 2 products.
For example Req1 is related to Product And B,if i select Product A in the list box i will get Req1 same as if i select Product B and that is not what i want.What i want is to get Req1 only if i select Product A and B.Here below is the code i wrote,it gives me Req1 wether i select Product A or B.
Could some one help me figure out how to handle this.
Private Sub Generate_Report_Click()
Dim var As Variant ' Product Selected
Dim status As Variant ' Status selected
Dim Release As Variant 'Release version selected
Dim strf As String ' holds Report filter
Dim iCounter As Integer 'a counter
iCounter = 1 'Initialize it to 1 to be able to compare it with the last selected item
If Not IsNull(var) Then
If Not IsNull(status) Then
If Not IsNull(Release) Then
strf = " ( "
For Each var In Me.ProductList.ItemsSelected
strf = strf + "RequirementQuery.ProductTag = '" + (Me.ProductList.Column(1, var)) + "' "
If iCounter < Me.ProductList.ItemsSelected.Count Then ' As long as icounter<the last Prod selected; add OR
strf = strf + "OR "
End If
iCounter = iCounter + 1
Next var
strf = strf + " ) AND ( "
iCounter = 1 'Initialize icounter again otherwise it will still have the previous value
For Each status In Me.StatusList.ItemsSelected
strf = strf + "RequirementQuery.StatusTag= '" + (Me.StatusList.Column(1, status)) + "'"
If iCounter < Me.StatusList.ItemsSelected.Count Then ' As long as iCouter< the last status selected add OR
strf = strf + " OR "
End If
iCounter = iCounter + 1
Next status
strf = strf + " )"
strf = strf + " AND ( "
iCounter = 1
For Each Release In Me.ReleaseList.ItemsSelected
strf = strf + "RequirementQuery.ReleaseVersion= '" + (Me.ReleaseList.Column(1, Release)) + " ' "
If iCounter < Me.ReleaseList.ItemsSelected.Count Then
strf = strf + " OR "
End If
iCounter = iCounter + 1
Next Release
strf = strf + " )"
DoCmd.OpenReport "RequirementQuery_Report", acViewPreview, , "( " + strf + " )"
Else
MsgBox "Provide a Release version"
Exit Sub
End If
Else
Exit Sub
End If
End If
End sub
Please help.
Best Regards,
Joelle
