Expression Builder

[AJ]

Is there a way with expression builder you can chnage the value of a field on
a report? For example the two I would like to do are:
1. If field = 0 then make blank or null
2. if field = 's' then display "store"

Thanks.

 

[John W. Vinson]

Is there a way with expression builder you can chnage the value of a field on
a report? For example the two I would like to do are:
1. If field = 0 then make blank or null
2. if field = 's' then display "store"

Thanks.

Sure. Use the IIF() function:

1. IIF([field] = 0, NULL, [field])
2. IIF([field] = "s", "Store", <whatever you want displayed if it isn't S,
such as [field] to display the actual field value>)

John W. Vinson [MVP]

 

[Tom Wickerath]

To add some to John's answer, include an equals sign in the expression for
the Control Source property of a text box on the report:

1. =IIF([field] = 0, NULL, [field])
2. =IIF([field] = "s", "Store")

Also, make sure that the Name of the text box control is *NOT* the same as
the field name. Otherwise, you will get a #Error showing up (circular
reference error).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Is there a way with expression builder you can chnage the value of a field on
a report? For example the two I would like to do are:
1. If field = 0 then make blank or null
2. if field = 's' then display "store"

Thanks.

Sure. Use the IIF() function:

1. IIF([field] = 0, NULL, [field])
2. IIF([field] = "s", "Store", <whatever you want displayed if it isn't S,
such as [field] to display the actual field value>)

John W. Vinson [MVP]

 

[Sarab]

I have used Access for years but am designing a new DB from scratch for the
1st time and am aware how much I really 'don't know' about Access!. I have
been trying for a week to do something similar but no success, saw this post
and thought, "ahah!". However I tried these in Expression Builder

=IIf([Lenders]![ZerofeeEnd]=#12/31/9999#,"YES",Null)

also tried
=IIf(Lenders!ZerofeeEnd<Date(),"NO","YES")

My field name for the report's text boxt is ysnZeroFee so I don't think I
have a 'circular reference' however it is returning "#Error" anyway.

[Lenders]![ZerofeeEnd] may be empty, may have a date prior to today's date,
or may have 12/31/9999 as default. Theoretically it could also have an
upcoming date in which case the answer should be "YES" as of today and change
to "NO" after that date.

Ideally I'd love to have it return a value from another field,
Lenders!ZeroFeeBegin, if the answer is YES. But I'll settle for a Yes/No
answer.
--
Sara
HESC/NTHEA
Customer Services Rep

To add some to John's answer, include an equals sign in the expression for
the Control Source property of a text box on the report:

1. =IIF([field] = 0, NULL, [field])
2. =IIF([field] = "s", "Store")

Also, make sure that the Name of the text box control is *NOT* the same as
the field name. Otherwise, you will get a #Error showing up (circular
reference error).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Is there a way with expression builder you can chnage the value of a field on
a report? For example the two I would like to do are:
1. If field = 0 then make blank or null
2. if field = 's' then display "store"

Thanks.

Sure. Use the IIF() function:

1. IIF([field] = 0, NULL, [field])
2. IIF([field] = "s", "Store", <whatever you want displayed if it isn't S,
such as [field] to display the actual field value>)

John W. Vinson [MVP]

 

[John W. Vinson]

I have used Access for years but am designing a new DB from scratch for the
1st time and am aware how much I really 'don't know' about Access!. I have
been trying for a week to do something similar but no success, saw this post
and thought, "ahah!". However I tried these in Expression Builder

=IIf([Lenders]![ZerofeeEnd]=#12/31/9999#,"YES",Null)

also tried
=IIf(Lenders!ZerofeeEnd<Date(),"NO","YES")

My field name for the report's text boxt is ysnZeroFee so I don't think I
have a 'circular reference' however it is returning "#Error" anyway.

That's probably because Access - in this context - has no idea what Lenders!
refers to. If it's the name of a table, you cannot refer to it from a form
control; you can refer to other Controls on the form; for instance, if there
is a textbox named txtZeroFeeEnd bound to the fieldname ZeroFeeEnd you could
use just

IIF([txtZeroFeeEnd], ...)

Or you can refer to a fieldname in the Form's Recordsource query - without
table qualification.

John W. Vinson [MVP]

 

[Sarab]

Thank you very much, John! You gave me exactly what I needed!