Text field and IN clause

[DevAccess]

Hello I have text field in table which stores value like this.

UK
United States
Japan


Now I have sql query which is like this;

SELECT * FROM qry_maindata WHERE [Country] in ('UK','United States')

It returns only UK rows for example if it is first entry in record, some table row which has value like below, it does not return record from below entry.

South Africa
Japan
UK

Is it because of it is defined as text field and am putting value by line feed.

Please not that above field is free text field and data is being entered from FORM by using enter for new entry.

 

[jdraw]

Tell us about qry_maindata .
What table(s) are involved? Show the sql.
More info about your set up would be helpful.

If you have tblCountries, and some other table or query with references to Country eg Visited

Select PersonName, CountryName, VisitDate from
tblVisit inner join tblCountries On Visit.CountryID = tblCountries.CountryID

 

[DevAccess]

Tell us about qry_maindata .
What table(s) are involved? Show the sql.
More info about your set up would be helpful.

If you have tblCountries, and some other table or query with references to Country eg Visited

Select PersonName, CountryName, VisitDate from
tblVisit inner join tblCountries On Visit.CountryID = tblCountries.CountryID

It is just one query: which comprises of country table
Country table has following field
country id
country name

country name is text field in table and data is being entered from FORM and that is free text field and user enters the data with line feed/enter.

like below
UK
Canada
Australia

Now I wanto have sql query which should reutrn all the records which has country = UK or Country = Australia considering country name field is text field with line feed for multiple entries.

SELECT * FROM qry_maindata WHERE [Country] in ('UK','Australia')

 

[jdraw]

I recommend you do some research on table design. You really don't want a catch all field to record text strings separated by linefeed.

Review info at a few of the links on this page. Spend some time learning the underlying concepts, it will help you with design.

 

[almahmood]

Strange issue but I have a couple of check points in my mind which could be your solution:

1) Please make sure qry_maindata is showing all your records.
2) Check your data to make sure you don't have any unprintable character with country name. To check, copy any of your country name which is not appearing and paste it over notepad and see if there is any extra space or line feed/enter.