Creating a new field based on conditions

[ksto]

I have a database that tracks insurance information for our various vendors.
Each insurance type has 2 fields - a requirement field (yes/no), and an
effective field (some show an expiration date, some are yes/no). I have
created a query that will return only the records for which insurance is
required but is expired/missing. My problem is that I want to create a new
field that is calculated based on the values in the other two fields in order
to make the resulting report more user-friendly.

For example, if GLRequired is True and GLExpiration is <Now(), I want the
new field to say "Expired". If GLRequired is True and GLExpiration is null,
I want the new field to say "Missing". Is this possible?

I'd appreciate any help you can offer!

 

[Marshall Barton]

I have a database that tracks insurance information for our various vendors.
Each insurance type has 2 fields - a requirement field (yes/no), and an
effective field (some show an expiration date, some are yes/no). I have
created a query that will return only the records for which insurance is
required but is expired/missing. My problem is that I want to create a new
field that is calculated based on the values in the other two fields in order
to make the resulting report more user-friendly.

For example, if GLRequired is True and GLExpiration is <Now(), I want the
new field to say "Expired". If GLRequired is True and GLExpiration is null,
I want the new field to say "Missing". Is this possible?

You should NOT put calculated fields in a table. Instead,
use a calculated field in a query and use the query as the
report's record source. OR just out the expression in a
report text box:

Either way the expression would be something like:

IIf(GLRequired And GLExpiration<Now(),"Expired",
IIf(GLRequired And GLExpiration Is Null,"Missing",""))

 

[ksto]

I guess I didn't specify that I am a total beginner : )

The expression definitely makes sense to get back the data I'm looking for,
so thank you. I am trying to create the field ("Status") in a query which,
as you suggest, will be used as the data source for my report. I just don't
know where in the query to enter that expression to make it create the new
field. I've tried entering it in a blank column in the design view of the
query but when I try to switch to datasheet view, it asks me to enter the
parameter value for Status. I've tried entering "Missing", "Expired" and
leaving it blank but it never returns any records.

Thanks for helping a new arrival to the world of Access!

 

[Marshall Barton]

That expression does not include the word Status so I don't
see how you could be prompted for its value.

To create a calulated field in a query, enter this kind of
thing in the Fields row of a blank column in the grid:

Status: IIf(GLRequired And GLExpiration<Now(),"Expired",
IIf(GLRequired And GLExpiration Is Null,"Missing",""))

 

[ksto]

There's my problem - I was entering the expression in the Criteria row
instead of the Fields row. It works perfectly now. Thank you!!

That expression does not include the word Status so I don't
see how you could be prompted for its value.

To create a calulated field in a query, enter this kind of
thing in the Fields row of a blank column in the grid:

Status: IIf(GLRequired And GLExpiration<Now(),"Expired",
IIf(GLRequired And GLExpiration Is Null,"Missing",""))
--
Marsh
MVP [MS Access]

I guess I didn't specify that I am a total beginner : )

The expression definitely makes sense to get back the data I'm looking for,
so thank you. I am trying to create the field ("Status") in a query which,
as you suggest, will be used as the data source for my report. I just don't
know where in the query to enter that expression to make it create the new
field. I've tried entering it in a blank column in the design view of the
query but when I try to switch to datasheet view, it asks me to enter the
parameter value for Status. I've tried entering "Missing", "Expired" and
leaving it blank but it never returns any records.