DLookup - why this syntax?

After a couple frustrating weeks - typos and my own ignorance - I finally
got a functioning control source for a textbox (and it works as desired LOL)
:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatus txtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's how
I've seen it done here and elsewhere (without explanation for the syntax).
My (very noob) question is: Why is this concatenation necessary?

-Ron

Because you are using a variable, not a value. The variable hold the value,
but is not itself the value. If you were to use a variable from the code
instead of from the form it would look like:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatus txtBox.ItemsID
= " & lngID
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Ron" wrote in message
...
After a couple frustrating weeks - typos and my own ignorance - I finally
got a functioning control source for a textbox (and it works as desired
LOL) :

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatus txtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's
how I've seen it done here and elsewhere (without explanation for the
syntax). My (very noob) question is: Why is this concatenation necessary?

-Ron

Ok, thanks. But isn't the the first parameter also a variable? I guess
what it comes down to is that the function "knows" that parameter will
always be a field name, so the quotes requirement is simply "protocol."
(Just trying to get some insight into designation convention. Gotta read
more.)

Thanks. -Ron

"Arvin Meyer [MVP]" wrote in message
...
Because you are using a variable, not a value. The variable hold the
value, but is not itself the value. If you were to use a variable from the
code instead of from the form it would look like:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatus txtBox.ItemsID
= " & lngID
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Ron" wrote in message
...
After a couple frustrating weeks - typos and my own ignorance - I finally
got a functioning control source for a textbox (and it works as desired
LOL) :

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatus txtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's
how I've seen it done here and elsewhere (without explanation for the
syntax). My (very noob) question is: Why is this concatenation necessary?

-Ron

"Ron" wrote in
:

Ok, thanks. But isn't the the first parameter also a variable? I
guess what it comes down to is that the function "knows" that
parameter will always be a field name, so the quotes requirement
is simply "protocol." (Just trying to get some insight into
designation convention. Gotta read more.)

Thanks. -Ron

No, in your examplethe first paramater is a literal. You are telling
the DLookup function to look in the named field. in the filter
parameter you are telling Dlookup to look at the value contained in
lngID and use that value. Without the concatenation, DLookup would
try to filter on the literal string "lngID"

You can use a variable for the field, and for the table, as follows
in VBA code.
Dim MyField as string
Dim MyTable as string
Myfield = "FldStatus"
MyTable = "QryforCurrentStatusTxtBox"
something =Dlookup(""[" & MyField & "]"",""[" & MyTable & "]"", ...
etc., but that's not often necessary.

Q



"Arvin Meyer [MVP]" wrote in message
...
Because you are using a variable, not a value. The variable hold
the value, but is not itself the value. If you were to use a
variable from the code instead of from the form it would look
like:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentS
tatustxtBox.ItemsID = " & lngID
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Ron" wrote in message
...
After a couple frustrating weeks - typos and my own ignorance -
I finally got a functioning control source for a textbox (and it
works as desired LOL) :

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrent
StatustxtBox.ItemsID = " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the
entire criteria parameter in quotes. I put the quotes as shown,
because that's how I've seen it done here and elsewhere (without
explanation for the syntax). My (very noob) question is: Why is
this concatenation necessary?

-Ron









--
Bob Quintal

PA is y I've altered my email address.

Ok, thanks. But isn't the the first parameter also a variable? I
guess what it comes down to is that the function "knows" that
parameter will always be a field name, so the quotes requirement
is simply "protocol." (Just trying to get some insight into
designation convention. Gotta read more.)

Thanks. -Ron

No, in your examplethe first paramater is a literal. You are telling
the DLookup function to look in the named field. in the filter
parameter you are telling Dlookup to look at the value contained in
lngID and use that value. Without the concatenation, DLookup would
try to filter on the literal string "lngID"

Well, lngID was from Arvin's example. I thought my OP criteria expression
contained only field names also. I guess the fact that the ID field is
expressed as a *form property* makes it a variable? Anyway, I'll keep
reading.

You can use a variable for the field, and for the table, as follows
in VBA code.
Dim MyField as string
Dim MyTable as string
Myfield = "FldStatus"
MyTable = "QryforCurrentStatusTxtBox"
something =Dlookup(""[" & MyField & "]"",""[" & MyTable & "]"", ...
etc., but that's not often necessary.

Thanks to both of you for the VBA suggestions. I may need them. Turns out
I forgot to anticipate that my DLookup expression generates an error when
the form moves to a new record (because some fields on which the query are
based are still null). So I may have to define the textbox controlsource
conditionally in code. (It'd be nice if there were an OnError() function
that works like IIF(). )

Thanks, -Ron




"Arvin Meyer [MVP]" wrote in message
...
Because you are using a variable, not a value. The variable hold
the value, but is not itself the value. If you were to use a
variable from the code instead of from the form it would look
like:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentS
tatustxtBox.ItemsID = " & lngID
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Ron" wrote in message
...
After a couple frustrating weeks - typos and my own ignorance -
I finally got a functioning control source for a textbox (and it
works as desired LOL) :

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrent
StatustxtBox.ItemsID = " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the
entire criteria parameter in quotes. I put the quotes as shown,
because that's how I've seen it done here and elsewhere (without
explanation for the syntax). My (very noob) question is: Why is
this concatenation necessary?

-Ron

"Ron" wrote in
:


Ok, thanks. But isn't the the first parameter also a variable?
I guess what it comes down to is that the function "knows" that
parameter will always be a field name, so the quotes requirement
is simply "protocol." (Just trying to get some insight into
designation convention. Gotta read more.)

Thanks. -Ron

No, in your examplethe first paramater is a literal. You are
telling the DLookup function to look in the named field. in the
filter parameter you are telling Dlookup to look at the value
contained in lngID and use that value. Without the concatenation,
DLookup would try to filter on the literal string "lngID"

Well, lngID was from Arvin's example. I thought my OP criteria
expression contained only field names also. I guess the fact that
the ID field is expressed as a *form property* makes it a
variable? Anyway, I'll keep reading.

There is a difference between the name of a field and the contents of
that field. And yes, a textbox on a form is a sort of variable.

For your issue with a new record, it's a lot easier to move the
Dlookup to code, leaving the target textbox's control source blank.
You'd put it in the form's on current event, and surround it with a
if statement that tests for an empty textbox and skips doing the
lookup. You'd also put code to (re)do the lookup if you change that
textbox in its after_update event.



--
Bob Quintal

PA is y I've altered my email address.

On Fri, 12 Feb 2010 18:08:58 -0500, "Ron" wrote:

After a couple frustrating weeks - typos and my own ignorance - I finally
got a functioning control source for a textbox (and it works as desired LOL)
:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatus txtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's how
I've seen it done here and elsewhere (without explanation for the syntax).
My (very noob) question is: Why is this concatenation necessary?

-Ron


It's not that the third parameter needs to be in quotes; the function just
requires that it be *a text string*, in particular a text string which is a
valid SQL WHERE clause (without the word WHERE).

If you had a criterion such as

"[ZipCode] = '83660'"

with no variables, then a string constant would work fine.

However, it's much more useful to be able to *construct* the text string by
pieceing together fragments - e.g. a literal text string

"QryforCurrentStatustxtBox.ItemsID ="

and a variable value 3156 (pulled from the form reference). The DLookUp
function then ends up using

"QryforCurrentStatustxtBox.ItemsID = 3156"

as its criteria parameter.
--

John W. Vinson [MVP]

On Sat, 13 Feb 2010 10:42:01 -0500, "Ron" wrote:

Thanks to both of you for the VBA suggestions. I may need them. Turns out
I forgot to anticipate that my DLookup expression generates an error when
the form moves to a new record (because some fields on which the query are
based are still null). So I may have to define the textbox controlsource
conditionally in code. (It'd be nice if there were an OnError() function
that works like IIF(). )

A getaround is to use NZ() in the criteria parameter, to return some value
which doesn't correspond to any record if the control is NULL.
--

John W. Vinson [MVP]

After a couple frustrating weeks - typos and my own ignorance - I finally
got a functioning control source for a textbox (and it works as desired
LOL)
:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatus txtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's
how
I've seen it done here and elsewhere (without explanation for the syntax).
My (very noob) question is: Why is this concatenation necessary?

-Ron


It's not that the third parameter needs to be in quotes; the function just
requires that it be *a text string*, in particular a text string which is
a
valid SQL WHERE clause (without the word WHERE).

If you had a criterion such as

"[ZipCode] = '83660'"

with no variables, then a string constant would work fine.

However, it's much more useful to be able to *construct* the text string
by
pieceing together fragments - e.g. a literal text string

"QryforCurrentStatustxtBox.ItemsID ="

and a variable value 3156 (pulled from the form reference). The DLookUp
function then ends up using

"QryforCurrentStatustxtBox.ItemsID = 3156"

as its criteria parameter.

Thanks John and thanks Bob for the helpful comments. Understood the
criteria parameter needs to be a string. You guys are gonna laugh at this,
but there's something even more fundamental that I didn't realize (and I
proved it for myself in debug): The concatenation of a string and a number
yields... a string! Doh! It all falls into place now. DLookup is mine!

I'm moving that unbound textbox expression from the control source property
to event methods. Since the box is on a main form, and the data on which
its value depends are in a combobox on a subform, and I'm inexperienced
, it's a matter of providing for different contingencies, since the user
could update existing records on both the main and subforms, and could also
add new records to either.

Thanks again. -Ron

On Sat, 13 Feb 2010 21:55:38 -0500, "Ron" wrote:

I'm moving that unbound textbox expression from the control source property
to event methods. Since the box is on a main form, and the data on which
its value depends are in a combobox on a subform, and I'm inexperienced
, it's a matter of providing for different contingencies, since the user
could update existing records on both the main and subforms, and could also
add new records to either.

Note that neither a DLookUp nor a text literal will be updateable...

If you're having trouble post the SQL view of the form and subform's
recordsource and a bit more information about what you're trying to
accomplish. Having a single textbox on a mainform dependent on a combo box on
a subform (which could represent thousands of records) seems suspect to me!
--

John W. Vinson [MVP]