Report Intelligence 1

[sanders720]

I have a need to add a field of data to a report only if the criteria for another field equals one of five possible values. Otherwise, this information would be blank.

I am thinking of utilizing the dLookup feature. We have a database of parts that are either purchased, custom, modified or painted. If the part on the database is painted, dLookup should lookup that parts manufacturer in the table.

The problem is, where is this coded? I believe it would look something like this...

If Code.Value = "Painted" Then
Text117.Value = DLookUp("[ManufacturedBy]","tblPartsListing"," [PartNo] = '" & [Reports]![rptManufactured Parts List]![PartNo] & "' &quot

Else
Text117.Value = Null
End If


There really is not much information out about VBA with reports, at least compared to forms - where most of my stuff gets customized. Are you aware of a good overview source on this?

Thankyou in advance for your help.

 

[JohnLowell]

Put the code in the Detail On_Format event.

 

[galar]

I have another suggestion:
The Dlookup function can slow the report and it is not nessesary in your case.
The more appropriate way is to use the iif() in the field's ControlSource.
I'd put that field on the report and put in the ControlSource property of the field this code:
=iif([criteriaField]=wantedValue, valueOfNeededField, "&quot

I'm willing to provide more explanations if needed.

Good luck

 

[sanders720]

Thankyou:

I do have one more question about this topic. The value of the needed field is based on the DLOOKUP criteria. I do not think I can use DLOOKUP within the iif command. What would be toe syntax? Also, is the , "" at the end of the iif the else condition?

 

[galar]

You can use the DLookup expression freely in the IIF() function and yes, the "" is in the "else" condition. I'll rewrite the code you've written to the IIF() function:
=IIF(

 = "Painted",DLookUp("[ManufacturedBy]","tblPartsListing"," [PartNo] = '" & [Reports]![rptManufactured Parts List]![PartNo] & "' "), Null)

The iif() function is very usefull in forms and reports where one field's value can depend on other fields value or any expression value. Also, you can use it in VBA instead of if..then..else..

 

[sanders720]

I think I have it working satisfactorily. Thanks again...

 

[galar]

You're welcome. I hope you'll succeed.