Need Coding for the Excel formula in MS Access

[adnanmir]

=IF($D2="Not Required in Current FY","No Visit Required",
IF(AND($A2<>TODAY(),$B2=""),"Needs Scheduled",
IF(AND(ISNUMBER($B2),$C2=""),"Work Scheduled ",
IF($C2=$B2,"Completed",
IF($C2<=$B2,"Completed Earlier",
"Completed with Delay")))))

 

[welshgasman]

Write a function and use a Case statement. Pass in the controls/variable if not in the module that needs them.
If needed in more than one place, put into a stamdard module, else leave it in the form module, then you will not need to pass in the controls/variables

It will also be much much easier to read.

 

[adnanmir]

Write a function and use a Case statement. Pass in the controls/variable if not in the module that needs them.
If needed in more than one place, put into a stamdard module, else leave it in the form module, then you will not need to pass in the controls/variables

It will also be much much easier to read.

Thanks Welshgasman, for your guideline, the thing is I'm not good at coding at all that's why i requested this, in fact i m new to all these coding and formula development, took me too long to develop this formula of excel even .

Hence just to save my time i m requesting if you can help me write it so it can be a startup for me to know how it is written.

Thanks in advance and looking forward for your helping hand.

 

[welshgasman]

I generally do not write code for people, just because it takes me a while to write it, I am not a developer doing it day in, day out, just an amateur.
However also because people do not learn anything if it is handed to them on a plate, they just come back for more, like Oliver.

However to make the minimum changes.
Replace IF with IIF
Replace your cell references with your control/field values

I have made an attempt, where I used similar control names so you can see what goes where. Hopefully you will have more meaningful names for your controls.

=IIF(Me.D2 = "Not Required in Current FY","No Visit Required",
IIF((Me.A2 <> DATE() AND Me.B2 =""),"Needs Scheduled",
IIF(ISNUMERIC(Me.B2) AND Me.C2 =""),"Work Scheduled ",
IIF(Me.C2 = Me.B2,"Completed",
IIF(Me.C2 <= Me.B2,"Completed Earlier",
"Completed with Delay")))))

HTH

 

[Micron]

Never mind, I misunderstood the request.
I think I would use a Select Case block though, as you said.

 

[adnanmir]

I generally do not write code for people, just because it takes me a while to write it, I am not a developer doing it day in, day out, just an amateur.
However also because people do not learn anything if it is handed to them on a plate, they just come back for more, like Oliver.

However to make the minimum changes.
Replace IF with IIF
Replace your cell references with your control/field values

I have made an attempt, where I used similar control names so you can see what goes where. Hopefully you will have more meaningful names for your controls.

=IIF(Me.D2 = "Not Required in Current FY","No Visit Required",
IIF((Me.A2 <> DATE() AND Me.B2 =""),"Needs Scheduled",
IIF(ISNUMERIC(Me.B2) AND Me.C2 =""),"Work Scheduled ",
IIF(Me.C2 = Me.B2,"Completed",
IIF(Me.C2 <= Me.B2,"Completed Earlier",
"Completed with Delay")))))

HTH

Hi Wleshgasman, thank for your help i will try my best to see if I can make it work for me

 

[james_lankford]

maybe something like this

'***************************

' https://www.techonthenet.com/access/functions/advanced/isnumeric.php
' https://www.techonthenet.com/access/functions/date/date.php
' https://www.techonthenet.com/access/functions/index.php

'***************************
Sub test_doit()
    
    Dim field_A As Variant
    Dim field_B As Variant
    Dim field_C As Variant
    Dim field_D As Variant
    
    field_A = Date
    field_B = "23"
    field_C = "abc"
    field_D = "def"
    
    Dim retval As String
    retval = doit(field_A, field_B, field_C, field_D)
    Debug.Print (retval)
    MsgBox (retval)
    
End Sub
'***************************
Function doit(field_A, field_B, field_C, field_D)
    
    Dim s As String
    
    If field_D = "Not Required in Current FY" Then
        s = "No Visit Required"
    ElseIf field_A <> Date And field_B = "" Then
        s = "Needs Scheduled"
    ElseIf IsNumeric(field_B) And field_C = "" Then
        s = "Work Scheduled"
    ElseIf field_C = field_B Then
        s = "Completed"
    ElseIf field_C <= field_B Then
        s = "Completed Earlier"
    Else
        s = "Completed with Delay"
    End If
    
    doit = s
    
End Function
'***************************