Solved Expression Not Work (1 Viewer)

[smtazulislam]

Here I using the module of
Original Developer: Michael Walsh
Calling: Call from Control Source of Text Box
Func : =IIf([BirthDate] Is Null,"",CalcAge([Birthdate],Date()))

It is work good. But problem is when employee is left then it is not calculated DateLeft

IIf([EmploymentDate] Is Null,"",CalcAge([EmploymentDate],Date()))

Hired Date & Left Date :
I tried two ways "=" and "<>"

IIf([StatusID]=InStr(2,3,4,5),"",CalcAge([EmploymentDate],[DateLeft]))
IIf([StatusID]<>InStr(2,3,4,5),"",CalcAge([EmploymentDate],[DateLeft]))

 

[arnelgp]

IIf([StatusID] In (2,3,4,5), "", CalcAge([EmploymentDate],[DateLeft]))

 

[smtazulislam]

IIf([StatusID] In (2,3,4,5), "", CalcAge([EmploymentDate],[DateLeft]))

Now does not have any error in expression, but not give result. return blank filed.

 

[June7]

I tested Arnel's expression and it works for me.

S, your InStr() doesn't work because the 2,3,4,5 is not a string and the InStr() syntax is wrong.

IIf(InStr("2,3,4,5", [StatusID])>0, "", CalcAge([EmploymentDate],[DateLeft]))

 

[smtazulislam]

I tested Arnel's expression and it works for me.

I dont know why here not work.

S, your InStr() doesn't work because the 2,3,4,5 is not a string and the InStr() syntax is wrong.

Oh, get it...

IIf(InStr("2,3,4,5", [StatusID])>0, "", CalcAge([EmploymentDate],[DateLeft]))

It is work now if replace ">0" to "<0"
Thank you very much Madam @June7

One more question. If any employee STILL work then how to calculate with same field in expression.
example :
Still work

="He served in Company " & IIf([EmploymentDate] Is Null Or InStr("1",[StatusID])>0,"",CalcAge([EmploymentDate],Date()))

Left form Work

="He served in Company " & IIf([DateLeft] Is Null Or InStr("2,3,4,5",[StatusID])<0,"",CalcAge([EmploymentDate],[DateLeft]))

I would like to display the result if employee is left then Calculate with DATELEFT otherwise calculate EMPLOYMENTDATE.

 

[XPS35]

Now does not have any error in expression, but not give result. return blank filed.

We do not see your data and we do not know how and where you are using the expression. So it is hard to tell what the problem is.

 

[June7]

InStr() will never return a value less than 0. It can return Null or =>0.

Maybe:

="He served in Company " & IIf(InStr("2,3,4,5",[StatusID])=0,"",CalcAge([Nz([DateLeft], [EmploymentDate])))

 

[smtazulislam]

We do not see your data and we do not know how and where you are using the expression. So it is hard to tell what the problem is.

Thanks your replay I using it on Form and Report.

 

[smtazulislam]

InStr() will never return a value less than 0. It can return Null or =>0.
Maybe:
="He served in Company " & IIf(InStr("2,3,4,5",[StatusID])=0,"",CalcAge([Nz([DateLeft], [EmploymentDate])))

Thanks for your reply.
here is not working... Somehwhere giving wrong calculate. Example the [EmployeeID=5] And if employee still work then there only display "He served in Company"

 

[June7]

Sorry, I should have looked more closely at the expression. And now that I know the function needs two date inputs, try:

="He served in Company " & IIf(InStr("2,3,4,5",[StatusID])=0,"",CalcAge(Nz([EmploymentDate],Date()), Nz(DateLeft,Date())))

 

[smtazulislam]

Sorry, I should have looked more closely at the expression. And now that I know the function needs two date inputs, try:

="He served in Company " & IIf(InStr("2,3,4,5",[StatusID])=0,"",CalcAge(Nz([EmploymentDate],Date()), Nz(DateLeft,Date())))

Thanks again.
It was given result as SAME only ONE side. If I enter the DateLeft then RESULT given, otherwise blank field.
Here need two result, Still work and Left work.

 

[June7]

It only calculates if StatusID is not 1. If you want calc for every record then remove that condition. The Nz() function deals with missing DateLeft.

="Served in Company " & CalcAge(Nz([EmploymentDate],Date()), Nz(DateLeft,Date()))

This means only records where neither field has data will not have a calc.

 

[smtazulislam]

It only calculates if StatusID is not 1. If you want calc for every record then remove that condition. The Nz() function deals with missing DateLeft.

="Served in Company " & CalcAge(Nz([EmploymentDate],Date()), Nz(DateLeft,Date()))

This means only records where neither field as data will not have a calc.

Great !. Its work now.
Appreciate & thanks one more for giving your valuable times.