Pipe "|" at End of Last Field

[golfnut324]

Brand new to Access. Thanks for the help and sorry for the simplistic question.

I need to export a query to a pipe delimited text file. I have that part down however I need the text file to show a pipe at the end of the last field. What is the best way to do this? Is there a way to build an expression to add the pipe to the last field? Or should I creat a table with one field, pipe, and incorporate that to my query.

A little bit lost here, thanks again for your help.

Craig

 

[ajetrumpet]

I would BET THE BEST WAY TO DO IT WOULD BE TO CREATE THE QUERY ONCE MORE, OR YOU CAN JUST MODIFY IT AS IS, AND CHANGE THE LAST FIELD OF THE QUERY TO THIS:

TABLE.FIELD & CHR(5) AS [NEW FIELD NAME]

THAT WILL GIVE YOU A PIPE AT THE END OF THE LAST FIELD, BUT IT IS PART OF THE FIELD AS WELL, SO IT SHOULD SHOW UP AT EXPORT AS WELL, BUT AS A PIECE OF TEXT IN THE EXACT SAME PLACE AS IF THE DELIMITER WAS THERE TOO.

 

[golfnut324]

Thanks for the help with that Adam. I'm sorry to say that I need very basic instruction on entering that statement. Please tell me exactly where and how to enter that into my query if the query name is "ATT_NM_UNIQUE" and there is currently one table named "ATTRIBUTE" in the query.

I'm just beginning to scratch the surface here on Access. Thanks for your patience and help!

Craig

 

[ajetrumpet]

what is the name of the last field that in your SELECT statement at the beginning of the query's SQL? If you dont' know how to get to the query's SQL view, once you have the query opened and in front of you, click on VIEW > SQL VIEW. Tell me the last field that is listed before you get to the word "FROM" in the long statement that you see. It should say something like:

ATTRIBUTE."some word is here"

WHAT IS THAT WORD?

 

[golfnut324]

what is the name of the last field that in your SELECT statement at the beginning of the query's SQL? If you dont' know how to get to the query's SQL view, once you have the query opened and in front of you, click on VIEW > SQL VIEW. Tell me the last field that is listed before you get to the word "FROM" in the long statement that you see. It should say something like:

ATTRIBUTE."some word is here"

WHAT IS THAT WORD?

Here is the entire sql view:

SELECT ATTRIBUTE.ATT_NM
FROM ATTRIBUTE
GROUP BY ATTRIBUTE.ATT_NM
ORDER BY ATTRIBUTE.ATT_NM;

I do have it set up to return unique values in the "ATT_NM" field from the "ATTRIBUTE" table. "ATT_NM" is the one and only field in this query. I have 6 other queries with other fields, but I'm sure I can duplicate the procedure there.

Thank you very much for the assistance.

Craig

 

[ajetrumpet]

try this Craig:

SELECT ATTRIBUTE.ATT_NM & CHR(5) AS ATT_NM
FROM ATTRIBUTE
GROUP BY ATTRIBUTE.ATT_NM
ORDER BY ATTRIBUTE.ATT_NM;

 

[golfnut324]

Great News! I entered the statement as instructed and when exported as text file, then opened in text editor, the result came back with alot of extra "stuff" (maybe ascii stuff?). So I swapped "CHR (5)" with just a ""|"" and it exported clean with the pipe at the end of the field.

Thanks Adam for hanging with me on this. I wish I could figure the best way to get started understanding sql. Too old to go to school so I usually look for a good book.

Thanks again for your help.

Craig

 

[ajetrumpet]

that end result is really embarrasing. good luck with it.