Query in Field with both Numbers & Text Fail

  • Thread starter Thread starter pete.trudell
  • Start date Start date
P

pete.trudell

I have a Field which uses either numbers or Text, on a search of that
field to match criteria it will work searching for numbers, but fails
when searching for text??
 
I have a Field which uses either numbers or Text, on a search of that
field to match criteria it will work searching for numbers, but fails
when searching for text??
Click to expand...

Could you give us some help here? Please tell us the datatype of the
field (is it a Text field, I would guess?); the SQL view of the query;
a sample of the data on which it fails and the nature of the failure.

John W. Vinson[MVP]
 
I have a Field which uses either numbers or Text, on a search of that
field to match criteria it will work searching for numbers, but fails
when searching for text??
Click to expand...


We will need more information about the field and the
cirteria you are trying to use.

In general, a Text field can only be compared to a text
string, even if it contains only digits.
 
Interesting, We have 5 centers statewide using the same database to
track help calls within our centers, since any of the centers can call
any center (network) , I have an append query that is used daily to
upload each centers daily activity to a Master table which store all
the center's activity. This Master table is also linked to a special
Database at each center which allows them to pull reports.

The Centers wanted a report that would search by Team, so I added the
Team Field (text field) yesterday to the Append Query. But to test
the new Team Query I went into the Master Table and entered by hand
team names for each record. Some of the Centers use the Supervisors
name as the team name and some use numbers. Since there are over 3000
records I did a test by adding only the team names for 9/26/06.

I then went to the Special Database and ran the new Team Query, here it
would only bring up data if the team was a number, if ignored teams by
name.

This morning I did an append for this Center, which included help calls
from all the centers. When I ran the report it recognized teams by
number and by name??

So it seems that if the recod is updated by the Append Query the
information in the Team field can be recognized. If you go into the
Master Table and hard enter the data to the Team field , you can only
use the Numbered teams.

The process will work for all new entries , as soon as I update each
Centers Append Query.
But, only for data from this data forward, not backward.

Amazing.
 
So it seems that if the recod is updated by the Append Query the
information in the Team field can be recognized. If you go into the
Master Table and hard enter the data to the Team field , you can only
use the Numbered teams.
Click to expand...

Might you be another victim of the Lookup Field misfeature? Is the
Team field in the Master Table a Lookup field? If so, it does NOT
contain what it appears to contain, but instead contains a concealed
numeric ID.

John W. Vinson[MVP]
 
Might you be another victim of the Lookup Field misfeature? Is the
Team field in the Master Table a Lookup field? If so, it does NOT
contain what it appears to contain, but instead contains a concealed
numeric ID.

John W. Vinson[MVP]
Click to expand...

No, It is a standard Text Box, 10 characters , not even Indexed.

Pete
 
Back
Top