DLookup error: " ...(missing operator) in query expression"

[sfrvn@earthlink.net]

I am embarrassed to say I cannot make this work. Recently upgraded to
Access 2003, but do not know if that part of problem (AKA 'syntax
change'). Would someone be kind enough to lead me by the hand?

Table name: LUtblProvider
Table field names: provPKID, name, company, type, phone

Table name: tblRef
Table field names: refPKID, prov, event, src

Form: frmRef BOUND TO: qryRef and DISPLAYS query
results appropriately based on criteria specified. Value of
tblRef.prov **is** displayed on frmRef in textbox called "txtProv"

Problem:
Another textbox called "txtType" on frmRef is intended to display the
TYPE of provider. Idea was to use DLOOKUP to display PROV TYPE
corresponding to PROV NAME by interrogating LUtblProvider.

Following code in ControlSource of txtType:
=DLookUp("[type]","LUtblProvide r","[name]= " & [Forms]![frmRef]!
[txtprov])

Receiving this error:
Syntax error (missing operator) in query expression '[name]= Jim
Adams'.
Note: Name Jim Adams is displayed in txtProv so Access is
recognizing the PROV NAME.

I will NOT admit to how long I have beat my forehead on the desk about
this!

Your help would be very much appreciated.

gary b

[fredg]

Re: DLookup error: " ...(missing operator) in query expression&quot ;

On 6 Feb 2007 17:14:21 -0800, sfrvn@earthlink .net wrote:

I am embarrassed to say I cannot make this work. Recently upgraded to
Access 2003, but do not know if that part of problem (AKA 'syntax
change'). Would someone be kind enough to lead me by the hand?
>
Table name: LUtblProvider
Table field names: provPKID, name, company, type, phone
>
Table name: tblRef
Table field names: refPKID, prov, event, src
>
Form: frmRef BOUND TO: qryRef and DISPLAYS query
results appropriately based on criteria specified. Value of
tblRef.prov **is** displayed on frmRef in textbox called "txtProv"
>
Problem:
Another textbox called "txtType" on frmRef is intended to display the
TYPE of provider. Idea was to use DLOOKUP to display PROV TYPE
corresponding to PROV NAME by interrogating LUtblProvider.
>
Following code in ControlSource of txtType:
=DLookUp("[type]","LUtblProvide r","[name]= " & [Forms]![frmRef]!
[txtprov])
>
Receiving this error:
Syntax error (missing operator) in query expression '[name]= Jim
Adams'.
Note: Name Jim Adams is displayed in txtProv so Access is
recognizing the PROV NAME.
>
I will NOT admit to how long I have beat my forehead on the desk about
this!
>
Your help would be very much appreciated.
>
gary b

Several problems that I can see.

1) Name is a reserved Access/VBA/Jet word and should not be used as a
field name.

2) Type is a Field property and also should not be used as a field
name.

For a more complete list of reserved words, see:

http://www.allenbrowne.com/AppIssueBadWord.html

3) What datatype is the field [Name]?
I would guess it's Text datatype. If so, after you change the names of
the Type and Name fields, use:

=DLookUp("[TypeField]","LUtblProvide r","[NameField]= """ &
Me![txtprov] & """")

Note that I've used the Me keyword in place of forms!frmRef.


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[sfrvn@earthlink.net]

Re: DLookup error: " ...(missing operator) in query expression&quot ;

Thank you for the pointers, Fred. I will give them a try. I should
have remembered about the keyword issue. I ran into that before.
<slapping forehead!>

I will try your suggestions... I am sure they will work. I really
appreciate your willingness to help. Now if you will excuse me, I'm
going to go take some more Prozac!!

Thanks!

gary b

>
Several problems that I can see.
>
1) Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
>
2) Type is a Field property and also should not be used as a field
name.
>
For a more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html
>
3) What datatype is the field [Name]?
I would guess it's Text datatype. If so, after you change the names of
the Type and Name fields, use:
>
=DLookUp("[TypeField]","LUtblProvide r","[NameField]= """ &
Me![txtprov] & """")
>
Note that I've used the Me keyword in place of forms!frmRef.
>
--
Fred