Access Expression for Calculated Field

Gilberto Sanches
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
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?
Gilberto SanchesFreelance ICT Consultant

Author

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.
Gilberto SanchesFreelance ICT Consultant

Author

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".
John TsioumprisSoftware & 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")

Open in new window

ste5anSenior 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>" ))))

Open in new window

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>" ))))

Open in new window

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>" ))))

Open in new window

Gilberto SanchesFreelance ICT Consultant

Author

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")

Open in new window

Gilberto SanchesFreelance ICT Consultant

Author

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."
ste5anSenior 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"

Open in new window

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"

Open in new window

instead?
Gilberto SanchesFreelance ICT Consultant

Author

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"
Gilberto SanchesFreelance ICT Consultant

Author

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"
ste5anSenior 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>" ))))

Open in new window

Gilberto SanchesFreelance ICT Consultant

Author

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?
ste5anSenior 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>" ))))

Open in new window

Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Okay, good to know Ste5an
Gilberto SanchesFreelance ICT Consultant

Author

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([

Open in new window


Is the formula too long?
Gilberto SanchesFreelance ICT Consultant

Author

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

Open in new window

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.
Gilberto SanchesFreelance ICT Consultant

Author

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;

Open in new window

ste5anSenior Developer

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

Author

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;

Open in new window

Gilberto SanchesFreelance ICT Consultant

Author

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" ?
ste5anSenior Developer

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

Author

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

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

Author

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
Gilberto SanchesFreelance ICT Consultant

Author

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

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

Start 7-Day Free Trial