Skip to main content
E
emmim44
Inspiring
April 30, 2008
Question

Ms Access - Data Mismatch

  • April 30, 2008
  • 5 replies
  • 756 views
Hi all,

I am not getting the correct data when use my simple ms access query ... I mean lets say I am looking for 2 (which is a number ) within the records... The result returns the sample " 53, 23, 24, 4, 43, 56, 55, 44, 59 "... Why is that ???
FindRouteID is Autonumber and Route_knowledge is text in ms access... Thanks all.


This topic has been closed for replies.

5 replies

N
Newsgroup_User
Inspiring
May 2, 2008
normalize your database.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
E
emmim44Author
Inspiring
May 1, 2008
It means that I cannot use outside the ms access.... Then How will I overcome my initial problem....??
E
emmim44Author
Inspiring
May 1, 2008
I have done some research ....The below function works in ms access editor but not in CF page...Why ?

select ID from drivers where instr((',' + Replace(Route_knowledge,' ','') + ','),#RtID#)

I am getting this error....
Undefined function 'Replace' in expression
P
paross1
Participating Frequently
May 1, 2008
After a little Googling.... using REPLACE within CFQUERY for ACCESS db

Phil
E
emmim44Author
Inspiring
May 1, 2008
Noap it doesnt. And I believe that I didnt normalize the table... Route_knowledge is defined as text that contains numbers delimited by comma...
P
paross1
Participating Frequently
May 1, 2008
Sounds like a good candidate for normalization then. (Or figure out how to do it "the hard way".)

Phil
P
paross1
Participating Frequently
May 1, 2008
Since you want to compare string to string, try "converting" FindRouteID to a string using CStr():

Does this work?

select ID
from drivers
where instr(Route_knowledge, CStr(FindRouteID))

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices