lincstech
asked on
Insert INTO data mismatch Access field
Hello,
I have a issue with fields format with Microsoft Access. If I format the field to Currency or Number and use the "Insert Into" I get data type mismatch.
I have a issue with fields format with Microsoft Access. If I format the field to Currency or Number and use the "Insert Into" I get data type mismatch.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If I format the field to Currency or Number...
You should leave the values "as is" and not formatting them when inserting into the table.
1234.56 will become $1,234.56 after being formatted to currency style. The comma separator (and the dollar symbol) will cause your sql INSERT statement to fail.
This works:
INSERT INTO SomeTable (NumCol, CurrencyCol) VALUES (1234, 1234.56) ; This will fail:
INSERT INTO SomeTable (NumCol, CurrencyCol) VALUES (1,234, 1,234.56) ; This will also fail:
INSERT INTO SomeTable (NumCol, CurrencyCol) VALUES (1,234, $1,234.56) ; SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This works for me:
INSERT INTO SomeTable (NumCol1, CurrCol2) VALUES (1, 2) ;