Simplest conversion of Excel nested 'If' statements to Access VBA

[karmacable]

Hi Guys,



I have formulas written in Excel cells that are working just great, and was wondering if there was a quick 'conversion' method I could use into my Access VBA code (I'm planning on putting these in the 'AfterUpdate()' control of my textboxes).

Here's one such code;

=IF(D24=0,"",IF(B25<>0,C24-D24*7,IF(B26<>0,B26-D24*7,IF(B27<>0,B27-D24*7,""))))

I've already setup textbox names to match the cell references (as variables of course), and was wondering if this could be dragged/dropped into VBA, and some plugin/tool would convert the logic? If not, what's the best way to re-construct these nested 'If' statements (e.g. If / EndIf, If / Else, Select, etc.)?

In case you're wondering why the *7 in the formula, this a date calculation formula for weeks. Should I account for that as well in the textboxes, as = 0 might work totally different for a date than a number?

Thanks and let me know if I should provide more detail.

[ajetrumpet]

i doubt there's a formula, or a plugin tool.

however, the easiest coding way to do this would be to simply be to split your cell strings and parse them out using functions like:

mid()
instr()
right()
left()
split()

then after that of course, you'd have to paste the results behind your controls manually. That has no conversion obviously, because controls don't exist automatically in access. thus, there would be no market value for such a thing.

[karmacable]

Thanks for the suggestions, I'll check them out.

I've gone ahead and converted to nested VBA If / ElseIf statements (something I haven't had much practice with), and am currently getting a 'End If without Block If' error. My nest is much bigger, but here's a small part of the code where the error begins;

 
If Me.txt1stPrepLocStart <> 0 Then
Me.txt1stPrepPaceStart = Me.txt1stPrepLocStart - PaceWeeks1
ElseIf Me.txt1stShootStart <> 0 Then
Me.txt1stPrepPaceStart = Me.txt1stShootStart - PaceWeeks1
Else
Me.txt1stPrepPaceStart = ""
End If
End If

I thought I had the correct matches as I see two Ifs and two EndIf's, but please enlighten me to the obvious mistake I'm not seeing.

Thanks!

[karmacable]

Never mind, I just discovered that 'Else If' statement only require a single 'End If'. So I changed my code to this and now it works (for anyone else new to nested if / else to learn);

 
If Me.txt1stPrepLocStart <> 0 Then
       Me.txt1stPrepPaceStart = Me.txt1stPrepLocStart - PaceWeeks1
   ElseIf Me.txt1stShootStart <> 0 Then
       Me.txt1stPrepPaceStart = Me.txt1stShootStart - PaceWeeks1
   Else
       Me.txt1stPrepPaceStart = ""
End If