Imported Text fields don't Join

  • Thread starter Thread starter Pele
  • Start date Start date
P

Pele

I recently used the Import Wizard to import two separate
tables - one was a text file and the other was an Excel
file.

Both tables had text fields in them.

I then tried to join two text fields from these two tables
in a query but it didn't work i.e. the text fields were
not recongizing the values from each other even though
they were exactly the same.

It's like Access does not realize that the two fields have
the same format.

I do need to keep the table sources the way they are (i.e.
Text file and Excel file), so can anybody tell me what to
do in Access to ensure that these text fields can
recognize that they have the same formatting.

Pele
 
If two text fields look identical, but Access does not treat them as
identical, one possible difference that would not be immediately visible is
trailing spaces. "Some text" is not equal to "Some text ". You could try
removing any trailing spaces with an update query, something like: "UPDATE
YourTable SET YourField = Trim$([YourField])"

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
This seems to be the problem but I haven't been able to
match the Text field from Excel with the text field from
the text file. There were various functions like TRIM,
TRIM$, LTRIM$, RTRIM and I am still trying to figure out
what they do. Access Help did not show any of Ltrim and
Rtim and what they do.

Pele
-----Original Message-----
If two text fields look identical, but Access does not treat them as
identical, one possible difference that would not be immediately visible is
trailing spaces. "Some text" is not equal to "Some text ". You could try
removing any trailing spaces with an update query, something like: "UPDATE
YourTable SET YourField = Trim$([YourField])"

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E- mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e- mail, you'll find
a useable e-mail address at the URL above.


I recently used the Import Wizard to import two separate
tables - one was a text file and the other was an Excel
file.

Both tables had text fields in them.

I then tried to join two text fields from these two tables
in a query but it didn't work i.e. the text fields were
not recongizing the values from each other even though
they were exactly the same.

It's like Access does not realize that the two fields have
the same format.

I do need to keep the table sources the way they are (i.e.
Text file and Excel file), so can anybody tell me what to
do in Access to ensure that these text fields can
recognize that they have the same formatting.

Pele
Click to expand...


.
Click to expand...
 
LTrim trims spaces from the left of a string, RTrim trims spaces from the
right of a string, and Trim does both. Examples in the Immediate window ...

? "*" & ltrim(" some text ") & "*"
*some text *
? "*" & rtrim (" some text ") & "*"
* some text*
? "*" & trim(" some text ") & "*"
*some text*

These are VBA functions, so to find them in the help system, start from the
VBA editor window rather than the main Access window.


--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Pele said:
This seems to be the problem but I haven't been able to
match the Text field from Excel with the text field from
the text file. There were various functions like TRIM,
TRIM$, LTRIM$, RTRIM and I am still trying to figure out
what they do. Access Help did not show any of Ltrim and
Rtim and what they do.

Pele
-----Original Message-----
If two text fields look identical, but Access does not treat them as
identical, one possible difference that would not be immediately visible is
trailing spaces. "Some text" is not equal to "Some text ". You could try
removing any trailing spaces with an update query, something like: "UPDATE
YourTable SET YourField = Trim$([YourField])"

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E- mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e- mail, you'll find
a useable e-mail address at the URL above.


I recently used the Import Wizard to import two separate
tables - one was a text file and the other was an Excel
file.

Both tables had text fields in them.

I then tried to join two text fields from these two tables
in a query but it didn't work i.e. the text fields were
not recongizing the values from each other even though
they were exactly the same.

It's like Access does not realize that the two fields have
the same format.

I do need to keep the table sources the way they are (i.e.
Text file and Excel file), so can anybody tell me what to
do in Access to ensure that these text fields can
recognize that they have the same formatting.

Pele
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top