Make a field appear blank based on another

[Guest]

I have a query with the fields "Spouse Name" and "Spouse Date of Birth" from
a another query called "Families. There is also another field called "NEWMG
Spouse Premium" from "NEWMG Premiums" query. I would like to make it so if
the "Spouse Name" field is blank the "NEWMG Spouse Premium" appears blank. Is
this possible?

 

[MGFoster]

I have a query with the fields "Spouse Name" and "Spouse Date of Birth" from
a another query called "Families. There is also another field called "NEWMG
Spouse Premium" from "NEWMG Premiums" query. I would like to make it so if
the "Spouse Name" field is blank the "NEWMG Spouse Premium" appears blank. Is
this possible?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In the query gird Field cell put something like this:

SpousePremium: IIf([Spouse Name] Is Not Null, [NEWMG Spouse Premium])

What it will do is check if the Spouse Name is NULL. If it is NOT NULL,
then it will show the NEWMG Spouse Premium value. If it IS NULL it will
show a NULL (blank).

You'll notice I did not have a False expression in the IIf() function.
In queries, this will mean if the evaluation expression returns False
the function returns NULL.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQuFAwYechKqOuFEgEQJ3RwCgmfgqic6UfvNzMq/ScvW0UiTNaUkAoJg0
oFXiCY6uvSp+PcJTeHskjuJM
=b+8j
-----END PGP SIGNATURE-----

 

[Guest]

MGFoster,

Hi! Thank you for the expression. I had to end up using this though:

Spouse Premium: IIf([Spouse Name] Is Null," ",[NEWMG Spouse Premium])

I'm having one problem... Once I put the expression in and try to close to
query I get a message saying "The Microsoft Jet database engine stopped the
process because you and another user are attempting to change the same data
at the same time". Do you have any idea why it would do this?

Tandy

 

[MGFoster]

MGFoster,

Hi! Thank you for the expression. I had to end up using this though:

Spouse Premium: IIf([Spouse Name] Is Null," ",[NEWMG Spouse Premium])

I'm having one problem... Once I put the expression in and try to close to
query I get a message saying "The Microsoft Jet database engine stopped the
process because you and another user are attempting to change the same data
at the same time". Do you have any idea why it would do this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It's better to use a NULL rather than a blank space in queries. You
never know - the query may be used as the data source in another query
and the usual criteria for Not there is

WHERE [Spouse Premium] Is NULL

The error you're getting "another user..." is possibly 'cuz another user
has the db open at the same time as you. Open the front-end (the user
interface - where the queries are) in Exclusive mode to make changes.
Make sure no one else has access to the same file you are working on
while you are working on it.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQuMN9YechKqOuFEgEQKo5QCgxkDokJgqZyiejGb9tU0NbySQkWoAniaW
2Z5yPd4a4/Lm/PPo16GzWP/j
=VSV2
-----END PGP SIGNATURE-----

 

[Guest]

MGFoster,

Hi! I didn't even think about using Null instead of a blank space, but
it makes sense now that you have brought it to my attention. Thank you. Thank
you also for helping me figure out how to actually work on my database I
really appreciate your help. I feel like sometime I know things in Access are
possible but just don't have the knowledge of how to get there!