Hi all,
Another first time poster. I read all the above and I'm just not getting it. I have a similar situation, but in my case I want to preserve the carriage returns and they are not moving cleanly between the apps. They are within a string of text and the text needs to be preserved. I import a spreadsheet from Excel to Access (running in 2007 but db & sheet are still in 2000 format). When I import into Access the carriage returns aren't coming through properly and appear as squares. If I manually edit them (Shift-F2, Ctrl-Enter) to make a proper carriage return they "magically" turn into a proper carriage return, only AFTER I add my own carriage return.
Seems rather stupid that this character cannot properly import/export between Excel and Access. Note that when exporting to Excel from Access, the same thing happens - proper carriage returns in Access turn into squares in Excel.
Any help would be greatly appreciated!
I'd like to do one of two things:
1. (preferred) do a straight replace on this funky code, not sure what to use though (trouble searching for it)
2. (if not possible) replace the carriage return in Excel with a placeholder, which I then convert into a proper carriage return in Access.
I want to do this with a query, don't understand VBA enough to do it this way. It looks like the Replace function will do the trick, but I'm just not finding enough explanation about the pieces to understand it.
Found: Replace («stringexpr», «find», «replace», «start», «count», «compare»)
So... preference 1 - Replace (string expression, Chr(13) & Chr(10), Chr(13) & Chr(10))
Or... preference 2 - Replace (string expression, "|HRT|", Chr(13) & Chr(10))
I just don't get string expression - is that supposed to be the table name, field name, type of string? Looks like it's type of string, but don't understand how it relates? Sorry, really a newbie here. Doesn't seem like I would need start, count or compare for this replace, not sure though.
Search problem: I can't even figure out how to create the proper criteria for searching for Chr(13) & Chr(10) within a larger text string. Trying in a select query '* Chr(13) & Chr(10) *' is bad syntax and '* & Chr(13) & Chr(10) & *' turns into ' "*" & Chr(13) & Chr(10) & "*" '.
More info:
Table name = Upload to T_ECCN Master
Field name = Notes
Field type = Memo
Search for, either 1 = assuming Chr(13) & Chr(10), but since it's not displaying properly not sure
or 2 = my placeholder - "|HRT|"
Replace with = Chr(13) & Chr(10), with leading and trailing text remaining intact
Another first time poster. I read all the above and I'm just not getting it. I have a similar situation, but in my case I want to preserve the carriage returns and they are not moving cleanly between the apps. They are within a string of text and the text needs to be preserved. I import a spreadsheet from Excel to Access (running in 2007 but db & sheet are still in 2000 format). When I import into Access the carriage returns aren't coming through properly and appear as squares. If I manually edit them (Shift-F2, Ctrl-Enter) to make a proper carriage return they "magically" turn into a proper carriage return, only AFTER I add my own carriage return.
Seems rather stupid that this character cannot properly import/export between Excel and Access. Note that when exporting to Excel from Access, the same thing happens - proper carriage returns in Access turn into squares in Excel.
Any help would be greatly appreciated!
I'd like to do one of two things:
1. (preferred) do a straight replace on this funky code, not sure what to use though (trouble searching for it)
2. (if not possible) replace the carriage return in Excel with a placeholder, which I then convert into a proper carriage return in Access.
I want to do this with a query, don't understand VBA enough to do it this way. It looks like the Replace function will do the trick, but I'm just not finding enough explanation about the pieces to understand it.
Found: Replace («stringexpr», «find», «replace», «start», «count», «compare»)
So... preference 1 - Replace (string expression, Chr(13) & Chr(10), Chr(13) & Chr(10))
Or... preference 2 - Replace (string expression, "|HRT|", Chr(13) & Chr(10))
I just don't get string expression - is that supposed to be the table name, field name, type of string? Looks like it's type of string, but don't understand how it relates? Sorry, really a newbie here. Doesn't seem like I would need start, count or compare for this replace, not sure though.
Search problem: I can't even figure out how to create the proper criteria for searching for Chr(13) & Chr(10) within a larger text string. Trying in a select query '* Chr(13) & Chr(10) *' is bad syntax and '* & Chr(13) & Chr(10) & *' turns into ' "*" & Chr(13) & Chr(10) & "*" '.
More info:
Table name = Upload to T_ECCN Master
Field name = Notes
Field type = Memo
Search for, either 1 = assuming Chr(13) & Chr(10), but since it's not displaying properly not sure
or 2 = my placeholder - "|HRT|"
Replace with = Chr(13) & Chr(10), with leading and trailing text remaining intact