losing trailing spaces

G

Guest

I have a query which compares two text fields for an exact match (I thought).
I find that it finds differences in the text strings if there is a leading
space in the string, BUT not a trailing space in the string.

HELP! I need to know whether the text fields are an exact match or not.
Novice experience in SQL...

Here is my SQL statement:
SELECT DS.FROM, DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS LEFT JOIN FS ON DS.FROM = FS.[Table of Requirement Keys]
WHERE (((FS.[Table of Requirement Keys]) Is Null));
 
G

Guest

Huh? You are joining something to something and looking for nothing (NULL)?

Also DS.FROM need brackets like this DS.[FROM] as from is a
reserved word.

Try this ---
SELECT DS.[FROM], DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS INNER JOIN FS ON Trim(DS.[FROM]) = Trim(FS.[Table of Requirement
Keys]);

You will not be able to see this query in design view due to what I did to
the join. It will pull up matches even if there is a difference in leading
or trailing spaces.
 
G

Guest

Hi, This query works to identify if the strings are not identical (list where
there isn't a match in the join. Unfortunately, it is not considering a
trailing space character so it says "ABCD " matches "ABCD" and this is not a
valid result. It works correctly for " ABCD" doesn't match "ABCD" and "AB
CD" doesn't match "ABCD" but...

The join is a list all from parent and only those that match from child,
then list only those where there isn't a match.

KARL DEWEY said:
Huh? You are joining something to something and looking for nothing (NULL)?

Also DS.FROM need brackets like this DS.[FROM] as from is a
reserved word.

Try this ---
SELECT DS.[FROM], DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS INNER JOIN FS ON Trim(DS.[FROM]) = Trim(FS.[Table of Requirement
Keys]);

You will not be able to see this query in design view due to what I did to
the join. It will pull up matches even if there is a difference in leading
or trailing spaces.

CADRAQA said:
I have a query which compares two text fields for an exact match (I thought).
I find that it finds differences in the text strings if there is a leading
space in the string, BUT not a trailing space in the string.

HELP! I need to know whether the text fields are an exact match or not.
Novice experience in SQL...

Here is my SQL statement:
SELECT DS.FROM, DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS LEFT JOIN FS ON DS.FROM = FS.[Table of Requirement Keys]
WHERE (((FS.[Table of Requirement Keys]) Is Null));
 
G

Guest

Hi, I guess I should explain. The environment we work in considers a "space"
to be just another charactor in a label string. We need to identify where
there are disconnects in the label strings. I do not know or understand why
or how to correct the query so it doesn't ignore any trailing spaces in the
field. The label strings are imported into access tables so the field does
actually have values like "ABCD " or "ABCD "

CADRAQA said:
Hi, This query works to identify if the strings are not identical (list where
there isn't a match in the join. Unfortunately, it is not considering a
trailing space character so it says "ABCD " matches "ABCD" and this is not a
valid result. It works correctly for " ABCD" doesn't match "ABCD" and "AB
CD" doesn't match "ABCD" but...

The join is a list all from parent and only those that match from child,
then list only those where there isn't a match.

KARL DEWEY said:
Huh? You are joining something to something and looking for nothing (NULL)?

Also DS.FROM need brackets like this DS.[FROM] as from is a
reserved word.

Try this ---
SELECT DS.[FROM], DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS INNER JOIN FS ON Trim(DS.[FROM]) = Trim(FS.[Table of Requirement
Keys]);

You will not be able to see this query in design view due to what I did to
the join. It will pull up matches even if there is a difference in leading
or trailing spaces.

CADRAQA said:
I have a query which compares two text fields for an exact match (I thought).
I find that it finds differences in the text strings if there is a leading
space in the string, BUT not a trailing space in the string.

HELP! I need to know whether the text fields are an exact match or not.
Novice experience in SQL...

Here is my SQL statement:
SELECT DS.FROM, DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS LEFT JOIN FS ON DS.FROM = FS.[Table of Requirement Keys]
WHERE (((FS.[Table of Requirement Keys]) Is Null));
 
G

Guest

You might try InStr on the fields.
In the design view add FindSpace: InStr([Table1].[Field1]," ")
In criteria add <> InStr([Table2].[Field1]," ")

This finds the first space. Next add another field like FindSpace:
InStr([Table1].[Field1]," ", InStr([Table1].[Field1]," ")+1)
In criteria add <> InStr([Table2].[Field1]," ",InStr([Table2].[Field1],"
")+1)
This starts looking for second space beyond the first one.

CADRAQA said:
Hi, I guess I should explain. The environment we work in considers a "space"
to be just another charactor in a label string. We need to identify where
there are disconnects in the label strings. I do not know or understand why
or how to correct the query so it doesn't ignore any trailing spaces in the
field. The label strings are imported into access tables so the field does
actually have values like "ABCD " or "ABCD "

CADRAQA said:
Hi, This query works to identify if the strings are not identical (list where
there isn't a match in the join. Unfortunately, it is not considering a
trailing space character so it says "ABCD " matches "ABCD" and this is not a
valid result. It works correctly for " ABCD" doesn't match "ABCD" and "AB
CD" doesn't match "ABCD" but...

The join is a list all from parent and only those that match from child,
then list only those where there isn't a match.

KARL DEWEY said:
Huh? You are joining something to something and looking for nothing (NULL)?

Also DS.FROM need brackets like this DS.[FROM] as from is a
reserved word.

Try this ---
SELECT DS.[FROM], DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS INNER JOIN FS ON Trim(DS.[FROM]) = Trim(FS.[Table of Requirement
Keys]);

You will not be able to see this query in design view due to what I did to
the join. It will pull up matches even if there is a difference in leading
or trailing spaces.

:

I have a query which compares two text fields for an exact match (I thought).
I find that it finds differences in the text strings if there is a leading
space in the string, BUT not a trailing space in the string.

HELP! I need to know whether the text fields are an exact match or not.
Novice experience in SQL...

Here is my SQL statement:
SELECT DS.FROM, DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS LEFT JOIN FS ON DS.FROM = FS.[Table of Requirement Keys]
WHERE (((FS.[Table of Requirement Keys]) Is Null));
 
V

Van T. Dinh

You may be able to add another criterion requiring that the lengths (using
VBA Len() function) of the 2 Field values are not equal ...

--
HTH
Van T. Dinh
MVP (Access)



CADRAQA said:
Hi, I guess I should explain. The environment we work in considers a
"space"
to be just another charactor in a label string. We need to identify where
there are disconnects in the label strings. I do not know or understand
why
or how to correct the query so it doesn't ignore any trailing spaces in
the
field. The label strings are imported into access tables so the field
does
actually have values like "ABCD " or "ABCD "

CADRAQA said:
Hi, This query works to identify if the strings are not identical (list
where
there isn't a match in the join. Unfortunately, it is not considering a
trailing space character so it says "ABCD " matches "ABCD" and this is
not a
valid result. It works correctly for " ABCD" doesn't match "ABCD" and
"AB
CD" doesn't match "ABCD" but...

The join is a list all from parent and only those that match from child,
then list only those where there isn't a match.

KARL DEWEY said:
Huh? You are joining something to something and looking for nothing
(NULL)?

Also DS.FROM need brackets like this DS.[FROM] as from is a
reserved word.

Try this ---
SELECT DS.[FROM], DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS INNER JOIN FS ON Trim(DS.[FROM]) = Trim(FS.[Table of
Requirement
Keys]);

You will not be able to see this query in design view due to what I did
to
the join. It will pull up matches even if there is a difference in
leading
or trailing spaces.

:

I have a query which compares two text fields for an exact match (I
thought).
I find that it finds differences in the text strings if there is a
leading
space in the string, BUT not a trailing space in the string.

HELP! I need to know whether the text fields are an exact match or
not.
Novice experience in SQL...

Here is my SQL statement:
SELECT DS.FROM, DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS LEFT JOIN FS ON DS.FROM = FS.[Table of Requirement Keys]
WHERE (((FS.[Table of Requirement Keys]) Is Null));
 
G

Gary Walter

I agree that following makes sense

SELECT
DS.[FROM],
DS.[Table of Requirement Keys],
DS.[Present Status]
FROM
DS LEFT JOIN FS
ON
DS.[FROM] = FS.[Table of Requirement Keys]
AND
LEN(DS.FROM]) = LEN(FS.[Table of Requirement Keys])
WHERE
(((FS.[Table of Requirement Keys]) Is Null));

but on an *untested hunch*, I might not trust
parser to do what "I say" and end up blowing
up outer join logic.....

to be sure, I might compute lengths in 2 preliminary
queries, then outer join those prelim queries on the text
fields and the computed lengths of the text fields...

As always, I could be wrong...



Van T. Dinh said:
You may be able to add another criterion requiring that the lengths (using
VBA Len() function) of the 2 Field values are not equal ...

--
HTH
Van T. Dinh
MVP (Access)



CADRAQA said:
Hi, I guess I should explain. The environment we work in considers a
"space"
to be just another charactor in a label string. We need to identify
where
there are disconnects in the label strings. I do not know or understand
why
or how to correct the query so it doesn't ignore any trailing spaces in
the
field. The label strings are imported into access tables so the field
does
actually have values like "ABCD " or "ABCD "

CADRAQA said:
Hi, This query works to identify if the strings are not identical (list
where
there isn't a match in the join. Unfortunately, it is not considering a
trailing space character so it says "ABCD " matches "ABCD" and this is
not a
valid result. It works correctly for " ABCD" doesn't match "ABCD" and
"AB
CD" doesn't match "ABCD" but...

The join is a list all from parent and only those that match from child,
then list only those where there isn't a match.

:

Huh? You are joining something to something and looking for nothing
(NULL)?

Also DS.FROM need brackets like this DS.[FROM] as from is a
reserved word.

Try this ---
SELECT DS.[FROM], DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS INNER JOIN FS ON Trim(DS.[FROM]) = Trim(FS.[Table of
Requirement
Keys]);

You will not be able to see this query in design view due to what I
did to
the join. It will pull up matches even if there is a difference in
leading
or trailing spaces.

:

I have a query which compares two text fields for an exact match (I
thought).
I find that it finds differences in the text strings if there is a
leading
space in the string, BUT not a trailing space in the string.

HELP! I need to know whether the text fields are an exact match or
not.
Novice experience in SQL...

Here is my SQL statement:
SELECT DS.FROM, DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS LEFT JOIN FS ON DS.FROM = FS.[Table of Requirement Keys]
WHERE (((FS.[Table of Requirement Keys]) Is Null));
 
J

John Spencer

Are you sure the trailing spaces are there after the import? Have you
checked the length to see?

Using a query like the following should return records if there are trailing
spaces. If it returns, no records then the import process is dropping the
trailing spaces.
SELECT DS.[Table of Requirement Keys]
FROM FS
WHERE Len([Table of Requirement Keys]) <> Len(RTrim([Table of Requirement
Keys]))

CADRAQA said:
Hi, I guess I should explain. The environment we work in considers a
"space"
to be just another charactor in a label string. We need to identify where
there are disconnects in the label strings. I do not know or understand
why
or how to correct the query so it doesn't ignore any trailing spaces in
the
field. The label strings are imported into access tables so the field
does
actually have values like "ABCD " or "ABCD "

CADRAQA said:
Hi, This query works to identify if the strings are not identical (list
where
there isn't a match in the join. Unfortunately, it is not considering a
trailing space character so it says "ABCD " matches "ABCD" and this is
not a
valid result. It works correctly for " ABCD" doesn't match "ABCD" and
"AB
CD" doesn't match "ABCD" but...

The join is a list all from parent and only those that match from child,
then list only those where there isn't a match.

KARL DEWEY said:
Huh? You are joining something to something and looking for nothing
(NULL)?

Also DS.FROM need brackets like this DS.[FROM] as from is a
reserved word.

Try this ---
SELECT DS.[FROM], DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS INNER JOIN FS ON Trim(DS.[FROM]) = Trim(FS.[Table of
Requirement
Keys]);

You will not be able to see this query in design view due to what I did
to
the join. It will pull up matches even if there is a difference in
leading
or trailing spaces.

:

I have a query which compares two text fields for an exact match (I
thought).
I find that it finds differences in the text strings if there is a
leading
space in the string, BUT not a trailing space in the string.

HELP! I need to know whether the text fields are an exact match or
not.
Novice experience in SQL...

Here is my SQL statement:
SELECT DS.FROM, DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS LEFT JOIN FS ON DS.FROM = FS.[Table of Requirement Keys]
WHERE (((FS.[Table of Requirement Keys]) Is Null));
 
G

Guest

THANKS TO ALL (Karl, Van, and Gary)!

I will try tomorrow and let you know. Thanks again!

John Spencer said:
Are you sure the trailing spaces are there after the import? Have you
checked the length to see?

Using a query like the following should return records if there are trailing
spaces. If it returns, no records then the import process is dropping the
trailing spaces.
SELECT DS.[Table of Requirement Keys]
FROM FS
WHERE Len([Table of Requirement Keys]) <> Len(RTrim([Table of Requirement
Keys]))

CADRAQA said:
Hi, I guess I should explain. The environment we work in considers a
"space"
to be just another charactor in a label string. We need to identify where
there are disconnects in the label strings. I do not know or understand
why
or how to correct the query so it doesn't ignore any trailing spaces in
the
field. The label strings are imported into access tables so the field
does
actually have values like "ABCD " or "ABCD "

CADRAQA said:
Hi, This query works to identify if the strings are not identical (list
where
there isn't a match in the join. Unfortunately, it is not considering a
trailing space character so it says "ABCD " matches "ABCD" and this is
not a
valid result. It works correctly for " ABCD" doesn't match "ABCD" and
"AB
CD" doesn't match "ABCD" but...

The join is a list all from parent and only those that match from child,
then list only those where there isn't a match.

:

Huh? You are joining something to something and looking for nothing
(NULL)?

Also DS.FROM need brackets like this DS.[FROM] as from is a
reserved word.

Try this ---
SELECT DS.[FROM], DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS INNER JOIN FS ON Trim(DS.[FROM]) = Trim(FS.[Table of
Requirement
Keys]);

You will not be able to see this query in design view due to what I did
to
the join. It will pull up matches even if there is a difference in
leading
or trailing spaces.

:

I have a query which compares two text fields for an exact match (I
thought).
I find that it finds differences in the text strings if there is a
leading
space in the string, BUT not a trailing space in the string.

HELP! I need to know whether the text fields are an exact match or
not.
Novice experience in SQL...

Here is my SQL statement:
SELECT DS.FROM, DS.[Table of Requirement Keys], DS.[Present Status]
FROM DS LEFT JOIN FS ON DS.FROM = FS.[Table of Requirement Keys]
WHERE (((FS.[Table of Requirement Keys]) Is Null));
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top