Show Not Null Fields in one Field

[Njliven]

Below is a statement I have in a query. Basically I want to bring back the text of FP, NTE or EST if the respective fields are not null. The query runs but I get a -1 in the field.


Hope some one can help if this is possible. Thanks!

Type: ([Fixed Price] Is Not Null) & " FP " Or ([Not to Exceed] Is Not Null) & " NTE " Or ([Estimated Time Expenses] Is Not Null) & " EST "

[June7]

Try:

IIf(Not IsNull([Fixed Price]),"FP ", "") & IIf(Not IsNull([Not to Exceed]), "NTE ", "") & IIf(Not IsNull([Estimated Time Expenses]), "EST ", "")

[Njliven]

Works Sort of.... Sometimes it brings back the desired result, but mostly it brings back all three FP NTE & EST in the field. I am bringing back the actual fields separately to see what is happening. There are no spaces in the fields, they are really null as far as I can tell. There is only a value in one of the three fields, so why is it sometimes bringing back all three? Thanks!

[June7]

Are these text or Yes/No fields? If each record can have only one of these values then should be one field with 3 value choices.

[Njliven]

No, these are not yes no fields. They are text fields. They are either blank or have an X in them.

[June7]

If the alias text displays then the field is NOT null, something is in there, possibly an empty string, which appears to be nothing but isn't. Access defaults to allow empty strings when a field is created. I don't allow empty strings so I always change that property when creating new text field. The following will handle if field is Null or empty string.

IIf([Fixed Price] & ""<>"", "FP", "") & IIf([Not to Exceed] & ""<>"", "NTE", "") & IIf([Estimated Time Expenses] & ""<>"", "EST", "")

Why did you have spaces on each side of the aliases if you expect only one value to return?

Again, if each record should have value in only one of the 3 fields then should be only 1 field with 3 value choices (FP, NTE, EST) and Null or empty string would not be an issue and no need for this expression.

[Njliven]

That worked Thanks!