Still unable to run successful query

[JJ]

I've used the IFF suggestions given but it returns only Not Active results.
I'm working on a case problem that reads:

Create a query to display all matching records from the tblProgram and
tblMember tables, selecting the ProgramType and MonthlyFee fields from the
tblProgram table, and the FirstName and LastName fields from the tblMember
table.

Add a calculated field named MonthlyFeeStatus as the last column that equals
Active if the MembershipStatus field is equal to Active and equals Not Active
otherwise.

 

[Michel Walsh]

Assuming MembershipStatus is a string (varchar) data field, not an integer
automatically formatted through a lookup, the expression could be:


iif( MembershipStatus = "Active", "Active", "Not Active" )




which covers the case where the membershipStatus is NULL. If you get all
"Not Active" result, probably your MembershipStatus is an integer, not a
string, and you see "Active", or something else, under it, BECAUSE of a
formatted - LOOKUP. A formatted value IS NOT the stored value, necessary; as
example: 3.141592... formatted to two decimal places, is exactly 3.14, but
that displayed 3.14 is NOT the stored value 3.141592... Go in table design
and check what is really your field membershipStatus.




Vanderghast, Access MVP

 

[Conan Kelly]

JJ,

If doing this in the query design view, enter this....

MonthlyFeeStatus:
Iif([TableQualifier].[MembershipStatus]="Active","Active","Not Active")

....into a "Field:" cell in the design grid.



If doing this in SQL veiw, then put this...

Iif([TableQualifier].[MembershipStatus]="Active","Active","Not Active") as
MonthlyFeeStatus

....in the SELECT clause (make sure to separate columns/fields with commas in
the SELECT clause, but no comma after the last column).




NOTE: Be sure to change [TableQualifier] to the appropriate table name. I
didn't want to assume which table that field was in.

I haven't tested this, but I think it is right.

HTH,

Conan