Hello, Are you asking for an exegesis of this sample you posted? To be
clearn, this is NOT an "odd combination of characters"; quite to the
contrary it is a very highly structured expression, with elements clearly
prescribed for them in the standard syntax for expressions of this type.
So, if you really want an explanation, here's how I would explain it.
The DLookup Function is one of the Domain Aggregate Functions (DMax and
DSum being two other examples).
The purpose of the domain aggregate functions is, essentially, to retrieve
values from a specified recordset. They do so by selecting value(s)
according to the parameters passed to the function, if any. Note the "if
any" part. Depending on the circumstances, you can omit criteria parameters
when it is appropriate to do so, or you may employ them. You apply criteria,
as you do in any other circumstance, in order to limit the values returned
by the function to those which are relevant to your purposes.
In this case, DLookup simply "Looks" in the recordsource and returns a
value. That source can be a table or a saved query. WHICH value the
expression returns depends on several factors, including the sort order
applied to the recourdsource, if any, and the criteria passed in the
function, if any. In other words, simply using an unqualified Dlookup can
return differnt values at different times, if for no other reason than that
the source table or source query can be sorted differently.
One thing can be predicted: DLookup will bring back the "FIRST" matching
value, whatever that is. For that reason, unqualified DLookups against
unsorted recordsets can yield unpredictble results. It's up to you,
therefore, to construct the expression in such as way as to retrieve the
exact value you need.
Inside the parentheses, you'll find three "arguments" or "parameters". They
are surrounded by quotes and separated by commas. That's the standard syntax
required.
The first parameter is the name of the field containing the values you want.
In your example, for example, the field is aptly named field1.
Note that it is enclosed in brackets. Access puts brackets around field
names to indicate they are units. If you follow one of the standard naming
conventions in which spaces and special characters are not permitted in
names, the brackets MAY be optional. However, if you have mistakenly used
one of the reserved words as a field name, you MUST enclose it in brackets
here. A commonly made mistake, for example, is to name a field in a table
Date. That, of course, is the name Access uses to retrieve date values, so
it must be bracketed anywhere you use it.
So, this DLookup retrieve a value from a field called field1. If there are
multiple records in the table, of course, you'll get only the "FIRST" value,
however that happens to land.
The second argument, or parameter, is the name of the recordset from which
the value is to be retrieved. In your example, this is the generic table.
Note that the same considerations apply to quotes, names, and brackets. Note
that it is separated from the field argument by the comma.
AT this point, then, the DLookup will go to the table specified (i.e.
) and grab the "first" value it encounters in the field specified
(i.e. [field1]).
Okay, now comes the third argument, which you use to restrict the selected
value to those that meet your requirements. Again, note the comma, the use
of quotes and brackets.
This example employs a shortcut, in which you ASSUME the name of the table
from which field2 comes. It would be more complete to state this expression
as
DLookup("[field1]","
","
.[field2]=" & [table2]![field3])
That's what we're doing here. We're telling Access to go to table and
retrieve the value from field1. In order for Access to know WHICH of the
many records in table we want, we append the criterion, "Get the value which
is found in the record(s) where field2 match the values in field3 in the
other table".
That could be one or more matches, depending on what field2 and field3
represent. I will leave exporing that aspect to you to work out.
The final thing to consider is that the syntax here allows you to pass in
the CURRENT value of field3 so that this expression returns different
results in different calls. =" & [table2]![field3])
Compare that to a hard-coded expression in which ONE value is always used as
the criteria:
DLookup("[field1]","
","
.[field2]=747")
see the difference there, where the hard-coded value is inside the quotes,
and in which case, the expression will always match on that value: 747.
It's possible to add more details, but that should be enough to give you a
fair picture of what is happening.
As always, I encourage clarifications and corrections. Thanks.
news:
[email protected]...
