HELP with expression

[dsk525]

Hi,

I have five fields in a report with expression for each field in control source:

Field 1: Description
Description

Field 2: Item Number
Item Number

Field 3: OLD PRICE
=IIf([Old ESUP]>0 AND (nz([Old ESUP]-([Old ESUP]*[Old DKLPCT])-[Old MPA])<>nz([ESUP]-([ESUP]*[DKLPCT])-[Current MPA])),[Old ESUP]-([Old ESUP]*[Old DKLPCT])-[Old MPA],&quot;&quot

Field 4: NEW PRICE
=IIF((nz([Old ESUP]-([Old ESUP]*[Old DKLPCT])-[Old MPA])<>nz([ESUP]-([ESUP]*[DKLPCT])-[Current MPA])),[ESUP]-([ESUP]*[DKLPCT])-[Current MPA],&quot;&quot

Field 5: PRICE PRO. AMT. / UNIT
=IIf([Old ESUP]>0 AND (nz([Old ESUP]-([Old ESUP]*[Old DKLPCT])-[Old MPA])<>nz([ESUP]-([ESUP]*[DKLPCT])-[Current MPA])),(CLng([Text70]*100)-([Text68]*100))/100,&quot;&quot

What I would like to do is, if there is a difference of zero between field 3 and 4, then I do not want to show any values in field 1 or 2 or 3 or 4 or 5. I've got it to work where no values are being shown in field 3, 4, or 5. However, values in field 1 and 2 are still being shown. I do not want any values in field 1 or 2 either if there is a difference of zero between field 3 and 4.

Thank you in advance.

DK

 

[GingerR]

rename fields and change control source:

Field1: Description1 (cannot name Description it will give an error)
ControlSource: iif([New Price]-[Old Price]=0,&quot;&quot;,[Description])

Field2: ItemNumber1
ControlSource: iif([New Price]-[Old Price]=0,&quot;&quot;,[Item Number])

hope it helps.

 

[MrAccess]

METHOD #1
Continue with the same technique you used from my previous post.

Field 1: Description
=IIF((nz([Old ESUP]-([Old ESUP]*[Old DKLPCT])-[Old MPA])<>nz([ESUP]-([ESUP]*[DKLPCT])-[Current MPA])),[Description],'')

Field 2: Item Number
=IIF((nz([Old ESUP]-([Old ESUP]*[Old DKLPCT])-[Old MPA])<>nz([ESUP]-([ESUP]*[DKLPCT])-[Current MPA])),[Item Number],'')



However this can get real ugly real quick if you need to get more complex with your logic.

METHOD #2
From what I have gathered from your description of your
report, you may want to handle this with queries.

Have Query#1 collect all of the data and add columns to compute Field3, Field4, and Field5.

Have Query#2 select only those records from Query#1 WHERE Field3<>Field4

Base your report on Query#2

This way youur formulas for Fields 3-5 would be more readable and maintainable::

Field 1: Description
Description

Field 2: Item Number
Item Number

Field 3: OLD PRICE
=[Old ESUP]-([Old ESUP]*[Old DKLPCT])-[Old MPA]

Field 4: NEW PRICE
=[ESUP]-([ESUP]*[DKLPCT])-[Current MPA]

Field 5: PRICE PRO. AMT. / UNIT
=(CLng([Text70]*100)-([Text68]*100))/100

This method works only if you do not have any fields in Query#1 that need to appear even if Filed3=Field4.


However you could still use this 2nd method by having Query#1 include only those fields which need to disappear when Field3=Field4 plus any fields that can join back to your original table. Have Query#2 select only records where Field3<>Field4. Then have Query#3 combine fields from your original table with the fields from Query#2. Be sure to create a join that includes all records from the original table and only those records from Query#2 where the joined fields are equal.

 

[dsk525]

Mr. Access,

I am new to Microsoft Technologies, and just wanted to acknowledge that your guidance is much appreciated!

Thanks,
D.K.