Expression Builder

  • Thread starter Thread starter AJ
  • Start date Start date
A

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.
 
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.
Click to expand...

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]
 
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
__________________________________________

John W. Vinson said:
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.
Click to expand...

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]
Click to expand...
 
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


Tom Wickerath said:
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
__________________________________________

John W. Vinson said:
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.
Click to expand...

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]
Click to expand...
Click to expand...
 
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.
Click to expand...

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]
 
Back
Top