urjudo
asked on
Coding problem
Hi Experts,
I have a coding below, how do I make it more simple, I know " If Me.CPDOMVIOL = True Then" are twice,
If IsNull(CPAgency) Then
If Me.CPDOMVIOL = True Then
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = ""
AddrLine3 = ""
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = Format(Me.CPADD, ">")
AddrLine3 = Format(Me.CPCitySt, ">")
AddrLine4 = ""
End If
Else
AddrLine1 = Format(Me.AGYName, ">")
AddrLine2 = Format(Me.AGYADDR, ">")
AddrLine3 = Format(Me.AGYCitySt, ">")
AddrLine4 = ""
End If
Else
If IsNull(CPATTYCODE) Or CPATYLAST = "UNKNOWN" Or CPATYLAST = "UNAVAILABLE" Then
If Me.CPDOMVIOL = True Then
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = ""
AddrLine3 = ""
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = Format(Me.CPADD, ">")
AddrLine3 = Format(Me.CPCitySt, ">")
AddrLine4 = ""
End If
Else
If IsNull(CPATTYName) Then
If Not IsNull(CPATYFIRM) Then
AddrLine1 = Format(Me.CPATYFIRM, ">")
AddrLine2 = Format(Me.CPATTYAdd, ">")
AddrLine3 = Format(Me.CPAttyCitySt, ">")
AddrLine4 = ""
End If
Else
If IsNull(CPATYFIRM) Then
AddrLine1 = Format(Me.CPATTYName, ">")
AddrLine2 = Format(Me.CPATTYAdd, ">")
AddrLine3 = Format(Me.CPAttyCitySt, ">")
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPATTYName, ">")
AddrLine2 = Format(Me.CPATYFIRM, ">")
AddrLine3 = Format(Me.CPATTYAdd, ">")
AddrLine4 = Format(Me.CPAttyCitySt, ">")
End If
End If
End If
I tried this, but it does not work
If IsNull(CPAgency) Or IsNull(CPATTYCODE) Or CPATYLAST = "UNKNOWN" Or CPATYLAST = "UNAVAILABLE" Then
If Me.CPDOMVIOL = True Then
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = ""
AddrLine3 = ""
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = Format(Me.CPADD, ">")
AddrLine3 = Format(Me.CPCitySt, ">")
AddrLine4 = ""
End If
Else
If Not IsNull(CPAgency) Then
AddrLine1 = Format(Me.AGYName, ">")
AddrLine2 = Format(Me.AGYADDR, ">")
AddrLine3 = Format(Me.AGYCitySt, ">")
AddrLine4 = ""
Else
If Not IsNull(CPATTYCODE) Then
If IsNull(CPATTYName) Then
If Not IsNull(CPATYFIRM) Then
AddrLine1 = Format(Me.CPATYFIRM, ">")
AddrLine2 = Format(Me.CPATTYAdd, ">")
AddrLine3 = Format(Me.CPAttyCitySt, ">")
AddrLine4 = ""
End If
Else
If IsNull(CPATYFIRM) Then
AddrLine1 = Format(Me.CPATTYName, ">")
AddrLine2 = Format(Me.CPATTYAdd, ">")
AddrLine3 = Format(Me.CPAttyCitySt, ">")
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPATTYName, ">")
AddrLine2 = Format(Me.CPATYFIRM, ">")
AddrLine3 = Format(Me.CPATTYAdd, ">")
AddrLine4 = Format(Me.CPAttyCitySt, ">")
End If
End If
End If
End If
End If
did I do anything wrong?
Thanks
I have a coding below, how do I make it more simple, I know " If Me.CPDOMVIOL = True Then" are twice,
If IsNull(CPAgency) Then
If Me.CPDOMVIOL = True Then
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = ""
AddrLine3 = ""
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = Format(Me.CPADD, ">")
AddrLine3 = Format(Me.CPCitySt, ">")
AddrLine4 = ""
End If
Else
AddrLine1 = Format(Me.AGYName, ">")
AddrLine2 = Format(Me.AGYADDR, ">")
AddrLine3 = Format(Me.AGYCitySt, ">")
AddrLine4 = ""
End If
Else
If IsNull(CPATTYCODE) Or CPATYLAST = "UNKNOWN" Or CPATYLAST = "UNAVAILABLE" Then
If Me.CPDOMVIOL = True Then
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = ""
AddrLine3 = ""
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = Format(Me.CPADD, ">")
AddrLine3 = Format(Me.CPCitySt, ">")
AddrLine4 = ""
End If
Else
If IsNull(CPATTYName) Then
If Not IsNull(CPATYFIRM) Then
AddrLine1 = Format(Me.CPATYFIRM, ">")
AddrLine2 = Format(Me.CPATTYAdd, ">")
AddrLine3 = Format(Me.CPAttyCitySt, ">")
AddrLine4 = ""
End If
Else
If IsNull(CPATYFIRM) Then
AddrLine1 = Format(Me.CPATTYName, ">")
AddrLine2 = Format(Me.CPATTYAdd, ">")
AddrLine3 = Format(Me.CPAttyCitySt, ">")
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPATTYName, ">")
AddrLine2 = Format(Me.CPATYFIRM, ">")
AddrLine3 = Format(Me.CPATTYAdd, ">")
AddrLine4 = Format(Me.CPAttyCitySt, ">")
End If
End If
End If
I tried this, but it does not work
If IsNull(CPAgency) Or IsNull(CPATTYCODE) Or CPATYLAST = "UNKNOWN" Or CPATYLAST = "UNAVAILABLE" Then
If Me.CPDOMVIOL = True Then
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = ""
AddrLine3 = ""
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPNAME, ">")
AddrLine2 = Format(Me.CPADD, ">")
AddrLine3 = Format(Me.CPCitySt, ">")
AddrLine4 = ""
End If
Else
If Not IsNull(CPAgency) Then
AddrLine1 = Format(Me.AGYName, ">")
AddrLine2 = Format(Me.AGYADDR, ">")
AddrLine3 = Format(Me.AGYCitySt, ">")
AddrLine4 = ""
Else
If Not IsNull(CPATTYCODE) Then
If IsNull(CPATTYName) Then
If Not IsNull(CPATYFIRM) Then
AddrLine1 = Format(Me.CPATYFIRM, ">")
AddrLine2 = Format(Me.CPATTYAdd, ">")
AddrLine3 = Format(Me.CPAttyCitySt, ">")
AddrLine4 = ""
End If
Else
If IsNull(CPATYFIRM) Then
AddrLine1 = Format(Me.CPATTYName, ">")
AddrLine2 = Format(Me.CPATTYAdd, ">")
AddrLine3 = Format(Me.CPAttyCitySt, ">")
AddrLine4 = ""
Else
AddrLine1 = Format(Me.CPATTYName, ">")
AddrLine2 = Format(Me.CPATYFIRM, ">")
AddrLine3 = Format(Me.CPATTYAdd, ">")
AddrLine4 = Format(Me.CPAttyCitySt, ">")
End If
End If
End If
End If
End If
did I do anything wrong?
Thanks
ASKER
I changed to this, but it only take the last Else section and the "If IsNull and If Not IsNull" those two parts the system skips.
If IsNull(CPATTYCODE) Or ATYLAST = "UNKNOWN" Or ATYLAST = "UNAVAILABLE" Then
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
AddrLine1 = Format(Me.ATYFIRM, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATTYAdd, ">")
AddrLine4 = Format(Me.AttyCitySt, ">")
AddrLine5 = ""
Else
If Not IsNull(ATTYNAME) And IsNull(ATYFIRM) Then
AddrLine1 = Format(Me.ATTYNAME, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATTYAdd, ">")
AddrLine4 = Format(Me.AttyCitySt, ">")
AddrLine5 = ""
End If
End If
Else
AddrLine1 = Format(Me.ATTYNAME, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATYFIRM, ">")
AddrLine4 = Format(Me.ATTYAdd, ">")
AddrLine5 = Format(Me.AttyCitySt, ">")
End If
If IsNull(CPATTYCODE) Or ATYLAST = "UNKNOWN" Or ATYLAST = "UNAVAILABLE" Then
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
AddrLine1 = Format(Me.ATYFIRM, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATTYAdd, ">")
AddrLine4 = Format(Me.AttyCitySt, ">")
AddrLine5 = ""
Else
If Not IsNull(ATTYNAME) And IsNull(ATYFIRM) Then
AddrLine1 = Format(Me.ATTYNAME, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATTYAdd, ">")
AddrLine4 = Format(Me.AttyCitySt, ">")
AddrLine5 = ""
End If
End If
Else
AddrLine1 = Format(Me.ATTYNAME, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATYFIRM, ">")
AddrLine4 = Format(Me.ATTYAdd, ">")
AddrLine5 = Format(Me.AttyCitySt, ">")
End If
ASKER
I found out that the problem is in this statement,
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
AddrLine1 = Format(Me.ATYFIRM, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATTYAdd, ">")
AddrLine4 = Format(Me.AttyCitySt, ">")
AddrLine5 = ""
it seems the system does not like the statement above and it went to the statement below
AddrLine1 = Format(Me.ATTYNAME, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATYFIRM, ">")
AddrLine4 = Format(Me.ATTYAdd, ">")
AddrLine5 = Format(Me.AttyCitySt, ">")
I can't figure out why.
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
AddrLine1 = Format(Me.ATYFIRM, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATTYAdd, ">")
AddrLine4 = Format(Me.AttyCitySt, ">")
AddrLine5 = ""
it seems the system does not like the statement above and it went to the statement below
AddrLine1 = Format(Me.ATTYNAME, ">")
AddrLine2 = "ATTORNEY FOR RESPONDENT"
AddrLine3 = Format(Me.ATYFIRM, ">")
AddrLine4 = Format(Me.ATTYAdd, ">")
AddrLine5 = Format(Me.AttyCitySt, ">")
I can't figure out why.
Still too much I do not know and must guess, but here are two more.
For this sequence:
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
...
Else
If Not IsNull(ATTYNAME) And IsNull(ATYFIRM) Then
You already tested ATTYNAME and know result of true so to simplify do not retest, leaving the latter statement as
If IsNull(ATYFIRM) Then
This line is not arrived at from test
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
For its 'else' is paired to by first 'if'
If IsNull(CPATTYCODE) Or ATYLAST = "UNKNOWN" Or ATYLAST = "UNAVAILABLE" Then
If I interpret your remark about skipping, perhaps a revisit to data handing for something like CPATTYCODE.
For testing it is sometimes better to use small sample set than the main file, one that can be easily edited to switch a cell here and there back and forth between values, especially when one can have situation such as "" and " " present. Just a few rows can be sufficient, even selectively copied from database.
As you started with other compares such as for CPDOMVIOL, you may have additional code remaining that can impact result, Before using the compounded statements you may benefit temporarily by increasing number of embedded ifs.
Also consider inventing some temporary results to assess result of compares, such as for AddrLine5, make it obvious such as "Temp Test" or "AddrLine5" and temporarily remove later references to AddrLine5. For example
If IsNull(CPATTYCODE) Or ATYLAST = "UNKNOWN" Or ATYLAST = "UNAVAILABLE" Then
AddrLine5 = "__AddrLine5__"
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
To validate the result achieved by first if. If that new result is unattained, then debug by removing one of the three compares for that if statement. Or try them one at a time.
For this sequence:
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
...
Else
If Not IsNull(ATTYNAME) And IsNull(ATYFIRM) Then
You already tested ATTYNAME and know result of true so to simplify do not retest, leaving the latter statement as
If IsNull(ATYFIRM) Then
This line is not arrived at from test
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
For its 'else' is paired to by first 'if'
If IsNull(CPATTYCODE) Or ATYLAST = "UNKNOWN" Or ATYLAST = "UNAVAILABLE" Then
If I interpret your remark about skipping, perhaps a revisit to data handing for something like CPATTYCODE.
For testing it is sometimes better to use small sample set than the main file, one that can be easily edited to switch a cell here and there back and forth between values, especially when one can have situation such as "" and " " present. Just a few rows can be sufficient, even selectively copied from database.
As you started with other compares such as for CPDOMVIOL, you may have additional code remaining that can impact result, Before using the compounded statements you may benefit temporarily by increasing number of embedded ifs.
Also consider inventing some temporary results to assess result of compares, such as for AddrLine5, make it obvious such as "Temp Test" or "AddrLine5" and temporarily remove later references to AddrLine5. For example
If IsNull(CPATTYCODE) Or ATYLAST = "UNKNOWN" Or ATYLAST = "UNAVAILABLE" Then
AddrLine5 = "__AddrLine5__"
If IsNull(ATTYNAME) And Not IsNull(ATYFIRM) Then
To validate the result achieved by first if. If that new result is unattained, then debug by removing one of the three compares for that if statement. Or try them one at a time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@PatHartman
Indeed the code I posted is not production-worthy, but rather a sample of what the technique would look like @urjudo's own terms.
Building such code has three components
1. Defining expressions that will evaluate to true or false
2. Ordering the CASEs so that, if more than one could evaluate to true, in some circumstances, but not in others, that the results you want to have happen in such cases occurs.
3. Coding the results and testing.
The original coded block does not actually appear to be valid compilable code. We've got an ELSE without IF problem.
But then, that's why I don't use nested ifs, if Select Case can do the job :)
@urjudo must first define all the conditions without the nesting, and then order them.
The last bit of his code will become the first part of a Select Case True structure.
Indeed the code I posted is not production-worthy, but rather a sample of what the technique would look like @urjudo's own terms.
Building such code has three components
1. Defining expressions that will evaluate to true or false
2. Ordering the CASEs so that, if more than one could evaluate to true, in some circumstances, but not in others, that the results you want to have happen in such cases occurs.
3. Coding the results and testing.
The original coded block does not actually appear to be valid compilable code. We've got an ELSE without IF problem.
But then, that's why I don't use nested ifs, if Select Case can do the job :)
@urjudo must first define all the conditions without the nesting, and then order them.
The last bit of his code will become the first part of a Select Case True structure.
ASKER
Thanks Nick67 's explained and show me another way to do a better coding.
For example, your initial attempt also does not work as given, for two 'if-then-else-end if' are followed by an 'Else'. Two 'If' with three 'Else'. Not good.
Your simplification has redundant test for CPATYFIRM which is probably what you are seeking to correct to some test for CPATTYName. As it stands you have essentially (abbrev)
If Not IsNull(CPATYFIRM)
else
If IsNull(CPATYFIRM) Then
where the latter can only be true causing the last else to never be handled.