Make the values of the field blank


G

Guest

Hi,

Is it possible to replace a value of a field to “BLANK†when you run a
query? I still want the record to appear but I want make the values of one
field to appear blank after you run a query.

Please advise!
 
Ad

Advertisements

R

Randy

Kash said:
Hi,

Is it possible to replace a value of a field to "BLANK" when you run a
query? I still want the record to appear but I want make the values of one
field to appear blank after you run a query.

Please advise!

Kash, since you want to keep the original value of the field intact, you
should not use the field itself on your query. Instead create a new column
on the data grid of the query with the following header:

NewField: IIF(nz([My Field], "") = "", "XXXXX", [My Field])

XXXXX is the value you will see on the screen whenever the field [My Field]
is blank (or NULL).

-Randy
 
G

Guest

Randy,

You are a genius! thanks a lot!

Randy said:
Kash said:
Hi,

Is it possible to replace a value of a field to "BLANK" when you run a
query? I still want the record to appear but I want make the values of one
field to appear blank after you run a query.

Please advise!

Kash, since you want to keep the original value of the field intact, you
should not use the field itself on your query. Instead create a new column
on the data grid of the query with the following header:

NewField: IIF(nz([My Field], "") = "", "XXXXX", [My Field])

XXXXX is the value you will see on the screen whenever the field [My Field]
is blank (or NULL).

-Randy
 
G

Guest

Hi Randy,

This is real helpful. I have another question for you. Below is how I am
using the code you gave me:

NewField: IIf(nz([Change],"new")="new"," ",[Change])

I am asking it to give me a "blank" value whenever it encounters a "new"
value. But how should I change the code to give me a "blank" value when it
encounters multiple values either "new" or "title" (these are the only two
possible values)...... please advise!!!!...


Randy said:
Kash said:
Hi,

Is it possible to replace a value of a field to "BLANK" when you run a
query? I still want the record to appear but I want make the values of one
field to appear blank after you run a query.

Please advise!

Kash, since you want to keep the original value of the field intact, you
should not use the field itself on your query. Instead create a new column
on the data grid of the query with the following header:

NewField: IIF(nz([My Field], "") = "", "XXXXX", [My Field])

XXXXX is the value you will see on the screen whenever the field [My Field]
is blank (or NULL).

-Randy
 
Ad

Advertisements

R

Randy

Kash said:
Hi Randy,

This is real helpful. I have another question for you. Below is how I am
using the code you gave me:

NewField: IIf(nz([Change],"new")="new"," ",[Change])

I am asking it to give me a "blank" value whenever it encounters a "new"
value. But how should I change the code to give me a "blank" value when it
encounters multiple values either "new" or "title" (these are the only two
possible values)...... please advise!!!!...

Kash, glad all it's working. To handle multiple cases you can either use
nested IIFs or use the OR operator. I'll use OR since it is easier for you
to read and understand. Both of the functions below will modify the output
of the "Change" field to a space (" ") when its value is any of the
followings: "new", "title", "" (empty), or Null. Otherwise the field is left
intact. I don't know if you also want to handle the case when the field is
as space (" ") itself (well, let me know).

This function is case aware:
NewField: IIF( (NZ([Change],"new") = "new" ) OR (NZ([Change],"title") =
"title" ), " ", [Change])

This other function works for either upper or lower cases:
NewField: IIF( (LCASE(NZ([Change],"new")) = "new" ) OR
(LCASE(NZ([Change],"title")) = "title" ), " ", [Change])

-Randy
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top