# Access Expression for Calculated Field

Gilberto Sanches used Ask the Experts™
on
Hi Experts,

Your help is appreciated with a solution to the following situation.

We have about 6 fields.
datum_M, SBB_label, Rejectlabel, KAPnr, datum_TS, datum_VVB

What I would like to achieve is a calculated column with the following in a expression:
If [datum_M] is empty show "1. In Production"
If [KAPnr] empty but not [SBB_Label] show "2. Ready for KAP"
If [SBB_Label] & [KAPnr] or [Rejectlabel] not empty show "3. Ready for Trucking"
If [datum_TS] or [datum_VVB] not empty show "4. Transported"

So far I came to ( & it works) :
IIf(IsNull([datum_M]),"1. In Production",IIf(IsNull([SBB_Label]),"2. Ready For Kap",IIf(IsNull([datum_TS] And [datum_VVB]),"3. To Be Transported","4. Transported"))).

The goal is not yet achieved. That's where I need you guys, how would the expression for this look like?
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
1. Are you sure the "empty" fields are Null and that none are ZLS?
2.#2 is ambiguous.  Are you saying that [SBB_Label] should NOT be null
3. #3 is also ambiguous  What are you testing the first two fields for?  And is the condition - ((a and b) or c)  OR is it (a and (b or c))?
You need a 5th condition.  Since the first four are not mutually exclusive, what happens if none of the four conditions are true?
Freelance ICT Consultant

Commented:
Hey PatHartman, thanks for the quick response.

1. Yeah, empty fields are Null.
2. Yes,  [SBB_Label] not null,
3. Your question got me thinking, [SBB_Label] can be left out. So it is a A or B condition. Like in condition 2.
The condition becomes : If [KAPnr] or [Rejectlabel] not empty show "3. Ready for Trucking"

The 5th condition is the first condition. If the last 4 criteria's are not met, it's condition 1.
Distinguished Expert 2017

Commented:
This may do it.  It's hard to get the parentheses correct without a compiler to help.  I also removed all the square brackets since none of the fields needed them and they blind me when I am trying to count parens.

IIf(IsNull(KAPnr) AND NOT IsNull(SBB_Label),"2. Ready For Kap", IIf(IsNull(KAPnr) AND NOT IsNull(RejectLabel),"3. To Be Transported", IIf(IsNull(datum_TS) And Not IsNull(datum_VVB),"4. Transported","1. In Production")))

Notice that I moved the first condition to the end and made it the default so no IIf() is needed for it.

Questions 2 and 3 may both be true.  If so, #2 will be chosen since that test is first.
Freelance ICT Consultant

Commented:
Thanks PatHartman, it works except for the following cases:
text # 2. Rows that have rejects labels get result "2. Ready for Kap" instead of "To Be transported".
text "4. Transported", works for field datum_VVB. But for datum_TS it puts "1. In Production" instead of also "4. Transported"
Text "1. Production" is also shown for condition 2 instead of "3. To Be Transported".
Software & Systems Engineer

Commented:
``````iif(Len(Nz( [datum_M],""))=0,"1. In Production")
iif(Len(Nz( [KAPnr],""))=0m"2. Ready for KAP")
iif Len(Nz([SBB_Label],"")+Len(Nz( [KAPnr],"")+Len(Nz( [Rejectlabel],"")=0,"3. Ready for Trucking")
iif(Len(Nz([datum_TS],""))+Len(Nz( [datum_VVB],"")))>0,"4. Transported")
``````
Senior Developer

Commented:
I would write it as either as

``````Iif ( Len([datum_M] & "") = 0, "1. In Production",
Iif ( Len([KAPnr] & "") = 0 And Len([SBB_Label] & "") > 0, "2. Ready for KAP",
Iif ( Len([SBB_Label] & "") > 0 And Len([KAPnr] & "") > 0 Or Len([Rejectlabel] & "") > 0, "3. Ready for Trucking",
Iif ( Len([datum_TS] & [datum_VVB] & "") > 0, "4. Transported",
"<N/A>" ))))
``````
without knowing the data types. Otherwise when the data types and properties of the column are correctly set, like disabled ZLS, correctly set mandatory input:

``````Iif ( Not IsNull([datum_M], "1. In Production",
Iif ( IsNull([KAPnr]) And Not IsNull([SBB_Label]), "2. Ready for KAP",
Iif ( Not IsNull([SBB_Label]) And Not IsNull([KAPnr]) Or Not IsNull([Rejectlabel]), "3. Ready for Trucking",
Iif ( Not IsNull([datum_TS]) Or Not IsNull([datum_VVB]), "4. Transported",
"<N/A>" ))))
``````
This could be a little bit simplified to:

``````Iif ( Not IsNull([datum_M], "1. In Production",
Iif ( IsNull([KAPnr]) And Not IsNull([SBB_Label]), "2. Ready for KAP",
Iif ( Not IsNull(Nz([SBB_Label], [KAPnr]) Or Not IsNull([Rejectlabel]), "3. Ready for Trucking",
Iif ( Not IsNull(Nz([datum_TS], [datum_VVB]), "4. Transported",
"<N/A>" ))))
``````
Freelance ICT Consultant

Commented:
@ John,

I get the error "The expression you entered contains invalid syntax. You may have entered an operator, such as the + operator, in an expression without a corresponding operand."

I thought it was a typo, so I also correct before text 2, 0m to 0,"
``````iif(Len(Nz( [datum_M],""))=0,"1. In Production")
iif(Len(Nz( [KAPnr],""))=0,"2. Ready for KAP")
iif Len(Nz([SBB_Label],"")+Len(Nz( [KAPnr],"")+Len(Nz( [Rejectlabel],"")=0,"3. Ready for Trucking")
iif(Len(Nz([datum_TS],""))+Len(Nz( [datum_VVB],"")))>0,"4. Transported")
``````
Freelance ICT Consultant

Commented:
@ste5an

expression:

1) Text 1 works good.
Text 2 is shown but it also includes rejectlabels, which should be for text 3.
Text 3 is shown but it also includes datum_VVB en datum_TS which should be for text 4.
Text 4 isn't shown.

2 & 3)  gives error "The expression you entered has a function containing the wrong numbers of arguments."
Senior Developer

Commented:
Post a  concise and complete sample.

``````If [KAPnr] empty but not [SBB_Label] show "2. Ready for KAP"
If [SBB_Label] & [KAPnr] or [Rejectlabel] not empty show "3. Ready for Trucking"
``````
Your requirements are interdependend, thus it's a data problem.

Or are your requirements incorrect? Do you mean
``````If [KAPnr] empty and not [SBB_Label] empty and  [Rejectlabel] empty  show "2. Ready for KAP"
If [SBB_Label] & [KAPnr] or [Rejectlabel] not empty show "3. Ready for Trucking"
``````
Freelance ICT Consultant

Commented:
@ste5an
The requirenments are:

If [KAPnr] empty and not [SBB_Label] empty show "2. Ready for KAP"
If [SBB_Label] & [KAPnr] not empty or [KAPnr] & [Reject_Label] not empty show "3. Ready for Trucking"
If [datum_TS] or [datum_VVB] not empty show "4. Transported"
Freelance ICT Consultant

Commented:
@ste5an
Sorry, a correction in the requirenments:

If [KAPnr] empty and not [SBB_Label] empty show "2. Ready for KAP"
If [SBB_Label] & [KAPnr] not empty or [KAPnr] empty & [Reject_Label] not empty show "3. Ready for Trucking"
If [datum_TS] or [datum_VVB] not empty show "4. Transported"
Senior Developer

Commented:
E.g.

``````Iif (       Len([datum_M] & "") = 0,                                        "1. In Production",
Iif (     Len([KAPnr] & "") = 0 And Len([SBB_Label] & "") > 0,            "2. Ready for KAP",
Iif (   Len([KAPnr] & "") > 0 And Len([SBB_Label] & "") > 0
Or Len([KAPnr] & "") = 0 And Len([Rejectlabel] & "") > 0,   "3. Ready for Trucking",
Iif ( Len([datum_TS] & [datum_VVB] & "") > 0,                         "4. Transported",
"<N/A>" ))))
``````
Freelance ICT Consultant

Commented:
@ Ste5an, results didn't changed much like before. Text 4 isn't shown. Text 2 & 3 still have info from other columns included.

Can you explain the Expression so I can help with thinking?
Senior Developer

Commented:
It's pretty simple:

[columnName] & "" will take any column, regardless of the type, and add an empty string (ZLS, zero length string) to it. Using the ampersand operator ensures, that if [columnName] is NULL, the result is an empty string.
Thus Len([columnName] & "") > 0 means the column has content. Len([columnName] & "") = 0 means the column is either NULL or contains an empty string.

results didn't changed much like before. Text 4 isn't shown. Text 2 & 3 still have info from other columns included.
Well, are these conditions mutually exclusive? In this case the order of the nested Iif() does not matter, otherwise you need to reorder them. E.g. you may have not explained all facts, thus it is maybe:

``````Iif (       Len([datum_TS] & [datum_VVB] & "") > 0,                         "4. Transported",
Iif (     Len([KAPnr] & "") > 0 And Len([SBB_Label] & "") > 0
Or Len([KAPnr] & "") = 0 And Len([Rejectlabel] & "") > 0,   "3. Ready for Trucking",
Iif (   Len([KAPnr] & "") = 0 And Len([SBB_Label] & "") > 0,            "2. Ready for KAP",
Iif ( Len([datum_M] & "") = 0,                                        "1. In Production",
"<N/A>" ))))
``````
Freelance ICT Consultant

Commented:
Okay, good to know Ste5an
Freelance ICT Consultant

Commented:
just saw in the Access expression builder that it only takes until text 3

``````IIf(Len([datum_M] & "")=0,"1. In Production",IIf(Len([KAPnr] & "")=0 And Len([SBB_Label] & "")>0,"2. Ready for KAP",IIf(Len([KAPnr] & "")>0 And Len([SBB_Label] & "")>0 Or Len([KAPnr] & "")=0 And Len([Reject_Label] & "")>0,"3. Ready for Trucking",IIf(Len([
``````

Is the formula too long?
Freelance ICT Consultant

Commented:
What do you think of splitting parts of the formula into multiple columns?
Senior Developer
Commented:
Where exactly do you need this?

If it's in the data source, thus the query, then add it in the SQL statement directly:

``````SELECT  *,
Iif (       Len([datum_TS] & [datum_VVB] & "") > 0,                         "4. Transported",
Iif (     Len([KAPnr] & "") > 0 And Len([SBB_Label] & "") > 0
Or Len([KAPnr] & "") = 0 And Len([Rejectlabel] & "") > 0,   "3. Ready for Trucking",
Iif (   Len([KAPnr] & "") = 0 And Len([SBB_Label] & "") > 0,            "2. Ready for KAP",
Iif ( Len([datum_M] & "") = 0,                                        "1. In Production",
"<N/A>" )))) AS StatusText
FROM yourTable
``````
Otherwise, when you do a copy'n'paste, paste it to notepad first. sometimes hidden characters copied from a web page can break inserting.

EDIT: added missing comma.
Freelance ICT Consultant

Commented:
@ Ste5an, yes in the data source. Alright, added in the SQL statement and get the following error "Syntax error (missing operator) in query expression '*

``````SELECT  *
Iif (       Len([datum_TS] & [datum_VVB] & "") > 0,                         "4. Transported",
Iif (     Len([KAPnr] & "") > 0 And Len([SBB_Label] & "") > 0
Or Len([KAPnr] & "") = 0 And Len([Rejectlabel] & "") > 0,   "3. Ready for Trucking",
Iif (   Len([KAPnr] & "") = 0 And Len([SBB_Label] & "") > 0,            "2. Ready for KAP",
Iif ( Len([datum_M] & "") = 0,                                        "1. In Production",
"<N/A>" )))) AS StatusText
FROM Logging_Register;
``````
Senior Developer

Commented:
Typo. Would not have happened, would you have posted a concise and complete example..
Freelance ICT Consultant

Commented:
I corrected the code and it works. Am now checking the results.

``````SELECT
Iif (       Len([datum_TS] & [datum_VVB] & "") > 0,                         "4. Transported",
Iif (     Len([KAPnr] & "") > 0 And Len([SBB_Label] & "") > 0
Or Len([KAPnr] & "") = 0 And Len([Reject_label] & "") > 0,   "3. Ready for Trucking",
Iif (   Len([KAPnr] & "") = 0 And Len([SBB_Label] & "") > 0,            "2. Ready for KAP",
Iif ( Len([datum_M] & "") = 0,                                        "1. In Production",
"<N/A>" )))) AS StatusText ,*
FROM Logging_Register;
``````
Freelance ICT Consultant

Commented:
Hey Ste5an, the SQL code works like a charm. The column that shows the results is named to "StatusText" how can I rename it to "StockStatus" ?
Senior Developer

Commented:
Change the column alias after the AS keyword.
Freelance ICT Consultant

Commented:
How do I change the column alias after the AS keyword ?
Senior Developer

Commented:
By changing the text in the SQL editor?
Or in the designer, the name before the colon.
Freelance ICT Consultant

Commented:
Hahah, I changed it before but I didn't saw it. The name change replaced the location of the column, but I found it.
Thank you very much Ste5an. It works :D
Freelance ICT Consultant

Commented:
Thanks again Experts
Distinguished Expert 2017

Commented:
text # 2. Rows that have rejects labels get result "2. Ready for Kap" instead of "To Be transported".
I did warn you that would happen but I was busy yesterday and not following the thread.

It looks like ste5an finally got the specs sufficiently straight to work out a solution for you.  We can't write logic for you when we do not have all the facts.  When you omit conditions we can only go by what you say.

Do more with

Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.