Query Not Working

[CT]

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.

 

[David Cox]

sometimes this is caused by a leading blank in the field. It displays very
narrow.

one solution sname: TRIM([field])

 

[CT]

Thanks,

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

sometimes this is caused by a leading blank in the field. It displays very
narrow.

one solution sname: TRIM([field])

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.

 

[Guest]

Post your SQL.

--
KARL DEWEY
Build a little - Test a little

Thanks,

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

sometimes this is caused by a leading blank in the field. It displays very
narrow.

one solution sname: TRIM([field])

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.

 

[CT]

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!

Post your SQL.

--
KARL DEWEY
Build a little - Test a little

Thanks,

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

sometimes this is caused by a leading blank in the field. It displays very
narrow.

one solution sname: TRIM([field])


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.

 

[Guest]

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

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!

Post your SQL.

--
KARL DEWEY
Build a little - Test a little

Thanks,

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


sometimes this is caused by a leading blank in the field. It displays very
narrow.

one solution sname: TRIM([field])


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.

 

[CT]

Thanks Karl..

Tried that..still returned no results.

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

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!

Post your SQL.

--
KARL DEWEY
Build a little - Test a little


:

Thanks,

I've confirmed there are no leading or trailing spaces in the

fields.
What

else could be going on?


sometimes this is caused by a leading blank in the field. It

displays
very

narrow.

one solution sname: TRIM([field])


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.

 

[Guest]

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

Thanks Karl..

Tried that..still returned no results.

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

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!


Post your SQL.

--
KARL DEWEY
Build a little - Test a little


:

Thanks,

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


sometimes this is caused by a leading blank in the field. It displays
very
narrow.

one solution sname: TRIM([field])


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.

 

[John W. Vinson]

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"));

Let me hazard a guess here: the field User in [Table - Mobile Devices] is a
Lookup field.

If it is, then what the table contains is NOT "smith, john", but rather a
concealed numeric foreign key to the lookup table.

This problem you're having is one of many reasons a lot of us really dislike
the lookup wizard.

John W. Vinson [MVP]

 

[CT]

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.

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"));

Let me hazard a guess here: the field User in [Table - Mobile Devices] is a
Lookup field.

If it is, then what the table contains is NOT "smith, john", but rather a
concealed numeric foreign key to the lookup table.

This problem you're having is one of many reasons a lot of us really dislike
the lookup wizard.

John W. Vinson [MVP]

 

[CT]

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.

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

Thanks Karl..

Tried that..still returned no results.

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


:

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!


Post your SQL.

--
KARL DEWEY
Build a little - Test a little


:

Thanks,

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


sometimes this is caused by a leading blank in the field. It displays
very
narrow.

one solution sname: TRIM([field])


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.

 

[Guest]

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

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.

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

Thanks Karl..

Tried that..still returned no results.


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


:

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!


Post your SQL.

--
KARL DEWEY
Build a little - Test a little


:

Thanks,

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


sometimes this is caused by a leading blank in the field. It
displays
very
narrow.

one solution sname: TRIM([field])


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.

 

[John W. Vinson]

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]

 

[CT]

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 !

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]

 

[John W. Vinson]

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]

 

[CT]

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!

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]

 

[John W. Vinson]

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]

 

[CT]

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

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]

 

[Scott Nash]

Thanks for the info.

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]