Trying to convert Excel 2007 formula to Access 2007 2

[mtdew]

This is how it looks in Excel, how do I need to change it for Access?

=IF(MONTH(A2)+1=13,IF(OR(ISBLANK(E2),E2=0),"1st Pay N/A",IF($C2<68,(MONTH(A2)+1)-12&"/1 and newer",MONTH(A2)&"/1 and older")),(IF(OR(ISBLANK(E2),E2=0),"1st Pay N/A",IF($C2<68,MONTH(A2)+1&"/1 and newer",MONTH(A2)&"/1 and older"))))

In Access A2 is now ReportDate, C2 is now AgeDays and E2 is now 1stPmtCat.

Thanks!

 

[dhookom]

I think this might work:

=IIF(MONTH(ReportDate)=12,IIF(Nz([1stPmtCat],0)=0,"1st Pay N/A",IIF(AgeDays<68,(MONTH(ReportDate)+1)-12 & "/1 and newer",MONTH(ReportDate) & "/1 and older")),IIF(Nz([1stPmtCat],0)=0,"1st Pay N/A",IIF(AgeDays<68,MONTH(ReportDate)+1 & "/1 and newer", MONTH(ReportDate) & "/1 and older"))))

Anytime you get something this messy, I would write a user-defined function that would accept the arguements and pass back the value (even in Excel). This is probably a business rule that might change and is possibly used throughout your application. I wouldn't waste my time building or maintaining it as an expression.

Duane
Hook'D on Access
MS Access MVP

 

[lionelhill]

... dhookom, thanks, it never occurred to me to do that in Excel, but in Access I've always felt strongly that vba is a better place for long and complex things full of iifs. They're a nightmare to read and debug.

 

[SkipVought]

Amen! I often code functions to use directly in my sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Code allows for commenting, error handling, re-usability, standardization, and greater functionality.

Now, if I had 10% of Skip's Excel knowledge ;-)

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

I stand on the shoulders of others and I share as liberally as I am able. It is the only 'liberal' that I allow myself. ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mtdew]

Thanks for getting me started. The following works if fist_pmt_effective is blank but otherwise I get an error:

1stPmtCat: IIf(Month([ReportDate])=12,IIf(Nz([first_pmt_effective],0)=0,"1st Pay N/A",IIf([AgeDays]<68,(Month([ReportDate])+1)-12 & "/1 and newer",Month([ReportDate]) & "/1 and older")),IIf(Nz([first_pmt_effective],0)=0,"1st Pay N/A",IIf([AgeDays]<68,Month([ReportDate])+1 & "/1 and newer",Month([ReportDate]) & "/1 and older")))

The UDF is a good idea.

 

[dhookom]

Create a user-defined function and save it in a module named "modBusinessCalcs".

Duane
Hook'D on Access
MS Access MVP

 

[mtdew]

I get invalid outside procedure error messages

 

[dhookom]

I can't see what you did.

Duane
Hook'D on Access
MS Access MVP

 

[mtdew]

Alt-F11
Insert
Module
Option Compare Database
1stPmtCat= IIf(Month([ReportDate])=12,IIf(Nz([first_pmt_effective],0)=0,"1st Pay N/A",IIf([AgeDays]<68,(Month([ReportDate])+1)-12 & "/1 and newer",Month([ReportDate]) & "/1 and older")),IIf(Nz([first_pmt_effective],0)=0,"1st Pay N/A",IIf([AgeDays]<68,Month([ReportDate])+1 & "/1 and newer",Month([ReportDate]) & "/1 and older")))
File
Save As
modBusinessCalcs
Error message invalid outside procedure

 

[dhookom]

Your code should. E something like

Public function YourFunctionName(arg1 as string, ar2 as date,...) as string
   'your code here
End function

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Even for a nested iif there is some serious Rude Goldberg logic here.
If I read this correctly regardless of the month if the first pmt effective is 0 then return "Ist Pay N/A", but you have this checked twice.
Then if the month is 12 and days less than 68 you retrun
(Month([ReportDate])+1)-12 & "/1 and newer",
so that is 12 + 1 -12 & "/1 and newer"
isn't that always "1/1 and newer"?

public function someFunction(Reportdate as variant, firstPmt as variant, ageDays as variant)
  dim rtn as string
  dim intMonth as integer
  intmonth = nz(reportDate,month(date)) 
  firstPmt = nz(firstPmt,0)
  ageDays = nz(ageDays,0)

  if firstPmt = 0 then
     rtn = "1st Pay N/A"
  elseif intMonth = 12 then
     if ageDays < 68 then
       rtn = "1/1 and newer" 
     else
       rtn = "12/1 and older"
     end if
  else
     if ageDays < 68 then
       rtn = intMonth + 1 & "/1 and newer"
     else
      rtn = intMonth & "/1 and older"
     end if 
  end if

  someFunction = rtn
end if