Query Help "If Then"

[Blackops]

Hello,

I'm hoping someone can assist me with an issue I have been having. I have been using access for 8 years now, but it's all self taught and my limitation have been reached with coding/ expression.

My question is; I would like to have a field named "Certificate", show the following message (Certificate will be provided upon request) when any data at all is in the field. I'm trying to do this in a query. I found this expression on the net and tried to change it to suit my needs.

DisplayText: IIf([Certificate]=Is Not Null,"Certificate will be provided upon request",)
Am I completely wrong? Any help at all would be appreciated. Thank you in advance.

 

[pbaldy]

Re: Query Help "If Than"

Try

DisplayText: IIf(IsNull([Certificate]), "","Certificate will be provided upon request")

 

[Blackops]

Re: Query Help "If Than"

It did work however, it seems to only work if I type it into the query in another field. Is their anyway for it to show the message in the same field the data is in. Meaning can it not show the data but instead show the text.

Thank you for your help pbaldy

 

[pbaldy]

Re: Query Help "If Than"

Not sure what you're asking. You can delete the actual field from the query if you don't want it. You can do this type of thing:

DisplayText: IIf(IsNull([Certificate]), "Certificate will be provided upon request",[Certificate])

to display either the data or the text as appropriate.

 

[Blackops]

Re: Query Help "If Than"

I'm sorry I understand now. It never occurred to me to just delete the field from the query and allow the new expression to display the message I want for it to display. Thank you very much for your help.

I do have one other question on the same topic. How can I modify the Expression to return the same text if the "Certificate" field has a certain entry.

Example: If the field [Certificate]show LH-11-0001, how can I make it show the text i want it so show "Cerificate will be provided upon request".

 

[pbaldy]

Try

DisplayText: IIf([Certificate]="LH-11-0001","Certificate will be provided upon request", "")

 

[Blackops]

Perfect, thank you very much. It works. This will help me a great deal

 

[pbaldy]

No problem, and welcome to the site by the way!