iIF function

[saravanan900]

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]

Where in your db are intending to use these expressions

 

[arnelgp]

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

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:

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

 

[Galaxiom]

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

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]

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]

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

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:

=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]

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

 

[saravanan900]

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]

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

 

[saravanan900]

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]

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]

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]

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

 

[saravanan900]

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]

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]

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

 

[saravanan900]

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]

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

.

Good Morning Sir,
I have enclosed the file.

 

[arnelgp]

Use query1 to build your report.

 

[saravanan900]

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