asked on

Runtime Error 3001 - Invalid Argument error

When trying to run the following piece of code I get a 'Runtime Error 3001 Invalid Argument'. This code has previously worked and I cannot find any changes to it. INTRAN is an ODBC database using a dataflex driver.
Intran1 is a copy which keeps an offline backup (for faster reporting)

I have tried changing the last line to:

" WHERE ([INTRAN].[DATE] > #01/01/2011# and [INTRAN].[DATE] < #30/04/2011#);"

And I still receive the same error

strSQL = "INSERT INTO Intran1 ( [Key], RECORD_NUMBER, [INM#], WHSE, QTY, PRICE, NET, COST, DISCRATE, DISCOUNT, TAXRATE," & _
                " TAXAMOUNT, [BATCH#], SEQUENCE, BATCH, PERIOD, TRAN, REF, [DATE], DETAIL, STOCK_CODE, [STOCKCAT#]," & _
                " CATEGORY, QTY2, [NOTE], ORDER_NO, ORDER_SEQ_NO, FC_PRICE, FC_NET, FC_DISCOUNT, FC_TAXAMOUNT, FC_COST," & _
                " [@SPARE1], [@SPARE2], [@SPARE3], [@SPARE4] )" & _
            " SELECT DISTINCTROW Format([date],'yyyymmdd') & '-' & [record_number] AS [Key], INTRAN.RECORD_NUMBER," & _
                " INTRAN.[INM#], INTRAN.WHSE, INTRAN.QTY, INTRAN.PRICE, INTRAN.NET, INTRAN.COST, INTRAN.DISCRATE," & _
                " INTRAN.DISCOUNT, INTRAN.TAXRATE, INTRAN.TAXAMOUNT, INTRAN.[BATCH#], INTRAN.SEQUENCE, INTRAN.BATCH," & _
                " INTRAN.PERIOD, INTRAN.TRAN, INTRAN.REF, INTRAN.DATE, INTRAN.DETAIL, INTRAN.STOCK_CODE," & _
                " INTRAN.[STOCKCAT#], INTRAN.CATEGORY, INTRAN.QTY2, INTRAN.NOTE, INTRAN.ORDER_NO, INTRAN.ORDER_SEQ_NO," & _
                " INTRAN.FC_PRICE, INTRAN.FC_NET, INTRAN.FC_DISCOUNT, INTRAN.FC_TAXAMOUNT, INTRAN.FC_COST," & _
                " INTRAN.[@SPARE1], INTRAN.[@SPARE2], INTRAN.[@SPARE3], INTRAN.[@SPARE4]" & _
            " FROM INTRAN" & _
           " WHERE (((DateDiff('m',([INTRAN].[DATE]),Now()))<9));"
              
            
            


DoCmd.RunSQL strSQL

Open in new window

Zane80

ASKER

This is the code which runs before the above (this completes correctly)

strSQL = "DELETE DISTINCTROW Intran1.*, Intran1.DATE" & _
                " FROM Intran1" & _
                " WHERE (datediff('m',(Intran1.DATE),now())<9);" 

DoCmd.RunSQL strSQL

Open in new window

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

also are you sure about these

INTRAN.[@SPARE1]
INTRAN.[@SPARE2]
INTRAN.[@SPARE3]
INTRAN.[@SPARE4]

-->

@SPARE1
@SPARE2
@SPARE3
@SPARE4

HainKurt

INTRAN.[@SPARE1], INTRAN.[@SPARE2], INTRAN.[@SPARE3], INTRAN.[@SPARE4]
-->
@SPARE1, @SPARE2, @SPARE3], @SPARE4

also you dont have to use table name all over the place

" INTRAN.[INM#], INTRAN.WHSE, INTRAN.QTY, INTRAN.PRICE, INTRAN.NET, INTRAN.COST, INTRAN.DISCRATE," &
-->
" [INM#], WHSE, QTY, PRICE, NET, COST, DISCRATE," &

HainKurt

are you sure your column names are like these: @SPARE1

because I see those ones into the values (...) part too...

Graham Mandeno

Hi Zane

I can't immediately see any problem with your INSERT query syntax, although the WHERE clause looks OK, so I don't think the problem is there.

Have you tried running the SQL from a query? This is often a good debugging technique because it tends to give more informative error messages that DoCmd.RunSQL or db.Execute.

Set a breakpoint at line 19 in your code and, when it stops, enter ?strSQL in the Immediate window. Select the SQL string and copy it.

Then create a new query, switch to SQL view, and paste your SQL string. Try to run the query and see what happens.

Incidentally, your test WHERE clause will not work, because SQL does not understand dates in good old NZ format.

" WHERE ([INTRAN].[DATE] > #01/01/2011# and [INTRAN].[DATE] < #30/04/2011#);"
should be
" WHERE ([INTRAN].[DATE] > #01/01/2011# and [INTRAN].[DATE] < #04/30/2011#);"
or I prefer
" WHERE ([INTRAN].[DATE] > #2011-01-01# and [INTRAN].[DATE] < #2011-04-30#);"

Best regards,
Graham

HainKurt

also does it make any difference to remove ";" from the end

SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zane80

ASKER

Yes im sure about the Spare fields (it is coming from a ERP system).

" WHERE 1=0 ;" - Update completed

I have noticed something else fishy, I am not sure if its related (from the result of the last test).

If i try to copy and paste the Intran1 table i also get an "Invalid argument" that is just a copy and paste from the access. I have tried a compact and repair (although this is also done nightly) with no luck.

HainKurt

so the problem is on this line?

" WHERE (((DateDiff('m',([INTRAN].[DATE]),Now()))<9));"

when you changed that line to

" WHERE 1=0 ;"

it worked (no inserts)

what about this line

" WHERE ( DateDiff('m',INTRAN.[DATE],Now()) < 9 );"

Zane80

ASKER

" WHERE ([INTRAN].[DATE] > #01/01/2011# );" didn't work 3001 invalid argument.

Boag2000
1) Yes I know but it makes it easier if we need to switch the tables in a query between live data and the copied data
2) Done
3) Yea, I learnt using this method, I do setwarnings ture and false before
4) It is much easier for formatting and trouble shooting when I use that char.
5) I think im down to two options, corrupt table or incorrect date somewhere

ASKER CERTIFIED SOLUTION

Graham Mandeno

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

<PS: @Jeff - the line continuation characters are not part of the SQL string - they are outside the quotes and are only in the VBA.>
Yes, but my point was that they are not needed for the SQL to run, and it just makes troubleshooting that much easier if they are not there.

Zane80

ASKER

Completed. I used an offline 3rd party access repair tool which seemed to fix the corruption (which was in both tables)