#Name error in Query

Rename the field from Phone to txtPhone. See if it's getting confused
between your filter and the name of the field. However, if it were me I
would use a DLookUp.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BBC via AccessMonster.com" u49322@uwe wrote in message
news:9137070b3c5c5@uwe...
the "client" table's primary key is ClientID and it is bound to the Client
form (as ClientID). The "contact" table also has a "ClientID" column that
is
related to the "Client.ClientID". The matching process seems to work
(when I
run the query from the QWizard it finds the right row and display the
right
contact address (remember non of this is related to "address" table,
although
it is open on another tab)
When I named these tables & fields I didn't expect it would be this
confusing
(I foolishly expected it all to work)

To recap.
I have Tables (with fields indicate by - ):
Client (displayed on the main form)
-ClientID (bound, numeric primary key)
-Phone (unbound txtbox)
Contact (related to "clients" tabel, a datagrid; located on
a tab control, bottom main form)
-ClientID (related to "clients.ClientID")
-Address (txt field containing the phone number)
Address (the table for mail/home address, a datagrid; located on
a tab control with "contact" and others)
-Address1,2, -City, -etc (this is not part of the interation
at this point but it does have a name in common
with a field in "contacts" so I've indicated its presence)

So I'm using the following query to place the primary phone number from
the
contacts table onto the form in the unbound control called "phone" (ie
it's
control source=(Select ....))

=(SELECT Contact.Address FROM Contact
WHERE (((Contact.ClientID)=[forms]![client]![clientid])
AND ((Contact.Category)="Phone")
AND ((Contact.Primary)=True))

I get the #Name error showing in the form's field txtbox "phone" when I
run
the form.
When I built this query in Query Wizard and ran it from there (ie selected
datagrid view) it actually works and shows me the primary phone number in
a
one-row,one column datagrid. As long as I had already tried to run the
form
and have itsitting in the background. I've gotton rid of the ![addreess]
as
it's not part of the issue and has become a red-herring.

I really apprecite the help, I have spent a whole week trying to solve
this.
It has become a major point of frustration for me, something so simple (or
not). I'm developing some free software for a chartible foundation and
I'm
just about at the point giving up (as I need this capability for a number
of
items on the form .. maybe I'll just have them re-type this stuff
everywhere
it's needed .. NOT)
thanks so much
Brian

Gina Whipp wrote:
BBC,

What is the name of the field on the form. If it's ClientID try changing
to
txtClientID.
Have read at least 100 posts re #name errors and still can't figure out
what's wrong with mine.
[quoted text clipped - 36 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

Thanks for your advice, I substituted DLookUp and it now works (a little slow
even with all 3 selection criteria as indices). I tried changing table,
field and textbox names to ensure no conflict but the problem persists. I
cannot figure out why it's not working (the query returns the right result in
the wizard, the problem would seem to lay in getting the returned result into
the form's textbox [both are simple text fields]). The query only (normally)
returns one record and one field to the textbox's recordsource, could there
be an issue in that it might be more if an error existed in the data. I
suspect it doesn't know or care and would just take the first item from the
query.
I'd really like this query process to work as I also need to sum some numeric
fields from child tables into textboxes on the main form.
thanks again
I'm about to post another question (on form sizing) if you're up for another
challange.


Gina Whipp wrote:
Rename the field from Phone to txtPhone. See if it's getting confused
between your filter and the name of the field. However, if it were me I
would use a DLookUp.

the "client" table's primary key is ClientID and it is bound to the Client
form (as ClientID). The "contact" table also has a "ClientID" column that
[quoted text clipped - 71 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

BBC,

It should not be slow... as a matter of fact, it should be quicker. Does
this database have any data? I'd be willing to take a look.

As for the next question, I would suggest posting a new question... not
because I am not up for the challenge but so everyone can benefit and you
get more prospectives/replies.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BBC via AccessMonster.com" u49322@uwe wrote in message
news:9184cbebee688@uwe...
Thanks for your advice, I substituted DLookUp and it now works (a little
slow
even with all 3 selection criteria as indices). I tried changing table,
field and textbox names to ensure no conflict but the problem persists. I
cannot figure out why it's not working (the query returns the right result
in
the wizard, the problem would seem to lay in getting the returned result
into
the form's textbox [both are simple text fields]). The query only
(normally)
returns one record and one field to the textbox's recordsource, could
there
be an issue in that it might be more if an error existed in the data. I
suspect it doesn't know or care and would just take the first item from
the
query.
I'd really like this query process to work as I also need to sum some
numeric
fields from child tables into textboxes on the main form.
thanks again
I'm about to post another question (on form sizing) if you're up for
another
challange.


Gina Whipp wrote:
Rename the field from Phone to txtPhone. See if it's getting confused
between your filter and the name of the field. However, if it were me I
would use a DLookUp.

the "client" table's primary key is ClientID and it is bound to the
Client
form (as ClientID). The "contact" table also has a "ClientID" column
that
[quoted text clipped - 71 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

There are about 8 records for this table in the test database. It takes
about 1 - 1.5 secs to get the 3 fields (3 DLookUps) and maybe I shouldn't be
concerned yet, maybe it will be the same with 800 or more recds but I surely
wouldn't want it to go up anywhere near proportionally. Because I never did
get the query/SQL way to work I don't have anything to compare to, although
I've read that DLookUp is faster.
I'm not going to sweat the speed right yet, maybe later though....

I did post the new question in the "Forms" section. The problem seems to be
very common (large numbers of posts) with the feedback focused on a few items,
but seems to be a mixed bag of suggestions, most of which I've tried.

thanks again

Gina Whipp wrote:
BBC,

It should not be slow... as a matter of fact, it should be quicker. Does
this database have any data? I'd be willing to take a look.

As for the next question, I would suggest posting a new question... not
because I am not up for the challenge but so everyone can benefit and you
get more prospectives/replies.

Thanks for your advice, I substituted DLookUp and it now works (a little
slow
[quoted text clipped - 31 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

BBC,

The time to worry is no in early development not down the road where it
could become an issue to fix. Hopefully, in your new psoting you'll get
some feedback that will help determine the issue. My DLookUp's cycle more
records then that and there is no delay, which is why it has me wondering
why yours gives you that 1 to 1.5 second delay.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BBC via AccessMonster.com" u49322@uwe wrote in message
news:918ebf056ce4c@uwe...
There are about 8 records for this table in the test database. It takes
about 1 - 1.5 secs to get the 3 fields (3 DLookUps) and maybe I shouldn't
be
concerned yet, maybe it will be the same with 800 or more recds but I
surely
wouldn't want it to go up anywhere near proportionally. Because I never
did
get the query/SQL way to work I don't have anything to compare to,
although
I've read that DLookUp is faster.
I'm not going to sweat the speed right yet, maybe later though....

I did post the new question in the "Forms" section. The problem seems to
be
very common (large numbers of posts) with the feedback focused on a few
items,
but seems to be a mixed bag of suggestions, most of which I've tried.

thanks again

Gina Whipp wrote:
BBC,

It should not be slow... as a matter of fact, it should be quicker. Does
this database have any data? I'd be willing to take a look.

As for the next question, I would suggest posting a new question... not
because I am not up for the challenge but so everyone can benefit and you
get more prospectives/replies.

Thanks for your advice, I substituted DLookUp and it now works (a little
slow
[quoted text clipped - 31 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

Below is the DLookUp I'm using

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

It is indexed on ClientID, Category & Primary
There are 7 recds in the table, 4 related to my 1st (and usual) test client.
This table is also simultaniously being displayed on one of the tabs of a
active tabcontrol on the same main form (only 1 recd would have
"primary=true" though)

Thanks Gina
Brian

Gina Whipp wrote:
BBC,

The time to worry is no in early development not down the road where it
could become an issue to fix. Hopefully, in your new psoting you'll get
some feedback that will help determine the issue. My DLookUp's cycle more
records then that and there is no delay, which is why it has me wondering
why yours gives you that 1 to 1.5 second delay.

There are about 8 records for this table in the test database. It takes
about 1 - 1.5 secs to get the 3 fields (3 DLookUps) and maybe I shouldn't
[quoted text clipped - 30 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

Brian,

This can't be the copy/paste... missing an ampersand, apostorphes and some
quotes... Can you please do a copy paste. If you look at the one I am
using you should see what I mean. Your symbols don't match your data type.

"[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " &
Forms![frmLogOn]![cboLoginID] & " And [llListingTypeID] = '" & Me.cboSortBy
& "'"

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BBC via AccessMonster.com" u49322@uwe wrote in message
news:91940a51a367a@uwe...
Below is the DLookUp I'm using

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

It is indexed on ClientID, Category & Primary
There are 7 recds in the table, 4 related to my 1st (and usual) test
client.
This table is also simultaniously being displayed on one of the tabs of a
active tabcontrol on the same main form (only 1 recd would have
"primary=true" though)

Thanks Gina
Brian

Gina Whipp wrote:
BBC,

The time to worry is no in early development not down the road where it
could become an issue to fix. Hopefully, in your new psoting you'll get
some feedback that will help determine the issue. My DLookUp's cycle more
records then that and there is no delay, which is why it has me wondering
why yours gives you that 1 to 1.5 second delay.

There are about 8 records for this table in the test database. It takes
about 1 - 1.5 secs to get the 3 fields (3 DLookUps) and maybe I
shouldn't
[quoted text clipped - 30 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

Here it is again, a direct copy/paste from the "controlsource". It gets the
correct results each time when switching parent records, so it does actually
work. As I recall I struggled with the quotes a bit and finally arrived at
this with the aid of either one of my books (or maybe a search on
AccessMonster).


=DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Gina Whipp wrote:
Brian,

This can't be the copy/paste... missing an ampersand, apostorphes and some
quotes... Can you please do a copy paste. If you look at the one I am
using you should see what I mean. Your symbols don't match your data type.

"[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " &
Forms![frmLogOn]![cboLoginID] & " And [llListingTypeID] = '" & Me.cboSortBy
& "'"

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Below is the DLookUp I'm using

[quoted text clipped - 25 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

BBC,

And you say this does not show immediately? I don't see anything wrong. Is
the ClientID a Primary Key in Contact table?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"BBC via AccessMonster.com" u49322@uwe wrote in message
news:919b611eebe5f@uwe...
Here it is again, a direct copy/paste from the "controlsource". It gets
the
correct results each time when switching parent records, so it does
actually
work. As I recall I struggled with the quotes a bit and finally arrived
at
this with the aid of either one of my books (or maybe a search on
AccessMonster).


=DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Gina Whipp wrote:
Brian,

This can't be the copy/paste... missing an ampersand, apostorphes and some
quotes... Can you please do a copy paste. If you look at the one I am
using you should see what I mean. Your symbols don't match your data
type.

"[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " &
Forms![frmLogOn]![cboLoginID] & " And [llListingTypeID] = '" &
Me.cboSortBy
& "'"

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Below is the DLookUp I'm using

[quoted text clipped - 25 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)

--
Message posted via http://www.accessmonster.com

First, you are missing a space in the third argument before the AND
Second, you can include the True inside the clause
Third, the assumption is that clientID is a number field. If it is not
then you are going to need to include text delimiters as shown below.
If it is a number then remove the apostophes (') that surround the
Forms!Client!ClientId reference.

=DLookUp("[contactdetail]","contact","[ClientID]= '" & [Forms]![client]!
[clientid] & "' And [category]='Phone' AND [Primary] = True")

What is the name of the control? It cannot be the same as the name of a
field included on the form/report.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


BBC via AccessMonster.com wrote:
Here it is again, a direct copy/paste from the "controlsource". It gets the
correct results each time when switching parent records, so it does actually
work. As I recall I struggled with the quotes a bit and finally arrived at
this with the aid of either one of my books (or maybe a search on
AccessMonster).


=DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Gina Whipp wrote:
Brian,

This can't be the copy/paste... missing an ampersand, apostorphes and some
quotes... Can you please do a copy paste. If you look at the one I am
using you should see what I mean. Your symbols don't match your data type.

"[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " &
Forms![frmLogOn]![cboLoginID] & " And [llListingTypeID] = '" & Me.cboSortBy
& "'"

DLookUp("[contactdetail]","contact","[ClientID]= " & [Forms]![client]!
[clientid] & "And [category]='Phone' AND [Primary] = " & True)

Below is the DLookUp I'm using

[quoted text clipped - 25 lines]
Very frustrating, surely can't be that hard ???
Thanks (sorry for the long post)