Discussion Locked

SQL vs DAO calculation in Access 97

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi
I have a table T_AAA linked to a fixed lenght text file which is replaced every 2 weeks from a mainframe. 2 numeric (single) fields defined in there. Every 2 weeks I run a query to APPEND selected records from this linked text table to a nativeAccess table T_BBB with the same structure.
Then I run:
UPDATE T_BBB SET T_BBB.FTE = [FTE]/100, T_BBB.salary = [salary]/100;
and SOMETIMES (most of the time lately) I get an "Invalid Argumet" error.
I compacted and repaired - didn't help.
Then I wrote VBA Update sub to do the same task and it did work. But my process requires SQL query, and I see nothing wrong with that query.
Please help me find a fix, because I've had related problems and VBA didn't help.
Thank you, Serge.

5 replies

by oliver w.

Hi!
Use the INSERT INTO Statement if you want to append records:
INSERT INTO T_BBB (FTE, salary) SELECT T_AAA.FTE/100, T_AAA.salary/100) FROM T_AAA;

But it might work with Update as well, therefor check the following:
Things to check:
T_BBB.FTE can't be zero but [FTE] might get zero.
or the same with salary.
or it's an integer field.

Well these are just guesses. If you send me the db (the necessary part), i'll have a look.

hope that helps
Oliver

This discussion is closed to new replies.