 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / December 2007Simple Select Query
I have a this table where users enter data using a entry form, and I have data appended from a Web site. I have a field called Location. When I query by location I only get the records entered thru the form, if I use the full location name. If I use the Like function however, I get all the records. Any ideas why, since I'd like to use a combo to select location. Hi, try this query create a new query, in SQL view type in: SELECT location FROM [TableName] replace TableName with the name of your table. does this query return all the records for location? Jeanette Cunningham >I have a this table where users enter data using a entry form, and I have > data appended from a Web site. I have a field called Location. When I [quoted text clipped - 3 lines] > location name. If I use the Like function however, I get all the records. > Any ideas why, since I'd like to use a combo to select location. Just a guess. Is there a chance that the data you are appending from a web site may contain some leading spaces? Try updating your [Location] field. UPDATE [yourTable] SET [Location] = TRIM([Location]) HTH Dale  SignatureDon''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. > I have a this table where users enter data using a entry form, and I have > data appended from a Web site. I have a field called Location. When I query > by location I only get the records entered thru the form, if I use the full > location name. If I use the Like function however, I get all the records. > Any ideas why, since I'd like to use a combo to select location. Dale, You're correct about the spaces (null strings) which are in fact trailing some of the location strings. For some reason though, my Trim (or RTrim) would not work on these strings(??). It did work however for removing leading spaces. I had a few of those throughout the project. No luck with trailing spaces - does not make sense. In process of trying the Remove function, however I do not see any character at the end of my Location names. The only way to tell is to highlight the location in table, then you see the extra space at the end. Before you brought this up, I did a StrComp between the two, and that showed the difference. > Just a guess. Is there a chance that the data you are appending from a web > site may contain some leading spaces? Try updating your [Location] field. [quoted text clipped - 10 lines] > > location name. If I use the Like function however, I get all the records. > > Any ideas why, since I'd like to use a combo to select location. Hi, here is someone else who recently had the same problem. Marshall Barton provided the solution I have a few simple character strings that won't "Trim", leaving me to believe that there are some kind of un-printable trailing characters of some sort. I need some sort of dump function like: To find what the extra characters are, do the following In the immediate window: For k = 1 To Len(s):Debug.Print Hex(Asc(Mid(s,k)));:Next Marshall Barton The "offending" characters are hex A0, which is ASCI 160, which is a "space". Why in the world wouldn't the Trim function trim those off? Trim() trims off ASCII 32 (hex 20). You could run a Query using Replace: UPDATE table SET fieldname = Replace([fieldname], Chr(160), "") It's possible that the user who inserted the text did so by copying and pasting from Word or from Wordpad, and that the original document had "non breaking spaces" - I believe that I recall that A0 is used for a blank that Word won't wrap at the end of a line. Jeanette Cunningham > Dale, > [quoted text clipped - 34 lines] >> > records. >> > Any ideas why, since I'd like to use a combo to select location. Thank you Jeanette, that did it! Thanks everyone for your help. > Hi, > here is someone else who recently had the same problem. [quoted text clipped - 65 lines] > >> > records. > >> > Any ideas why, since I'd like to use a combo to select location. As an addition to this: I had strings with multiple trailing spaces, which were not removed by the Replace(strExample, Chr(160), "") function. However I was able to Trim them all out after the Chr(160) was removed (what a BAD character!!!). Thanks again, > Thank you Jeanette, that did it! Thanks everyone for your help. > [quoted text clipped - 67 lines] > > >> > records. > > >> > Any ideas why, since I'd like to use a combo to select location. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.