Query Not Working

Karl,

When I used that criteria, it returned everyone in the table! Making
progress now...at least I'm getting results.

So in your suggested solution, you said to "add the lookup table and join in
your query. Then add
the criteria to the name field of the lookup table." Are you saying to
add the both tables (main table and user table) to the query? If so, when I
tried to do that I get an error that the tables are not related.


"KARL DEWEY" wrote in message
...
Is this a lookup field by chance? Test it by using 0 as criteria.

If so, then you need add the lookup table and join in your query. Then
add
the criteria to the name field of the lookup table.
--
KARL DEWEY
Build a little - Test a little


"CT" wrote:

Thanks Karl..

Tried that..still returned no results.


"KARL DEWEY" wrote in message
news
Maybe there are other errors as David Cox pointed out so try this --
WHERE ((([Table - Mobile Devices].User) Like "smith*"));

--
KARL DEWEY
Build a little - Test a little


"CT" wrote:

Here is the SQL"

SELECT [Table - Mobile Devices].Manufacturer, [Table - Mobile
Devices].Model, [Table - Mobile Devices].[Serial Number], [Table -
Mobile
Devices].User
FROM [Table - Mobile Devices]
WHERE ((([Table - Mobile Devices].User)="smith, john"));

Thanks!


"KARL DEWEY" wrote in message
...
Post your SQL.

--
KARL DEWEY
Build a little - Test a little


"CT" wrote:

Thanks,

I've confirmed there are no leading or trailing spaces in the
fields.
What
else could be going on?


"David Cox" wrote in message
news sometimes this is caused by a leading blank in the field. It
displays
very
narrow.

one solution sname: TRIM([field])


"CT" wrote in message
...
Hello,

I have set up a query for 1 table.

The field I'm trying to run the query on is a text field. I
enter a
name
(="smith, john") I get zero results. However I have
confirmed
that
this
user exists in the table.

Also (if this helps), this field gets populated via a combo
box
on a
form.
The table being used for the lookup contains a "user" field
which is
also
a
text field

Thanks.

Based on your post to John you need to join User to Staff.
--
KARL DEWEY
Build a little - Test a little


"CT" wrote:

Karl,

When I used that criteria, it returned everyone in the table! Making
progress now...at least I'm getting results.

So in your suggested solution, you said to "add the lookup table and join in
your query. Then add
the criteria to the name field of the lookup table." Are you saying to
add the both tables (main table and user table) to the query? If so, when I
tried to do that I get an error that the tables are not related.


"KARL DEWEY" wrote in message
...
Is this a lookup field by chance? Test it by using 0 as criteria.

If so, then you need add the lookup table and join in your query. Then
add
the criteria to the name field of the lookup table.
--
KARL DEWEY
Build a little - Test a little


"CT" wrote:

Thanks Karl..

Tried that..still returned no results.


"KARL DEWEY" wrote in message
news Maybe there are other errors as David Cox pointed out so try this --
WHERE ((([Table - Mobile Devices].User) Like "smith*"));

--
KARL DEWEY
Build a little - Test a little


"CT" wrote:

Here is the SQL"

SELECT [Table - Mobile Devices].Manufacturer, [Table - Mobile
Devices].Model, [Table - Mobile Devices].[Serial Number], [Table -
Mobile
Devices].User
FROM [Table - Mobile Devices]
WHERE ((([Table - Mobile Devices].User)="smith, john"));

Thanks!


"KARL DEWEY" wrote in message
...
Post your SQL.

--
KARL DEWEY
Build a little - Test a little


"CT" wrote:

Thanks,

I've confirmed there are no leading or trailing spaces in the
fields.
What
else could be going on?


"David Cox" wrote in message
news sometimes this is caused by a leading blank in the field. It
displays
very
narrow.

one solution sname: TRIM([field])


"CT" wrote in message
...
Hello,

I have set up a query for 1 table.

The field I'm trying to run the query on is a text field. I
enter a
name
(="smith, john") I get zero results. However I have
confirmed
that
this
user exists in the table.

Also (if this helps), this field gets populated via a combo
box
on a
form.
The table being used for the lookup contains a "user" field
which is
also
a
text field

Thanks.

On Mon, 11 Jun 2007 16:13:53 -0400, "CT" wrote:

User in [Table - Mobile Devices] is a text field.

On the form I use a combo box to do a lookup on a table named STAFF and have
the selected value stored in the USER field from the Mobile Devices table.

If you open the Mobile Devices table, what is actually stored in the USER
field? "Smith, John" or perhaps a user code like JSMIT? The fact that you're
using a combo box to look up a value suggests the possibility that you're
storing something other than the value displayed by the combo. What's the
combo's RowSource, and what's its bound column?

John W. Vinson [MVP]

There is an actual name (Smith, John) in the USER field in the Mobile
Devices table.

This is what is in the Combo's Row source:
SELECT [Table - DD MOESC Staff List].ID, [Table - DD MOESC Staff List].Names
FROM [Table - DD MOESC Staff List] ORDER BY [Names];

This is what was in the bound column: 1

Thanks !

"John W. Vinson" wrote in message
...
On Mon, 11 Jun 2007 16:13:53 -0400, "CT" wrote:

User in [Table - Mobile Devices] is a text field.

On the form I use a combo box to do a lookup on a table named STAFF and
have
the selected value stored in the USER field from the Mobile Devices
table.

If you open the Mobile Devices table, what is actually stored in the USER
field? "Smith, John" or perhaps a user code like JSMIT? The fact that
you're
using a combo box to look up a value suggests the possibility that you're
storing something other than the value displayed by the combo. What's the
combo's RowSource, and what's its bound column?

John W. Vinson [MVP]

On Tue, 12 Jun 2007 12:19:05 -0400, "CT" wrote:

There is an actual name (Smith, John) in the USER field in the Mobile
Devices table.

This is what is in the Combo's Row source:
SELECT [Table - DD MOESC Staff List].ID, [Table - DD MOESC Staff List].Names
FROM [Table - DD MOESC Staff List] ORDER BY [Names];

This is what was in the bound column: 1

In that case you are NOT storing the User Name into the bound column of the
combo box; you are storing the ID.

What you *SEE* is the second column - the name; what's actually in the table
is the ID, presumably a Long Integer number. A query criterion of "Smith,
John" will not match an ID of 431.

Please, again: check the definition of the table being used as this form's
recordsource (not [Table - DD MOESC Staff List] but the main form's table).
Select the User Name field. Look on the "Lookup" tab in the field properties.
Does it say Combo Box?

If so, you are yet another victim of Microsoft's misdesigned, misleading,
infuriating Lookup Field misfeature. The table does not contain what it
appears to contain!

John W. Vinson [MVP]

Yes..it says COMBO box. So what are my options to correct this? I really
need to be able to query this table using the staff's name.

Thanks for hanging in there with me on this problem!


"John W. Vinson" wrote in message
...
On Tue, 12 Jun 2007 12:19:05 -0400, "CT" wrote:

There is an actual name (Smith, John) in the USER field in the Mobile
Devices table.

This is what is in the Combo's Row source:
SELECT [Table - DD MOESC Staff List].ID, [Table - DD MOESC Staff
List].Names
FROM [Table - DD MOESC Staff List] ORDER BY [Names];

This is what was in the bound column: 1

In that case you are NOT storing the User Name into the bound column of
the
combo box; you are storing the ID.

What you *SEE* is the second column - the name; what's actually in the
table
is the ID, presumably a Long Integer number. A query criterion of "Smith,
John" will not match an ID of 431.

Please, again: check the definition of the table being used as this form's
recordsource (not [Table - DD MOESC Staff List] but the main form's
table).
Select the User Name field. Look on the "Lookup" tab in the field
properties.
Does it say Combo Box?

If so, you are yet another victim of Microsoft's misdesigned, misleading,
infuriating Lookup Field misfeature. The table does not contain what it
appears to contain!

John W. Vinson [MVP]

On Sat, 16 Jun 2007 22:14:18 -0400, "CT" wrote:

Yes..it says COMBO box. So what are my options to correct this? I really
need to be able to query this table using the staff's name.

You can't query *that table* for the staff name - because that table *DOES
NOT CONTAIN* the staff name, in any way, shape, or form.

The field which *appears* to contain the staff name actually contains a long
integer StaffID. The Lookup Wizard has concealed that very basic fact from
view by displaying data *from a different table*, the lookup table.

The solution? Instead of looking for the staff name where it isn't, either
look for it where it *is* - by creating a query joining your main table to the
lookup table; or, use an unbound Combo Box on a form to let the user select a
StaffID (while seeing only the staff name), and use

=Forms![nameofform]![nameofcombobox]

as a criterion in your query, to search for the ID value (which is in fact in
your table).

John W. Vinson [MVP]

I think I got it now! Thanks for your help!


"John W. Vinson" wrote in message
...
On Sat, 16 Jun 2007 22:14:18 -0400, "CT" wrote:

Yes..it says COMBO box. So what are my options to correct this? I really
need to be able to query this table using the staff's name.

You can't query *that table* for the staff name - because that table
*DOES
NOT CONTAIN* the staff name, in any way, shape, or form.

The field which *appears* to contain the staff name actually contains a
long
integer StaffID. The Lookup Wizard has concealed that very basic fact from
view by displaying data *from a different table*, the lookup table.

The solution? Instead of looking for the staff name where it isn't, either
look for it where it *is* - by creating a query joining your main table to
the
lookup table; or, use an unbound Combo Box on a form to let the user
select a
StaffID (while seeing only the staff name), and use

=Forms![nameofform]![nameofcombobox]

as a criterion in your query, to search for the ID value (which is in fact
in
your table).

John W. Vinson [MVP]

Thanks for the info.
"John W. Vinson" wrote in message
...
On Sat, 16 Jun 2007 22:14:18 -0400, "CT" wrote:

Yes..it says COMBO box. So what are my options to correct this? I really
need to be able to query this table using the staff's name.

You can't query *that table* for the staff name - because that table
*DOES
NOT CONTAIN* the staff name, in any way, shape, or form.

The field which *appears* to contain the staff name actually contains a
long
integer StaffID. The Lookup Wizard has concealed that very basic fact from
view by displaying data *from a different table*, the lookup table.

The solution? Instead of looking for the staff name where it isn't, either
look for it where it *is* - by creating a query joining your main table to
the
lookup table; or, use an unbound Combo Box on a form to let the user
select a
StaffID (while seeing only the staff name), and use

=Forms![nameofform]![nameofcombobox]

as a criterion in your query, to search for the ID value (which is in fact
in
your table).

John W. Vinson [MVP]