Happy to break it down:
[DISPLAY]: Nz(DLookup("[DisplayValue]", "tblDisplayLookup", _
"[RestraintType] = '" & [type of restraint] & "'"), "Index Not
Found")
[DISPLAY]: - The name of your calculated field. It will show the value
returned by
The DLookup
Nz - This is a VBA function to convert Nulls to some other value. We use
this to
handle any cases where the field [type of restraint] is not found in
the field
[RestraintType] in the table tblDisplayLookup. The DLookup will
return Null
in this case, and be replaced with the Value "Index Not Found"
DLookUp - The definition of this function can be found in VBA Help
[DisPlayValue] - A field in the table tblDisplayLookup that contains the
value you
want to return for the [Display] field in your
query. This is the
value the DLookUp will return if it finds a match.
The syntax of
DLookUp requires it be in quotes and since it is a
field name, it
Needs the brackets.
tblDisplayLookup - The name of the table that DLookUp will look in. Again,
the
quotes are required by the syntax.
"[RestraintType] = '" - This begins the criteria argument for the DLookUp.
RestrainType is the field we want to find a
value in. The
Brackets are there because it is a field.
The double qoutes
are to build a string. The criteria
argument is basically a
WHERE clause without the where. The end
result of this
argument needs to be a string with the
value to look for
enclosed in the correct type of delimiters.
Since we are
looking up a text field, the search value
has to be in quotes.
It is acceptable to use single qoutes
around it.
& - Both of these are just concatenation characters to build the pieces into
one
string
[type of restraint] - This is the field from the table your query is based
on we want
to look up. Again, the brackets indicate a
field name.
"'" - This is to put a qoute at the end of the criteria argument.
So when it is passed to Jet for the lookup, say [type of restraint] contains
"1224", it will look like this:
[RestraintType] = '1224'
"Index Not Found" - This is an argument for the Nz function. It tells Nz
what to
return when it receives a Null from DLookUp
If that doesn't answer your questions, feel free to post back.
ST8 EMPLOYEE said:
Okay...please be patient with me...
I anticipated this solution...so I created the table with all of the values
and their corresponding labels...and I kinda get the function you gave me
below...but not quite sure...
would you explain the variables in brackets and in quotations, so that I am
sure I am understanding you correctly...?
:
You would never be able to get that many conditionals in a nested IIf
statement.
If these display values exist in a table and you can look it up using [type
of restraint], you can use the following to do the work. If such a table
does not exist, you could create one with two fields: RestraintType and
DisplayValue with RestraintType being the primary key. Then you could code
the follwing in your query:
[DISPLAY]: Nz(DLookup("[DisplayValue]", "tblDisplayLookup", _
"[RestraintType] = '" & [type of restraint] & "'"), "Index Not
Found")
:
I have a field in my table that holds a value keyed from a list of numbers:
100-999. Each number has a corresponding "label" that I want to be displayed
in my query or (ultimately) in my report. I know the typical IIF statement
looks kinda like the following:
[DISPLAY]: IIf([type of restraint]="124","programmatic personal
restraint","emergency personal restraint")
But I want it to say:
If the number is "x", display "text string 1"...
If the number is "y", display "text string 2"...
If the number is "z", display "text string 3"...
...and so on...
What do I need to do?
