I'm constantly required to export data from Access to Excel for formatting and find it extremely difficult to remove the Chr(10) and Chr(13) codes from the imported data. A search and replace in Excel doesn't recognize the characters and I can find no 'quick' way of fixing this problem, is there a way of making sure the characters don't come across during an export (they appear as boxes in Excel) or is there code that I can use in Excel to search for and remove the carriage return characters?
2  12916  PEB 1,418
Recognized Expert Top Contributor
If in your data in Access you have those symbols you can use the function:
Replace(My_fiel d, chr(13), "") and everywhere in the field CHr(13) will be replaced with ""
But maybe this problem appears while exporting data to Excel? How do you export your data to Excel? Because when I use directly xls format for Excel 5-7 I don't have any problems! It's like in Access!
Thanks, great tip - both of you, for telling me what the character numbers were that were replacing those carriage returns in the Access memo field, and for suggesting the replace() function.
I used the following nested replace in my query, it even worked around the results of my iif() function - to replace the multi-line characters with a "," and " " for a much smoother export.
eg: A query to pull the correct address from a record now contains this:
Mailing address: Replace(Replace (IIf[Address] Is Null,[address2],[address]),Chr(13),","), Chr(10)," ")
There are probably a number of permutations that would have worked in this situation. (like putting the "replace" within the iif - but I'd have had to do it for both fields then)! So thanks for the tip.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Alin Popovici |
last post by:
Hi!
I have this problem. I am sending as a parameter for a
webmethod a string containing '\r\n' sequences. For some
reason, when I debug my webmethod, the paramter is
received with the carriage return characters ('\r')
stripped. Can someone try this by creating a simple
webmethod that takes a string as a parameter and returns
the same string back. On the client, create a proxy, call
the webmethod and pass: "bla\r\nbla". If you put a...
|
by: Steven |
last post by:
i need to force a carriage return with a textarea field at X number of
characters. anybody know how to do this?
tks
|
by: Alin Popovici |
last post by:
Hi!
I have this problem. I am sending as a parameter for a
webmethod a string containing '\r\n' sequences. For some
reason, when I debug my webmethod, the paramter is
received with the carriage return characters ('\r')
stripped. Can someone try this by creating a simple
webmethod that takes a string as a parameter and returns
the same string back. On the client, create a proxy, call
the webmethod and pass: "bla\r\nbla". If you put a...
|
by: Laura D |
last post by:
How can I identify a carriage return in C++?
\r, \f, \0, \n, \t does not work. I have also tried !isprint(ch), iscntrl(ch), isspace(ch), etc....with no luck!
I even poked around in the MSDN and found some code that MS claims will save a file in unix format and I cut and pasted into my program(and made changes to suit):
.....
char ch;
char temp="\0";
//Open the file for reading in binarymode.
ifstream fp_read(filename.c_str(),...
|
by: Matt Mercer |
last post by:
Hi all,
I am having a frustration problem, and I have read about 25 newsgroup
postings that do not have a satisfying answer :)
The problem appears to be common where carriage returns are lost when
pulling data from an SQL database. The thing that frustrates me the
most, is that when I use Enterprise Manager, the carriage returns ARE
THERE. It looks fine until I pull it out.
| | |
by: J.Marsch |
last post by:
I am having a problem in which ASP.Net web services are corrupting my data.
I know that my problem is related to the standard way of encoding carriage
return linefeeds, so I need to figure out how to correctly encode them.
I am trying to return XML strings that were generated by calling
DataTable.WriteXml() from my web service, and then using DataTable.ReadXml
to deserialize the data. The problem is that some of the data in the...
|
by: no_spam_for_gman |
last post by:
Hi,
I have been using the db2 export function for quite sometimes.
Everytime I want to use it I have to remove every carriage return from
my SQL statement and have it all on one line as shown in the following
simplistic example:
db2 export to myfile.txt of del select a,b,c from staff
This can be time consuming when you have big SQL joining many tables
|
by: Ellen |
last post by:
Hi All -
I have some data that I export from Access to a pipe-delimited text
file. Unfortunately, the text file always has a carriage return symbol
at the end. I need to export this file frequently and send to a system
that cannot accept this character at the end of the file. I am looking
for a way, perhaps with VB code, to automate this process.
I found this code on another Google Group, but I can't get it to work.
|
by: Bobby |
last post by:
Hi
I'm trying to export some data from an Access table to Sage Line 50
using VBA. It works fine, except that very occasionally one of the
fields contains a carriage return. If I step through my code in debug,
it handles the carriage return by replacing it with a square in the
middle of the text.
I need a way in Access of identifying the carriage return and
replacing it with a space. Is there a function I could use to do this?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
