Proper syntax for DLookup criteria

[George]

Hello everybody,
I want to update a text box in a form based on a value
from a query that is not part of the form, so I'm using
DLookup. I'm trying to pass criteria to the DLookup
method which is composed of 3 fields.

I need to pass 2 number values and 1 text value.
I've been trying different combinations with AND in the
middle, and the text value between "" but it looks like
I'm not hitting the correct syntax (the function always
return only the first value on the query for ALL rows)

I need to retrieve COST from STAGE QUERY, based on
HOUSEID, OPTIONID, and ROOMNAME (nmbr, nmbr, text). I've
been using expressions like this:

=DLookUp("Cost", "Decorator Choices SubQuery", "HouseID="
& [HouseID] "And" "AvailableOptions.OptionNmbr=" &
[OptionNmbr] "And" "RoomName=" "" [RoomName] "")

Could anybody help me out with the syntax?
Thanks in advance for your help!
Regards,
George

 

[Douglas J. Steele]

Already answered in another newsgroup to which you posted the same question
(I think it was m.p.a.modulesdaovba)

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

If you're using Microsoft's web interface to post, please note that you can
type the names of the various groups into the Newsgroup box. Separate each
newsgroup name with a semicolon.

Note that it's generally consider to be A Bad Thing to cross-post to more
than about 2 or 3 newsgroups. (In fact, at
http://www.microsoft.com/presspass/features/2001/Mar01/Mar27pmvp.asp
Microsoft suggests that "One group will suffice")

 

[George]

Thank you Doug, I'll take that into consideration for
future postings. I didn't know that
I'm trying with the solution you gave me on the other
posting, although I'll have to use Aliases (I'm checking
how).
I'll keep the thread on the other posting on
modulesdaovba.
Thank you and I'm sorry for posting twice.
Regards,
George

-----Original Message-----
Already answered in another newsgroup to which you posted the same question
(I think it was m.p.a.modulesdaovba)

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

If you're using Microsoft's web interface to post, please note that you can
type the names of the various groups into the Newsgroup box. Separate each
newsgroup name with a semicolon.

Note that it's generally consider to be A Bad Thing to cross-post to more
than about 2 or 3 newsgroups. (In fact, at
http://www.microsoft.com/presspass/features/2001/Mar01/Ma r27pmvp.asp
Microsoft suggests that "One group will suffice")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello everybody,
I want to update a text box in a form based on a value
from a query that is not part of the form, so I'm using
DLookup. I'm trying to pass criteria to the DLookup
method which is composed of 3 fields.

I need to pass 2 number values and 1 text value.
I've been trying different combinations with AND in the
middle, and the text value between "" but it looks like
I'm not hitting the correct syntax (the function always
return only the first value on the query for ALL rows)

I need to retrieve COST from STAGE QUERY, based on
HOUSEID, OPTIONID, and ROOMNAME (nmbr, nmbr, text). I've
been using expressions like this:

=DLookUp("Cost", "Decorator Choices SubQuery", "HouseID="
& [HouseID] "And" "AvailableOptions.OptionNmbr=" &
[OptionNmbr] "And" "RoomName=" "" [RoomName] "")

Could anybody help me out with the syntax?
Thanks in advance for your help!
Regards,
George

.