Converting a text field to memo Results

I am using Access database for one system, and SQL server for another system. The data gets synced between these two systems. The problem is that one of the fields in a table in Access database is a Memo field which is in double-byte format. When I read this data using .Net DataGridView in a Windows form, the text is displayed as ???. Also, when data from this field is inserted in SQL server database nvarchar(max) field, non-English characters are inserted as ???.
How can I fetch data from memo field, convert its encoding to Unicode, so that it appears correctly in SQL server database as well?
Please help!!!

I am new to access but I have a feeling that my question will probably require VBA. If there is a simpler solution I would be interested to hear it.

I have a table with two fields - Extract (which is a unique identifying number) and Narrative (which is in the memo format).
Although within each narrative record is text, much of which should be in multiple records.
The narrative goes like this

[text string] text string text string etc [text string] text string text string ect [text string] text string text string ect
(thus the memo always begins with a section enclosed in square brackets, but the square brackets occur again within the same record, but each time this occurs within the memo I want this to be the beginning of a new record).

Thus I want it to go like this:

[text string] text string text string etc
[text string] text string text string etc
[text string] text string text string etc

Thus each text string is its own record, with the Extract ID number now duplicated for each new record.

I have created my own clumsy workaround by exporting the narrative to Word and doing a find/replace where I substitute the left square bracket for paragraph break/left square bracket.
Find: [
Replace: ^p[

This almost most works for me, but I was hoping there might be a way of doing this in Access.

Any suggestions or pointers would be greatly appreciated.

Stage 2 will be to then parse the start of the memo into its own field
[text string]

Thus I will now have three fields
Extract, NewField, Narrative

But I would happy to solve stage 1 first.


I have a MS Access database application split into a front-end/back-end database. The functionality all works fine on my computer but when my client tests it various pieces of functionality don't work. This is VERY frustrating and quite difficult to troubleshoot as I'm not sure what it is that's different between the client's and my pc that is causing the code to break.

Issue #1) The button on-click event: "DoCmd.OutputTo acOutputQuery, "Talent Data", acFormatXLSX, , True" works great on my computer my when my client runs this screen from her laptop, all of the memo fields convert to something that looks like chinese characters when it is exported to Excel.

Issue #2) I have a report that has a text box with a control source set to "IIf([l2p1] Is Null,"",[L2P1] & Chr(13) & Chr(10) & [l2p1jobtitle])" which in essence lists a persons name, forces a new line and then displays their job title on the next line of the text box. If that field is null, it won't display anything. This works Great in my version but of course, on my client's computer, the text box displays as "#Name?".

I'm not positive that this is the same issue - but it sort of seems that there is a missing library or something on my client's pc. I know that it is missing the DTS.dll (Microsoft DTS runtime 1.0) file... but when I take that reference out of my copy of access, all of my functionality still works - so I don't think that's it.



I have been tasked with converting an old Electronic Trouble report (ETR) flatfile database to access 97. The old flatfile had 2 basic tables in it, ETR and LOG.

The ETR table consists of the following fields:

ETR - Text
Equip - Text
Station - Text
SSCODE - text
Date_Open - Date/Time
Time_Open - Date/Time
Opened_By - Text
Status - Text
PCN - Text
Date_CLS - Date/Time
Closed_By - Text
Problem - Memo
Fix_Action - Text
Manhours - text
Parts_Used - Text
Action - Memo

The LOG table is layed out as follows:

Subject - text
Station - Text
Date_Open - Date/Time
Time_Open - Date/Time
Initials - Text
ETR_Ref - Text
Status - text (status of ETR)
PCN_SN - Text (serial num of Equip)
Manhours - text
Problem - text
Entry - Text (Log entry)

The problem I'm having is that when user opens an ETR on a piece of equipment, not only does he have to fill out all items on form that go into ETR table but at the same time generate a Log entry that can be viewed or printed out vice having to go in and search each ETR.

On the other side, users will need to be able to make a log entry without having to open a trouble report (ETR), such as replacing expendibles, this would not require a trouble report being opened.

So they both will have some of the same information but at the same time have to be independent.
Here lies my problem on how to normalize?

Any help greatly appreciated.


Not finding an answer? Try a Google search.