How to update query memo field

[njem]

I have an old db that I need to import a long notes field from. My new
table is existing. I have managed to get the notes from the old db
into a comma separated text file with just an SSN and the notes. I can
import this into a table that is structured with just an SSN field and
a memo field. I'm trying to run an update query to fill in the notes
fields of the people already in the new table. When I run the update
query it says it's going to update x number of records, but doesn't. I
tried changing both the old and new tables to text fields as an
experiment and then it works, but of course truncated. How can I get
these long notes fields into the memo field?

Thanks,
Tom

 

[John W. Vinson]

I have an old db that I need to import a long notes field from. My new
table is existing. I have managed to get the notes from the old db
into a comma separated text file with just an SSN and the notes. I can
import this into a table that is structured with just an SSN field and
a memo field. I'm trying to run an update query to fill in the notes
fields of the people already in the new table. When I run the update
query it says it's going to update x number of records, but doesn't. I
tried changing both the old and new tables to text fields as an
experiment and then it works, but of course truncated. How can I get
these long notes fields into the memo field?

Thanks,
Tom

Please post the SQL of the query that's not working.

John W. Vinson [MVP]

 

[njem]

Please post the SQL of the query that's not working.

John W. Vinson [MVP]

UPDATE Student INNER JOIN CommLogs ON (Student.SSN = CommLogs.SSN) AND
(Student.SsnExt = CommLogs.SsnExt) SET Student.CommLog = [commlogs]!
[commlog];


Thanks,
Tom

 

[John W. Vinson]

Please post the SQL of the query that's not working.

John W. Vinson [MVP]

UPDATE Student INNER JOIN CommLogs ON (Student.SSN = CommLogs.SSN) AND
(Student.SsnExt = CommLogs.SsnExt) SET Student.CommLog = [commlogs]!
[commlog];


Thanks,
Tom

Is there a unique two-field index on SSN and SsnExt? Do the datatypes and
sizes match exactlyl between the two tables? Do you get any error messages, or
just no updating?

I'd change the ! to . in [commlog]![commlog] but other than that don't see any
problems.

John W. Vinson [MVP]

 

[njem]

Is there a unique two-field index on SSN and SsnExt? Do the datatypes and
sizes match exactlyl between the two tables? Do you get any error messages, or
just no updating?

I'd change the ! to . in [commlog]![commlog] but other than that don't see any
problems.

John W. Vinson [MVP]

In the Student table there was no individual index on SSN. It, along
with SsnExt and Lname were the primary key. I put an index (not
unique) on SSN and the update seems to be working. I figured if a
field was part of a primary key it was indexed. I guess not. I was not
getting any error. As I said it would tell me how many records it was
going to update. When they were text fields it would update. When they
were memo fields, no error, just no data updated. Now it's working.
Thanks. This is why I love being given projects in Access.

By the way, being an MVP, maybe you know how to report a bug to MS. I
spent a great deal of time on another issue that turned out to be a
bug. I finally found a KB article (269380) that described it but
claims it only applied to Office 2000. Well I have Office 2003 and
Access 2007 and had the exact same behavior. I find no place in any MS
site where I can report a bug. Do they take bug reports?

Thanks,
Tom