It helps me from time to time (1 Viewer)

Lutful

New member
Local time
Today, 11:45
Joined
May 9, 2018
Messages
4
I am from Dhaka, Bangladesh.
I do use MS Assess in my office works. To solve my queries, Make form, Reports, I take help from this Center.
Today I became member.
Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:45
Joined
May 7, 2009
Messages
19,169
Welcome to awf!
 

Lutful

New member
Local time
Today, 11:45
Joined
May 9, 2018
Messages
4
I want an expression which will return "OK OK OK" if three fields are not empty

I want an expression which will return "OK OK OK" if three fields are not empty.
If any one field is empty,two others have value then it will appear Pending, OK, OK
If two fields are empty it will appear Pending-Pending-OK
If three fields are empty it will appear Pending-Pending-Pending.

I could make half and got result:
DOX STATUS: IIf(IsNull([IP Request Receive Date]),IIf(IsNull([Authority Letter Receive Date]),IIf(IsNull([Duty Exemption Letter Receive Date]),"Pending","OK OK OK")))

Can you help me?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:45
Joined
May 7, 2009
Messages
19,169
It would require many Iifs on the query. Why dont you call a function instead. Out in a module:

Public function fnStatus(f1 as variant, f2 as variant, f3 as variant) as string
Dim r1 as byte, r2 as byte, r3 as byte, r as byte
If not isnull(f1) then
r1=1
End if
If not isnull(f2) then
r2=1
End if
If not isnull(f3) then
r3=1
End if
r=r1+r2+r3+1
fnStatus=choose(r, "pending-pending-pending","pending-pending-ok","pending-ok-ok", "ok-ok-ok")
End function




On your query:

DOX STATUS: fnStatus([IP Request Receive Date], [Authority Letter Receive Date], [Duty Exemption Letter Receive Date])
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:45
Joined
May 7, 2009
Messages
19,169
or something like this to show the correct position of the Status with respect to the fields:
Code:
Public function fnStatus(f1 as variant, f2 as variant, f3 as variant) as string

	Dim s1 as string, s2 as string, s3 as string
	s1="Ok, "
	s2="Ok, "
	s3="Ok"

	If not isnull(f1) then

		s1="Pending, "

	End if

	If isnull(f2) then
		s2="Pending, "
	End if

	
If  isnull(f3) then
		s3="Pending"
	End if

	fnStatus= s1 & s2 & s3
End Function
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 21:45
Joined
Aug 30, 2003
Messages
36,118
FYI I moved this out of the introductions forum.
 

timpage

New member
Local time
Yesterday, 21:45
Joined
May 10, 2018
Messages
1
Hello

In this grammar lesson, I look at the difference between in time and on time. ... time


________________________
 

Lutful

New member
Local time
Today, 11:45
Joined
May 9, 2018
Messages
4
Dear Arnelgp,
Thanks a lot for solution.
Lutful
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:45
Joined
May 7, 2009
Messages
19,169
You're welcone Lut.
 

Lutful

New member
Local time
Today, 11:45
Joined
May 9, 2018
Messages
4
Dear Arnlegp,
I was trying to use in a query because with other fields, I was liking to get return.
However, I will do by it now.
Regards,
Lutful
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:45
Joined
May 7, 2009
Messages
19,169
Ok Lutful, ill wait if it worked for your query.
 

Users who are viewing this thread

Top Bottom