If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Imported Text fields don't Join
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 |
#2
|
|||
|
|||
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. "Pele" wrote in message ... 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 |
#3
|
|||
|
|||
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. "Pele" wrote in message ... 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 . |
#4
|
|||
|
|||
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" wrote in message ... 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. "Pele" wrote in message ... 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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking text boxes | Volunteer Mom | Publisher | 7 | November 12th, 2008 01:29 AM |
textbox to normal text | Jack Sons | New Users | 16 | December 5th, 2004 03:44 PM |
Vertical Text Orientation | Paul Anderson [MSFT] | Visio | 0 | May 11th, 2004 05:16 PM |