Avatar of Zane80
Zane80
Flag for New Zealand 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

Microsoft OfficeMicrosoft AccessSQL

Avatar of undefined
Last Comment
Zane80

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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," & 
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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)