Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-20-2019, 11:52 PM   #1
petko
Newly Registered User
 
Join Date: Jun 2007
Location: Sopron, Hungary
Posts: 68
Thanks: 5
Thanked 0 Times in 0 Posts
petko is on a distinguished road
Unwanted CR LF character when using Chr(26)

Hi,

I'm using a form to collect the actual bank transfer data from the user and at submit the code prepares the requested string that is converted to a text file, that will be imported into the bank terminal.
After having prepared the string I write it into a text file:

Open TxtFileName For Output As #FileNum
Print #FileNum, StrBankTransfer
Close #FileNum

After this I have an unwanted CR LF character at the end of the text file causing error during importing it into the bank terminal . These are definitely not comming from the string but are added during the Print command.

Do you have any idea how I could avoid this or get rid of it with from code?

Appreciating any suggestion

petko

petko is offline   Reply With Quote
Old 05-21-2019, 01:27 AM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Unwanted CR LF character when using Chr(26)

I think that explain it:
Unlike the Print # statement, the Write # statement inserts commas between items and quotation marks around strings as they are written to the file. You don't have to put explicit delimiters in the list. Write # inserts a newline character, that is, a carriage return-linefeed (Chr(13) + Chr(10) ), after it has written the final character in outputlist to the file.
Source document:
https://docs.microsoft.com/en-us/off...writestatement
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 05-27-2019, 06:50 AM   #3
petko
Newly Registered User
 
Join Date: Jun 2007
Location: Sopron, Hungary
Posts: 68
Thanks: 5
Thanked 0 Times in 0 Posts
petko is on a distinguished road
Re: Unwanted CR LF character when using Chr(26)

Thanks for your suggestion. I tried to Write statement, however the only difference was that it inserted a quotation mark at the beginning and end, which is also not wanted. And the CR LF was still there that I'm trying to avoid.

I would appreciate any further ideas.

many thanks

petko

petko is offline   Reply With Quote
Old 05-27-2019, 07:00 AM   #4
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,829
Thanks: 56
Thanked 1,032 Times in 998 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Unwanted CR LF character when using Chr(26)

does your strBanklTransfer END with a control character?
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-27-2019, 09:01 AM   #5
Solo712
Newly Registered User
 
Solo712's Avatar
 
Join Date: Oct 2012
Posts: 742
Thanks: 14
Thanked 126 Times in 123 Posts
Solo712 will become famous soon enough
Re: Unwanted CR LF character when using Chr(26)

Try appending a semi-colon.
Code:
Print #FileNum, StrBankTransfer;
Best,
Jiri
__________________
Dyslexics of the world untie !

Last edited by Solo712; 05-27-2019 at 01:10 PM.
Solo712 is offline   Reply With Quote
Old 05-27-2019, 10:09 AM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,829
Thanks: 56
Thanked 1,032 Times in 998 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Unwanted CR LF character when using Chr(26)

Hmm. I just created a text file (a csv file) in a different project, and there is a vbCrLf at the end of the last row.

Are you sure that's producing an error, as it's a standard feature. If you edit it out manually does it fix it? Are you sure you aren't putting your own control character into the print line as well?
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-27-2019, 11:02 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,770
Thanks: 93
Thanked 1,729 Times in 1,600 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Unwanted CR LF character when using Chr(26)

OK, just for clarification, I can think of three different interpretations and want to clarify which one is being used. CHR$(26) is ASCII "SUB" for decimal 26 or ASCII "SYN" for octal 26, and it is the Ampersand character ("&") for hexadecimal 26.

If it happens that you really meant CHR$(26) with decimal 26, then that character represents a "substitution" marker. I.e. ASCII SUB is standing in place of a character that cannot be represented by some other ASCII character. I don't know enough about VBA "WRITE" statements to be sure because I always used PRINT statements, which behave differently.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-27-2019, 01:19 PM   #8
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,829
Thanks: 56
Thanked 1,032 Times in 998 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Unwanted CR LF character when using Chr(26)

I also meant to comment on chr(26)

vbCrLf (Carriage Return - LineFeed) is chr(13) And chr(10). (which is what you described) - and I assumed your reference to chr(26) was just a mistype - but are you really putting a chr(26) in your "printed" line?
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-01-2019, 02:58 AM   #9
petko
Newly Registered User
 
Join Date: Jun 2007
Location: Sopron, Hungary
Posts: 68
Thanks: 5
Thanked 0 Times in 0 Posts
petko is on a distinguished road
Re: Unwanted CR LF character when using Chr(26)

According to the bank's requirement strBankTransfer ends with Chr(26), the Substitution character.

After using Print statement the text file is created. Checking the text file with a help of an editor Notepad++ I find the Sub character at the end and following it the unwanted CR LF character that is definitely created during Print - it is not in strBankTransfer.
Having this CR LF at the end of the file the bank terminal rejects the import with error message.

As soon I delete CR LF manually from the text file the import runs fine.
petko is offline   Reply With Quote
Old 06-01-2019, 03:24 AM   #10
rpeare
Newly Registered User
 
Join Date: Sep 2016
Posts: 18
Thanks: 0
Thanked 3 Times in 2 Posts
rpeare is on a distinguished road
Re: Unwanted CR LF character when using Chr(26)

Have you tried using filesystemobject commands instead?

with filesystemobject you should be able to define your own end of line character

Code:
Sub main()

Dim f
Dim fs

Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.createtextfile(Replace(CurrentProject.Path & "\", "\\", "\") & "TEST.txt", True)

f.write "this is a test" & Chr(26)
f.write "omg now what" & Chr(26)

f.Close
Set fs = Nothing

End Sub
rpeare is offline   Reply With Quote
The Following 2 Users Say Thank You to rpeare For This Useful Post:
petko (06-03-2019), The_Doc_Man (06-03-2019)
Old 06-01-2019, 03:49 AM   #11
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,829
Thanks: 56
Thanked 1,032 Times in 998 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Unwanted CR LF character when using Chr(26)

@petko

it's hard to resovle, I imagine, since print # will always oput the crlf at the end of a line.

I can't even think you could edit the text file automatically, becuase when you print the output back, the crlf at the end will re-appear.

You might be able to do it with write, but I am not sure. Write is really used to write records, rather than text. you could probably assemble the records with a trailing crlf on all but the last, and a trailing chr(26) on the last, and then write them out to the file.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-01-2019, 05:13 AM   #12
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Unwanted CR LF character when using Chr(26)

Quote:
Originally Posted by petko View Post
..
As soon I delete CR LF manually from the text file the import runs fine.
How do you delete it, do you save the file afterwards?
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 06-01-2019, 05:29 AM   #13
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Unwanted CR LF character when using Chr(26)

I think rpeare had the solution with the Filesystemobject. Looking at the output it looks perfect.
Attached Images
File Type: jpg TestTxt.jpg (37.7 KB, 50 views)
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 06-01-2019, 08:19 AM   #14
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,829
Thanks: 56
Thanked 1,032 Times in 998 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Unwanted CR LF character when using Chr(26)

@JHB

I think you are right. I think he wanted this though

f.write "this is a test" & Chr(13) & chr(10)
f.write "this is another test" & Chr(13) & chr(10)
f.write "omg now what" & Chr(26)

So you only get chr(26) on the last one, which is why print # wasn't working.

He can do this your way, and I imagine he could also do it directly with wrte, rather than print #, but I hadn't tested it.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-01-2019, 01:48 PM   #15
rpeare
Newly Registered User
 
Join Date: Sep 2016
Posts: 18
Thanks: 0
Thanked 3 Times in 2 Posts
rpeare is on a distinguished road
Re: Unwanted CR LF character when using Chr(26)

You may be right about it being an end of file character gemma, even so filesystemobject would still work you'd just use 'writeline' for everything except the last line and 'write' for the last line

rpeare is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unwanted parameter Box John thomas Queries 3 12-06-2014 12:51 AM
Unwanted Duplicates vince_uninc Queries 1 06-29-2012 07:13 AM
How to Delete the First Character if it Equals a Specific Character mdurschlag Reports 7 03-14-2012 02:08 PM
[SOLVED] unwanted 30.12.1899 wolf54 Modules & VBA 3 05-02-2005 08:41 AM
Unwanted new record kbrooks Forms 10 05-19-2003 12:40 PM




All times are GMT -8. The time now is 03:29 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World