Hi all,
I have a problem with an Insert Into statement in a module in Access 2007.
I import data from another database that has poor reporting functionality and use Access to automate an export in the format our users demand. Much of the data is of a global nature and as such, the original db has many non-standard characters such as é etc.
I have no problem importing this data and keeping the original characters, but it is often nested and I use a script to flatten it so that all the nested data appears in a single cell in another table delimited by the | character, which is then exported to Excel.
For standard keyboard characters the script works fine, but I've encountered a problem with the text "Muséum national d'Histoire naturelle - Département des Milieux et Peuplements Aquatiques".
When the script gets to this line it doesn't append the data to the new table, it just leaves it blank and moves to the next record. All other records work fine.
I have debugged in the immediate window and I find that the variable I have declared is actually holding the data, but that's as far as it gets, it won't insert it into the other table. I should point out that the field type for both the originating table and the table the data is pasted into are memo. I know memo is buggy, but I have no choice as the length of the string in the original db can be longer than 255 characters and the users need the full info.
I have tried:
1. Changing the variable type from string to variant. No response.
2. Adding " to each end in the hope that it might deal with it as a fixed string. No luck.
3. Using the Left function as I thought it might have something to do with memo data types and I have seen some solutions for other memo problems not strictly like my problem that use the Left function to trick Access.
I haven't pasted the code as it is working fine normally, just bugging with unusual data, so I don't think the problem is there, more with the way Access handles the data.
Any help would be greatly appreciated.
Kind regards,
Michael
I have a problem with an Insert Into statement in a module in Access 2007.
I import data from another database that has poor reporting functionality and use Access to automate an export in the format our users demand. Much of the data is of a global nature and as such, the original db has many non-standard characters such as é etc.
I have no problem importing this data and keeping the original characters, but it is often nested and I use a script to flatten it so that all the nested data appears in a single cell in another table delimited by the | character, which is then exported to Excel.
For standard keyboard characters the script works fine, but I've encountered a problem with the text "Muséum national d'Histoire naturelle - Département des Milieux et Peuplements Aquatiques".
When the script gets to this line it doesn't append the data to the new table, it just leaves it blank and moves to the next record. All other records work fine.
I have debugged in the immediate window and I find that the variable I have declared is actually holding the data, but that's as far as it gets, it won't insert it into the other table. I should point out that the field type for both the originating table and the table the data is pasted into are memo. I know memo is buggy, but I have no choice as the length of the string in the original db can be longer than 255 characters and the users need the full info.
I have tried:
1. Changing the variable type from string to variant. No response.
2. Adding " to each end in the hope that it might deal with it as a fixed string. No luck.
3. Using the Left function as I thought it might have something to do with memo data types and I have seen some solutions for other memo problems not strictly like my problem that use the Left function to trick Access.
I haven't pasted the code as it is working fine normally, just bugging with unusual data, so I don't think the problem is there, more with the way Access handles the data.
Any help would be greatly appreciated.
Kind regards,
Michael
