# 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

welshgasman said:


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



		Code:
__


=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

welshgasman said:


> 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.
> 
> 
> 
> Code:
> __
> 
> 
> =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 



		VBA Code:
__


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

' 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
'***************************


----------

