iIF function (1 Viewer)

saravanan900 · Jun 5, 2018

Hi Good morning,
i need to convert the following Excel formula into MS access.

First one

=IF($J2="","",IF($L2<>"",IF(OR($K2=$L2,$M2>=7),"EXPIRED","RETURNED"),IF($K2-TODAY()>=1,$K2-TODAY(),"EXPIRED")))

second one
=IF(J2="","",IF(L2="","Open","Closed"))

J2 refers Received Date
L2 refers Actual Return Date
K2 refers Contract Return Date
M2 refers Actual Review Duration

Please help me.

bob fitz · Jun 5, 2018

Where in your db are intending to use these expressions

arnelgp · Jun 5, 2018

on the 1 expression, it is better to create a Public Function in a module
then set it as the Control Source of the textbox:

=fncResult([J2], [K2], [L2], [M2])

Code:

Public Function fncResult(j2, k2, l2, m2)
If (Trim(j2 & "") = "") Then
    If (Trim(l2 & "") <> "") Then
        If ((k2 & "") = (l2 & "")) Or (Val(m2 & "") >= 7) Then
            fncResult = "Expired"
        Else
            fncResult = "Returned"
        End If
    End If
Else
    If (k2 & "") <> "" Then
        If (k2 - Date) >= 1 Then
            fncResult = Date
        Else
            fncResult = "Expired"
        End If
    End If
End If
End Function

for the 2nd expression, add this as Control Source of the textbox:

Code:

=IIf(Trim([j2] & [l2] & "")="","Open","Closed")

Galaxiom · Jun 5, 2018

I have not looked closely at the logic required and trust that arnelgp has worked it out in the function.

However as a general principle, passing Variants to functions and subs is best avoided. All the parameters should be declared as Date. The ByVal declaration also makes it clear that the arguments passed are not going to be altered in the function. (By default parameters are ByRef and any changes within the function are passed back through the call.)

Code:

Public Function fncResult(ByVal j2 As Date, ByVal k2 As Date, ByVal l2 As Date, ByVal m2 As Date)

The logic where there are no dates to pass is best handled using IIF(), only passing to the function for processing when dates actually exist. If this is not manageable then pass a placeholder date which can be recognised by the function as such.

This is more efficient, less error prone and avoids the need for Variant parameters to handle any Nulls.

With functions it is sometimes a good idea to test the arguments are valid and in the range of plausibility, returning something recognisable as an error to the caller if falling outside of expectations. This also avoids the function breaking over and over again when use in a query that has fed it bad data.

BTW Use more meaningful names for your parameters too. Arnelgp has just used your cell names for simplicity.

saravanan900 · Jun 6, 2018

I need first formula in the attached file -Query-Incoming Correspondence-No. of Days Remaining and Second formula-Query-Incoming Correspondence-Open/Closed. Please help me to get this formula in the attached file

saravanan900 · Jun 6, 2018

arnelgp said:
on the 1 expression, it is better to create a Public Function in a module
then set it as the Control Source of the textbox:

=fncResult([J2], [K2], [L2], [M2])

Code:

Public Function fncResult(j2, k2, l2, m2) If (Trim(j2 & "") = "") Then If (Trim(l2 & "") <> "") Then If ((k2 & "") = (l2 & "")) Or (Val(m2 & "") >= 7) Then fncResult = "Expired" Else fncResult = "Returned" End If End If Else If (k2 & "") <> "" Then If (k2 - Date) >= 1 Then fncResult = Date Else fncResult = "Expired" End If End If End If End Function

for the 2nd expression, add this as Control Source of the textbox:

Code:

=IIf(Trim([j2] & [l2] & "")="","Open","Closed")

Thanks for your response.

I tried it but couldn't succeed. Please can you put it in the attached file-Query -Incoming Correspondence-First formula in the place of No. of Days Remaining and second formula in the place of Open/Closed .

arnelgp · Jun 6, 2018

Here i made 2 queries. Both have calculated column, Status.
for you to test.

saravanan900 · Jun 7, 2018

arnelgp said:
Here i made 2 queries. Both have calculated column, Status.
for you to test.

Hi Sir,
Thanks for your response.
But still not working. I have attached Excel for your reference.

arnelgp · Jun 7, 2018

Here i made the correxion. See also the form with conditional format.

saravanan900 · Jun 7, 2018

arnelgp said:
Here i made 2 queries. Both have calculated column, Status.
for you to test.

.

Thanks a lot Mr. Arnelgp.
It is working well.

Now i have attached Excel sheet for Outgoing Correspondence. Please make it.
Really thank you so much for your help.

saravanan900 · Jun 7, 2018

arnelgp said:
Here i made the correxion. See also the form with conditional format.

Thanks a lot Mr. Arnelgp.
It is working well.

Now i have attached Excel sheet for Outgoing Correspondence. Please make it.
Really thank you so much for your help.
Attached Files
File Type: xlsx Outgoing Letter Register.xlsx (32.0 KB, 0 views)

Minty · Jun 7, 2018

Please don't demand updates so quickly.
Everyone on here including Arne is an unpaid volunteer, and generally we assist you to solve problems and learn.

Arne appears to be off line, so unless you have tried something and failed, and can show us the problem, I doubt anyone else will pick this up.

arnelgp · Jun 7, 2018

Tnx ms.minty.
To the op, you owe me a doughnut.

saravanan900 · Jun 9, 2018

arnelgp said:
Tnx ms.minty.
To the op, you owe me a doughnut.

Thanks a lot Mr. Arnelgp. Really appreciate your help. It was very helpful.

.

saravanan900 · Jun 20, 2018

saravanan900 said:
Thanks a lot Mr. Arnelgp. Really appreciate your help. It was very helpful..

Good Morning Mr. Arnelgp,
Please help me to get the Report in Access. I have attached Access file and Excel sample. I tried but i am getting with full data. But i need a status A, B, W, I each in count only.

arnelgp · Jun 20, 2018

I need the technical subnissiom master register table. What you have is a link only.

saravanan900 · Jun 25, 2018

Good Morning,
Please help me to get the Report in Access. I have attached Access file and Excel sample. I tried but i am getting with full data. But i need a status A, B, W, I each in count only.

saravanan900 · Jun 25, 2018

arnelgp said:
I need the technical subnissiom master register table. What you have is a link only.

.

Good Morning Sir,
I have enclosed the file.

arnelgp · Jun 25, 2018

Use query1 to build your report.

saravanan900 · Jun 25, 2018

arnelgp said:
Use query1 to build your report.

Thank you very much for your help.
I need one more coloum for open items (Under Review) and total in that report. One more question about document type that if there is lot of document type i should add manually in Criteria In ("MS","MT","PL","RD").

iIF function (1 Viewer)

Registered User.

Attachments

AWF VIP

..forever waiting... waiting for jellybean!

Super Moderator

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Attachments

Registered User.

Attachments

AWF VIP

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

Attachments

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Attachments

Similar threads

Users who are viewing this thread