Expressions

[dsk525]

Hi,

I have three fields with expression for each:

Field 1: OLD PRICE
=IIf([Old ESUP]>0,[Old ESUP]-([Old ESUP]*[Old DKLPCT])-[Old MPA],"&quot

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



Field 3: PRICE PRO. AMT. / UNIT
=IIf([Old ESUP]>0,(CLng([Text70]*100)-([Text68]*100))/100,"&quot

What I would like to do is, if there is a difference of 0 between field 1 and 2, then I do not want to show any values in field 1 or 2 or 3.

Any help would be much appreciated!

Thank you.

 

[MrAccess]

dsk525,

One solution is...
Include the test
(nz(Field1 expression)<>nz(Field2 expression))
in the iif of each field


EXAMPLE

Field 1: 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 2: 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 3: 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



Another solution is...
In the query for your form or report, include your Field1, Field2, and Field3 expressions. (If your form/report is bound to a table instead of a query, change it to use a query instead)

Have three textboxes on your form/report with the following ControlSources:

Textbox1.ControlSource
=IIF(nz([Field1])<>nz([Field2]),[Field1],&quot;&quot

Textbox2.ControlSource
=IIF(nz([Field1])<>nz([Field2]),[Field2],&quot;&quot

Textbox3.ControlSource
=IIF(nz([Field1])<>nz([Field2]),[Field3],&quot;&quot

 

[Guest_imported]

I want to write an expression in Access 97 in a form letter that will say Dear [First Name]: If the First name only has an initial I want to say Dear Mr. [Last Name]: I have trying to write this myself and have had no luck. I am new to Access. Can you help?

 

[MrAccess]

Assuming that your &quot;form letter&quot; is an Access Report,

Either in the query for your form letter or in the textbox control on the form letter

If using a query create a column
DearName: =IIF(LEN([FirstName])>1,[FirstName],&quot;Mr. &quot; & [LastName])
and reference [DearName] in your form letter.

OR

If using a textbox on a report
set the textbox's control source to
=IIF(LEN([FirstName])>1,[FirstName],&quot;Mr. &quot; & [LastName])



However [FirstName] having only an initial could potientially mean for John Smith either J Smith or J. Smith

But you must also allow for Ed Smith to be Dear Ed

so you might want something like...

=IIF(LEN([FirstName])>2 OR (LEN([FirstName])=2 AND MID([FirstName],2,1)<>&quot;.&quot,[FirstName],&quot;Mr. &quot; & [LastName])

Be very careful with your parenthesis.