No. Len([tblUsers]![uMI])<1 does not cover the case where
uMI is Null. You may get the desired result with that but
Len(Null) returns Null and Null<1 also returns Null which is
neither True nor False. If the field does allow Zero Length
Strings then use either Nz(uMI,"") = "" or Len(Nz(uMI,""))=0
In A2003, the definitive list of VBA functions, their
arguments and their return value is available in:
VBA Help
Table of Contents
Microsoft Visual Basic Documentation
Visual Basic Language Reference
Functions
--
Marsh
MVP [MS Access]
that was indeed the problem. Given your answer, the solution was clear,=.
I simplied the test to Len([tblUsers]![uMI])<1 which covers alll cases with
one test.
Is there someplace on the web where all (or some) of the functions in Access
are defined, with a list of return value and parameters?
Marshall Barton said:
Twas wrote:
I've got a simple error that's bugging me. In a table I have a computed field
defined as
FullName: tblUsers!uFirstName & IIf(nz(tblUsers!uMI)=0 Or tblUsers!uMI="","
" & tblUsers!uLastName," " & tblUsers!uMI & ". " & tblUsers!uLastName)
If the uMI (middle initial) field is non-blank, I get "#Error" as the
result; if uMI is blank, it work. What's wrong??
I think the problem is that Nz of a text field returns a
string and you are comparing that to a numeric value(0).
Try changin your expression to:
FullName: tblUsers!uFirstName & IIf(nz(tblUsers!uMI, "")="",
" " & tblUsers!uLastName," " & tblUsers!uMI & ". " &
tblUsers!uLastName)
If the uMI field has its AllowZeroLength property set to No,
then you are checkin for a situation that can not occur. In
this case, the entire expression can be reduced to:
FullName: tblUsers!uFirstName & " " & (tblUsers!uMI + ". ")
& tblUsers!uLastName)
