Runtime Error 3001 - Invalid Argument error

Zane80
Zane80 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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

HainKurtSr. System Analyst
Commented:
does it work if you change last line as

" WHERE (((DateDiff('m',([INTRAN].[DATE]),Now()))<9));"
-->
" WHERE 1=0"
or
" WHERE 1=0;"
HainKurtSr. System Analyst

Commented:
also are you sure about these

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

-->

@SPARE1
@SPARE2
@SPARE3
@SPARE4
HainKurtSr. System Analyst

Commented:
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," & 
HainKurtSr. System Analyst

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

because I see those ones into the values (...) part too...
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
HainKurtSr. System Analyst

Commented:
also does it make any difference to remove ";" from the end
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
First, try not to re-use the same variable names.
This makes troubleshooting more difficult.

Second in case there is a timing issue, try inserting DoEvents between the two snippets.

Third, try using CurrentDB.execute instead of Docmd.run SQL.

Forth, don't bother with the line continuation character in SQL.  It is really only needed for display purposes.  When you insert it in the actual SQL you now have the added complexity of wondering if something is wrong with the synatx.

Fifth, for troubleshooting, start with a very minimal Insert statement and see if that runs.  If so, then add more gradually, and keep testing, untill it fails.
This will tell you exactly where the error is.chop off the Insert SQL
If the minimal Insert fails immediately, then why bother loading the full statement?
Make sense?
;-)

So the way I see it, give it a go like the attached code snippet.
If this does not work, then again, strip down the Insert to be very minimal and start troubleshooting.

Keep us posted.

;-)

JeffCoachman


Dim strSQLDel as string
Dim strSQLInsert as string


strSQLDel ="DELETE DISTINCTROW Intran1.*, Intran1.DATE FROM Intran1 WHERE (datediff('m',(Intran1.DATE),now())<9);" 
CurrentDB.execute strSQLDel, dbfailonerror

DoEvents

strSQLInsert ="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));"

CurrentDB.execute strSQLInsert, dbfailonerror

Open in new window

Author

Commented:
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.

HainKurtSr. System Analyst

Commented:
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 );"

Author

Commented:
" 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
Zane (and others)

The fact that it works with "WHERE 1=0" does not mean that the problem is with the WHERE clause.  The query optimiser is simply figuring out that no records will ever fit the criteria, so whatever causes the "invalid argument" error never gets executed.

I agree that it is likely the problem is with corruption.  As Intran1 is only a backup table, is it feasible to delete it and rebuild it?  Do a compact/repair after deleting and before you create the new table.

Best,
Graham

PS: @Jeff - the line continuation characters are not part of the SQL string - they are outside the quotes and are only in the VBA.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial