Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trying to convert Excel 2007 formula to Access 2007 2

Status
Not open for further replies.

mtdew

Technical User
Dec 9, 2007
77
US
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!
 
I think this might work:
Code:
=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
 
... 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.
 



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

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



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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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.
 
I get invalid outside procedure error messages
 
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
 
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"?

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top