DLOOK UP in Query Criteria

[Guest]

Hi every one and tahnks Steve,

Below is Steve's answer to my question.

However, I forgot to mention that in the query I am includuding fields from
2 related tables.

These Tables are "Contacts" and "HotelList", related by the field "Hotel"

The relationship is one to many as below

1 Hotel in table "Hotels" and many "Hotels" in table "Contacts"


The "hotel" field I am using in the Query is from the "Table " "Contacts"


When I type the expression Steve has given me and run the Query, I receive a
message telling be that:

The specified field 'Hotel' could refer to more than one table in the FROM
clause of you SQL statement

Anybody able to help me on this!


Paul,

Well, the syntax of the expression is not quite correct. Try it like
this...
GmEmailAddress: DLookUp("","Contacts","[Hotel]='" & [Hotel] &
"' And [Contact Type]='GM'")

According to your description, it looks to me like you also need a
criteria of "Owner" in the Contact Type column of the query.

 

[david epsom dot com dot au]

GmEmailAddress: DLookUp("","Contacts","[Hotel]='" & [Hotel] &

You need to include the table name if the field is in more
than one table:

DLookUp("[Email]","Contacts","[email].[Hotel]='" & [tbl].[Hotel] &

(david)

[QUOTE="Paul."]
Hi every one and tahnks Steve,

Below is Steve's answer to my question.

However, I forgot to mention that in the query I am includuding fields
from
2 related tables.

These Tables are "Contacts" and "HotelList", related by the field "Hotel"

The relationship is one to many as below

1 Hotel in table "Hotels" and many "Hotels" in table "Contacts"


The "hotel" field I am using in the Query is from the "Table " "Contacts"


When I type the expression Steve has given me and run the Query, I receive
a
message telling be that:

The specified field 'Hotel' could refer to more than one table in the FROM
clause of you SQL statement

Anybody able to help me on this!


Paul,

Well, the syntax of the expression is not quite correct. Try it like
this...
GmEmailAddress: DLookUp("[Email]","Contacts","[Hotel]='" & [Hotel] &
"' And [Contact Type]='GM'")

According to your description, it looks to me like you also need a
criteria of "Owner" in the Contact Type column of the query.

--
Steve Schapel, Microsoft Access MVP


[QUOTE="Paul."]
Hi Everyone

I have the following expression in a field I add in the design view of a
query.

Basically I have a table of Contacts named "Contacts" with fields:
Contact Types : GM, Owner or Owner's Rep so it's text
Hotel: name of the hotel so text again, note that each contact is related
to
an hotel
E-mail: e-mail address of the person

In a Query I import all the fields and add one called GMEmailAddress:

For the field E-Mail, I have add a criteria : Is Null

This query is then supposed to give me the list of Owners without e-mail
addresses and the e-mail address of the GM of their hotel

The following script doesn't work !!!!

GmEmailAddress: DLookUp("[Contacts]![Email]","[Contacts]","
[Contacts]![Hotel]='" & [Hotel] & "' And [Contacts]![Contact Type]="GM")

I know that it we have to use ' or " depending of the nature of the field
but I think Ihave been carefull.

I receive a message Syntax error Wrong Operand

Anybody can help me on this,

Would be really greatfull![/QUOTE]
[/QUOTE]