qdef.Execute options??

[AccessFreak]

hey does someone know what the options are for a querydefinition execute.

Look. I have a listboc and a query that has 4 columns, but I set it on 5 at the properties. What I want to do is. if the fourth column IS NULL then the fifth column is also NULL. But if the fourth column has a number than the unbound fifth column needs to run a query and deliver this in the fifth column.

So I have this as my vba code.

Dim Qdef As DAO.QueryDef
Set Qdef = CurrentDb.CreateQueryDef("")
Qdef.SQL = "SELECT tbl_Portfolio.Portfoliocode From tbl_Portfolio Where tbl_Portfolio.PortfolioID = (@PortfolioID)"

For i = 1 To Me.list_Employees.ListCount - 1
If IsNull(Me.list_Employees.Column(4, i)) Then
Else
Qdef.Parameters("@PortfolioID").Value = Me.list_Employees.Column(4, i)
Qdef.Execute 'what can i type here.. or what are the options.
End If
Next i

The query needs to set the value to column (5, i)

How can I do that.

 

[Husan]

i would suggest creating a 5th column in yr original query using a iif statement with a dlookup inside it:

somethin like this:
5thField:iif(isnull(4thfield),null,dlookup("Portfoliocode","tbl_Portfolio","PortfolioID"=4thfield))

 

[AccessFreak]

I think you are on the right track BUT how can i set this in the GROUP BY.. Because it gives me a problem. At the moment I said

SELECT tbl_Employee.[Employee First Name] AS FirstName, tbl_Employee.[Employee Middle Name] AS MiddleName, tbl_Employee.[Employee Last Name] AS LastName, tbl_Employee.[Business Unit] AS BU, tbl_EmpPortRule.PortfolioID AS PortfolioID1, Portfolio(IIf(ISNull(PortfolioID1),Portfolio Is Null,DLookup("Portfoliocode","tbl_Portfolio","PortfolioID = PortfolioID1")))
FROM tbl_Employee, tbl_EmpPortRule
WHERE tbl_Employee.Username=tbl_EmpPortRule.Username And tbl_EmpPortRule.Applicationcode=Forms.Startmenu.Subform_menu.Form.box_Applications
GROUP BY tbl_Employee.[Employee First Name], tbl_Employee.[Employee Middle Name], tbl_Employee.[Employee Last Name], tbl_Employee.[Business Unit], tbl_EmpPortRule.PortfolioID, Portfolio;

 

[AccessFreak]

I changed it in this and it doesn't work also

SELECT tbl_Employee.[Employee First Name] AS FirstName, tbl_Employee.[Employee Middle Name] AS MiddleName, tbl_Employee.[Employee Last Name] AS LastName, tbl_Employee.[Business Unit] AS BU, tbl_EmpPortRule.PortfolioID AS PortfolioID1, IIf(tbl_EmpPortRule.PortfolioID Is Null,Portfolio Is Null,DLookup("tbl_Portfolio.Portfoliocode","tbl_Portfolio","tbl_Portfolio.PortfolioID = tbl_EmpPortRule.PortfolioID")) AS Portfolio
FROM tbl_Employee, tbl_EmpPortRule
WHERE tbl_Employee.Username=tbl_EmpPortRule.Username And tbl_EmpPortRule.Applicationcode=Forms.Startmenu.Subform_menu.Form.box_Applications
GROUP BY tbl_Employee.[Employee First Name], tbl_Employee.[Employee Middle Name], tbl_Employee.[Employee Last Name], tbl_Employee.[Business Unit], tbl_EmpPortRule.PortfolioID;

 

[AccessFreak]

Thanxxx I got it.

This was the query.

SELECT tbl_Employee.[Employee First Name] AS FirstName, tbl_Employee.[Employee Middle Name] AS MiddleName, tbl_Employee.[Employee Last Name] AS LastName, tbl_Employee.[Business Unit] AS BU, tbl_EmpPortRule.PortfolioID AS PortfolioID1, IIf(tbl_EmpPortRule.PortfolioID IS Not Null,(SELECT tbl_Portfolio.Portfoliocode FROM tbl_Portfolio WHERE tbl_Portfolio.PortfolioID = tbl_EmpPortRule.PortfolioID)) AS Portfolio
FROM tbl_Employee, tbl_EmpPortRule
WHERE tbl_Employee.Username=tbl_EmpPortRule.Username And tbl_EmpPortRule.Applicationcode=Forms.Startmenu.Subform_menu.Form.box_Applications
GROUP BY tbl_Employee.[Employee First Name], tbl_Employee.[Employee Middle Name], tbl_Employee.[Employee Last Name], tbl_Employee.[Business Unit], tbl_EmpPortRule.PortfolioID;

 

[Husan]

why dont you get yr query working with the 4 columns first. once you happy with this then just create a new query based on this where u return the 4 colums + add a new column usinf iif statement and dlookup then use that for yr listbox.