How to return values found within the table?

[MsAxes]

Hello all - I have a table where I have a "Yes/No" column. I need a new field; if that record contains "Yes" then I need to look up the ParentComp field and return whatever the debt is for that company. Example, Comp3 should return the debt for Comp1 which is 504. If the field = "No", then return the debt it has, ex. Comp1 would return a debt of 504.



Any ideas on what function to use? DLookup? Not sure here.

[ranman256]

something like: getDebtViaID()


function getDebtViaID(byval pvCustID)
dim bValid as boolean
dim vID


'here yes/no is boolean , not a string 'yes' or 'no'
bValid = dlookup("[field]","table","[CustID]=" & pvCustID)
if bValid then 'get parents debt
vID = dlookup("[ParentComp]","table","[CustID]=" & pvCustID)
getDebtViaID= getDebt(vID)
else 'get normal debt
getDebtViaID= getDebt(pvCustID)
endif
exit function


function getDebt(byval pvCustID)
getDebt = dlookup("[field]","table","[CustID]=" & pvCustID)
end function

[Bob Fitz]

IMHO your table needs to be changed. I have removed the "Yes/No" field. As far as I can see it serves no purpose. If the "ParentComp" field has data, then the company has a Parent Comp.
In any case "Special" keys like "/" should not be used in the name of any object in Access. Neither should "Reserved" words be used, so I have changed the first field to "CName" rather than "Name".
I have not added one but all tables should have a Primary Key. An auto-number field is often a suitable choice.

Take a look at the query in the attached db. I believe this fulfils your requirement.

[ssanfu]

@ Bob,
I modified your query so that when the YN column is No, the debt field is returned:

NEW: IIf(Len([ParentComp] & "")<=0,[debt],DLookUp("debt","sheet1","[CName] = '" & [parentcomp] & "'"))

If the field = "No", then return the debt it has, ex.
Comp1 would return a debt of 504.

Here is the SQL of the query

SELECT Sheet1.CName, IIf(Len([ParentComp])>0,"Yes","No") AS YN, Sheet1.debt, Sheet1.ParentComp, IIf(Len([ParentComp] & "")<=0,[debt],DLookUp("debt","sheet1","[CName] = '" & [parentcomp] & "'")) AS NEW
FROM Sheet1;