Simple Select Query

[Ra]

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.

 

[Jeanette Cunningham]

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

 

[Dale Fye]

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

 

[Ra]

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.

UPDATE [yourTable]
SET [Location] = TRIM([Location])

HTH
Dale

--
Don''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.

 

[Jeanette Cunningham]

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)ebug.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,

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.

UPDATE [yourTable]
SET [Location] = TRIM([Location])

HTH
Dale

--
Don''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.

 

[Ra]

Thank you Jeanette, that did it! Thanks everyone for your help.

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)ebug.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,

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.

UPDATE [yourTable]
SET [Location] = TRIM([Location])

HTH
Dale

--
Don''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.

 

[Ra]

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.

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)ebug.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,

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.

UPDATE [yourTable]
SET [Location] = TRIM([Location])

HTH
Dale

--
Don''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.