By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Query Problem in Access

P: n/a
hi friends i need help in this sql query

i have table like,

id fid
__ _____
autonumber text

and i am storing values like

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

now to find values i am using query

sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')

only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%

now from this group some one give me the answer of this query

select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line
now this query is running perfectly in other database except msaccess
2000. can anyone solve this problem. this query is not giving any
answer. it checks all those records which are singleton but not middle
of line records. and it seems to be problem in access only not in
mysql. it is working perfectly in mysql but not in access and as access
is my database in application i have to use access and i am really
irritate when i find in help that either i can use ' * ' or ' % ' in
expression on any one side like '%,1' or '%1,' but not like middle of
line that i am using '%,1,%'

here is example of my problem

sample table:=

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

query like

select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line

will result id=1 perfectly but when i search

select *
from test
where fid = '2' -- singleton
or fid like '2,%' -- beginning of line
or fid like '%,2,%' -- middle of line
or fid like '%,2' -- end of line

it will not give me no output. plz help me i dont know what is the
problem if anyone can solve this i will be really thankful.

Oct 19 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 19 Oct 2006 00:56:48 -0700, "hardik" <ha**********@gmail.com>
wrote:

% is not a recognized wildcard for Access. Use * instead. See the
topic "About using wildcard characters to search for partial or
matching values" in the help file.

I find your solution creative but horrible. The real solution would be
to normalize this data.

-Tom.

>hi friends i need help in this sql query

i have table like,

id fid
__ _____
autonumber text

and i am storing values like

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

now to find values i am using query

sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')

only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%

now from this group some one give me the answer of this query

select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line
now this query is running perfectly in other database except msaccess
2000. can anyone solve this problem. this query is not giving any
answer. it checks all those records which are singleton but not middle
of line records. and it seems to be problem in access only not in
mysql. it is working perfectly in mysql but not in access and as access
is my database in application i have to use access and i am really
irritate when i find in help that either i can use ' * ' or ' % ' in
expression on any one side like '%,1' or '%1,' but not like middle of
line that i am using '%,1,%'

here is example of my problem

sample table:=

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

query like

select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line

will result id=1 perfectly but when i search

select *
from test
where fid = '2' -- singleton
or fid like '2,%' -- beginning of line
or fid like '%,2,%' -- middle of line
or fid like '%,2' -- end of line

it will not give me no output. plz help me i dont know what is the
problem if anyone can solve this i will be really thankful.
Oct 19 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.