Conditional null value

[atlantis43 via AccessMonster.com]

Wondering if anyone can suggest a way to simplify the following code, which
I’m using in the OnFormat event of a report.

If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me!
[PtAcct#])) Then
Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", " ")
End If
If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me!
[PtAcct#])) Then
Me![Field194] = Replace(DLookup("[ICD3]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", " ")
End If
If Not IsNull(DLookup("[ICD4]", "EntryICDs", "[EntryNum] = " & Me!
[PtAcct#])) Then
Me![Field196] = Replace(DLookup("[ICD4]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", " ")
End If


If I use the following code line alone:
Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", " ")
I the Replace function yields an errrmsg if the DLookup function is null
Is there any way to use an 'if isnull' function or an IIf function to
accomplish the If---Then clauses that I have included, so that the coding is
neater?

TIA, Richard

 

[Marshall Barton]

Wondering if anyone can suggest a way to simplify the following code, which
I’m using in the OnFormat event of a report.

If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me!
[PtAcct#])) Then
Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", " ")
End If
If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me!
[PtAcct#])) Then
Me![Field194] = Replace(DLookup("[ICD3]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", " ")
End If
If Not IsNull(DLookup("[ICD4]", "EntryICDs", "[EntryNum] = " & Me!
[PtAcct#])) Then
Me![Field196] = Replace(DLookup("[ICD4]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", " ")
End If


If I use the following code line alone:
Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", " ")
I the Replace function yields an errrmsg if the DLookup function is null
Is there any way to use an 'if isnull' function or an IIf function to
accomplish the If---Then clauses that I have included, so that the coding is
neater?

I think you can avoid the whole thing if you chabge the
report's record source to a query the Joins the EntryICDs
table and includes the EntryICDs fields ICD2, ICD3 and ICD4.

The text boxes can then do the replace in the control source
expression:

=Replace(ICD2, ".", " ")