Cannot insert weird characters to Access table

lukin

I have an Access table with a field that I have to insert a lot of non-alphanumeric characters to. Example:
(max([PLC00000015;VAR00001|Result;Result;*;enteredvalue],[PLC00000166;VAR00001|Result;Result])-min([PLC00000015;VAR00001|Result;Result;*;enteredvalue],[PLC00000166;VAR00001|Result;Result]))/mean([PLC00000015;VAR00001|Result;Result;*;enteredvalue],[PLC00000166;VAR00001|Result;Result])

I have tried the memo field and the text field. Text doesn't work as it is more than 255 characters. Memo doesn't work either even though it should (the character restriction size with memo is 65535 characters I think).
I think it has a problem with certain characters like "|" as some data that is not exactly like the example above goes into the table.
It is with an embedded SQL statement in VBA I am attempting to insert it but even when I copy and paste the SQL into a query it doesn't go in either.
That makes me think it is the table that is the problem.
It is not an option for me to strip out characters that will not insert, everything has to go in.
Any help will be greatly appreciated.

tiptap

lukin wrote: »

I have an Access table with a field that I have to insert a lot of non-alphanumeric characters to. Example:
(max([PLC00000015;VAR00001|Result;Result;*;enteredvalue],[PLC00000166;VAR00001|Result;Result])-min([PLC00000015;VAR00001|Result;Result;*;enteredvalue],[PLC00000166;VAR00001|Result;Result]))/mean([PLC00000015;VAR00001|Result;Result;*;enteredvalue],[PLC00000166;VAR00001|Result;Result])

I have tried the memo field and the text field. Text doesn't work as it is more than 255 characters. Memo doesn't work either even though it should (the character restriction size with memo is 65535 characters I think).
I think it has a problem with certain characters like "|" as some data that is not exactly like the example above goes into the table.
It is with an embedded SQL statement in VBA I am attempting to insert it but even when I copy and paste the SQL into a query it doesn't go in either.
That makes me think it is the table that is the problem.
It is not an option for me to strip out characters that will not insert, everything has to go in.
Any help will be greatly appreciated.

Hi,
Whats the error ?
What happens when you manually try and add the data into the table in the datasheet view of access ?

lukin

I haven't tried to insert it manually to the table but the error for both SQL and the query is "incorrect syntax" I think.
Edit:"Syntax error in string in query expression"

tiptap

lukin wrote: »

I haven't tried to insert it manullay to the table but the error for both SQL and the query is "incorrect syntax" I think.

take it you don't have it handy there the database to try add it manually ?
I'm sure it's just incorrect syntax alright..no reason why that data shouldn't go in if it's memo.
if you want to post the db up here I can have a look

lukin

tiptap wrote: »

take it you don't have it handy there the database to try add it manually ?
I'm sure it's just incorrect syntax alright..no reason why that data shouldn't go in if it's memo.
if you want to post the db up here I can have a look

I will post up the table and a module that contains the code I am doing the insert with. Give me a few mins.

lukin

I have attached a db with the table that is causing the problem ("Calculation Rule" is the field it will not enter into). There is also a module with the code that I am doing the insert with.
The code actually inserts from an Excel sheet (an example of which I have also attached). That doesn't really make a difference, you can try inserting just what won't go into the "Calculation Rule" field (turn off the primary key).
"Calculation Rule" is memo datatype in that table and the SQL inserts what won't go in with single quotes. That is because I have tried it without single quotes, changed the datatype to text etc. nothing work.
It's kind of important I sort it.

lukin

It looks like I will have to cop and paste the fields in one go from the Excel sheet into the table. Not really the way I want to do it but still.