Results 1 to 11 of 11
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Wrong number of arguments


    I make backups almost every day. If I open the query of yesterdays backup the error is not there. I am fresh but can not remind myself that I have changed something for the error to appear today. I imported the table and query from the backup then the error still appears. On the error message it can be seen that it doesn't display a second closing bracket? Does someone see something? I think it is possible the error originates from another involved calculated field in this query.
    Click image for larger version. 

Name:	WrongNimber01.png 
Views:	14 
Size:	16.2 KB 
ID:	41990

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Suggest you show the SQL of the query so readers can see the whole thing. Nothing obvious in the picture.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    There are14 fields in the query, 12 calculated. Query works well for over a year.

    SELECT t01Month.MntID038, IIf([MntID038]=1,[FirstMnthInPerfac038],DateAdd("m",[AmtMnths038],[FirstMnthinPerfac038])) AS FstDayOfMnth038, DateAdd("m",1,[FstDayOfMnth038])-1 AS LstDayOfMnth038, MonthName(Month([FstDayOfMnth038])) AS MonthName038, Year([LstDayOfYear038]) AS Year038, DateSerial(IIf(Month(DLookUp("FirstMonthPerfac020" ,"q01BusinessInformation"))>Month([FstDayOfMnth038]),Year([FstDayOfMnth038])-1,Year([FstDayOfMnth038])),Month(DLookUp("FirstMonthPerfac020","q01Busines sInformation")),1) AS FstDayFinYer038, DateAdd("yyyy",1,[FstDayFinYer038])-1 AS LstDayOfYear038, t01Month.Workingdays038, -Int(-IIf([LstDayOfYear038]-DLookUp("StartDate020","q01BusinessInformation")<0 ,0,[LstDayOfYear038]-DLookUp("StartDate020","q01BusinessInformation"))/365.25) AS YearNumforBus038, DateSerial(Year([FstDayOfMnth038]),Month([FstDayOfMnth038]),1) AS FstDayOfMnth038b, [MntID038]-1 AS AmtMnths038, DLookUp("FirstMonthPerfac020","q01BusinessInformat ion") AS FirstMnthInPerfac038, DLookUp("StartDate020","q01BusinessInformation") AS FstMnthInBusiness038, IIf((Year([FstDayOfMnth038])-Year([FstMnthInBusiness038]))*12+Month([FstDayOfMnth038])-Month([FstMnthInBusiness038])<0,0,(Year([FstDayOfMnth038])-Year([FstMnthInBusiness038]))*12+Month([FstDayOfMnth038])-Month([FstMnthInBusiness038])+1) AS MonthNumInBus038
    FROM t01Month;
    Click image for larger version. 

Name:	WrongNumber02.png 
Views:	15 
Size:	6.9 KB 
ID:	41994Click image for larger version. 

Name:	WrongNumber03.png 
Views:	15 
Size:	19.3 KB 
ID:	41995Click image for larger version. 

Name:	WrongNumber04.png 
Views:	15 
Size:	19.1 KB 
ID:	41996

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I copied your code to an SQL re-formatter. It did not give an error???

    Code:
    SELECT t01Month.MntID038
        ,IIf([MntID038] = 1, [FirstMnthInPerfac038], DateAdd("m", [AmtMnths038], [FirstMnthinPerfac038])) AS FstDayOfMnth038
        ,DateAdd("m", 1, [FstDayOfMnth038]) - 1 AS LstDayOfMnth038
        ,MonthName(Month([FstDayOfMnth038])) AS MonthName038
        ,Year([LstDayOfYear038]) AS Year038
        ,DateSerial(IIf(Month(DLookUp("FirstMonthPerfac020", "q01BusinessInformation")) > Month([FstDayOfMnth038]), Year([FstDayOfMnth038]) - 1, Year([FstDayOfMnth038])), Month(DLookUp("FirstMonthPerfac020", "q01BusinessInformation")), 1) AS FstDayFinYer038
        ,DateAdd("yyyy", 1, [FstDayFinYer038]) - 1 AS LstDayOfYear038
        ,t01Month.Workingdays038
        ,- INT (- IIf([LstDayOfYear038] - DLookUp("StartDate020", "q01BusinessInformation") < 0, 0, [LstDayOfYear038] - DLookUp("StartDate020", "q01BusinessInformation")) / 365.25) AS YearNumforBus038
        ,DateSerial(Year([FstDayOfMnth038]), Month([FstDayOfMnth038]), 1) AS FstDayOfMnth038b
        ,[MntID038] - 1 AS AmtMnths038
        ,DLookUp("FirstMonthPerfac020", "q01BusinessInformation") AS FirstMnthInPerfac038
        ,DLookUp("StartDate020", "q01BusinessInformation") AS FstMnthInBusiness038
        ,IIf((Year([FstDayOfMnth038]) - Year([FstMnthInBusiness038])) * 12 + Month([FstDayOfMnth038]) - Month([FstMnthInBusiness038]) < 0, 0, (Year([FstDayOfMnth038]) - Year([FstMnthInBusiness038])) * 12 + Month([FstDayOfMnth038]) - Month([FstMnthInBusiness038]) + 1) AS MonthNumInBus038
    FROM t01Month;

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you. Truly frustrating. I will fight on. I will see if I can post just the involved objects.

  6. #6
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I opened a new database. Copied objects (my system has 2000) group by group each time testing q01Month where the problem is. No problem. The problem is in the fourth last module. Not sure exactly what will test and let you know. Can a module be too long? This module has 404 lines. The only object I have not copied over is that module, then there is no error. If I copy it over there is an error in q01Month. What doesn't make sense is the module only activate closing form f01Month, the query has nothing to do with the module.

    Is there a limit to the amount of functions in one Module? I just removed the one function from that Module. The function is about 10 lines, and now the error stays away.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe a stretch, but is it possible that the problem module queries records that the others don't, and one of those records contains Null, thus the Month function (nested) is returning Null? New data like this could raise that error after years of running perfectly. Or the issue is still new data with nulls but the problem is in the query itself and is only called by something in that one module?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi Micron. There are about 40 of the functions below in this Module. My system has 250 forms. Each form has buttons to delete all records, or delete one record at a time. This Module does that, clicking the "Delete All" button on form f01Month. I just put the code below into its own Module, but the error came back opening q01month. I thought the module is "sleeping" unless invoked by the button on the form?


    Option Compare Database
    Option Explicit
    __________________________________________________ ________
    Function Month()
    Dim InsertInto As String
    Dim DeleteRecords As String
    '---------------------------------t01Month--------------------------------
    DeleteRecords = "Delete * From t01Month"
    CurrentDb.Execute DeleteRecords
    InsertInto = "INSERT INTO t01Month (MntID038, Workingdays038) VALUES (0,1)"
    CurrentDb.Execute InsertInto
    DeleteRecords = "Delete * From t01Month"
    CurrentDb.Execute DeleteRecords
    End Function

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is a long shot, but "Month()" is a built in function in Access. (and a reserved word)

    Maybe try this code??
    Code:
    Function fMonth()    '<<-- added "f" to the front of the function name
        Dim InsertInto As String
        Dim DeleteRecords As String
        '---------------------------------t01Month--------------------------------
        DeleteRecords = "Delete * From t01Month"
        CurrentDb.Execute DeleteRecords, dbFailOnError
        
        InsertInto = "INSERT INTO t01Month (MntID038, Workingdays038) VALUES (0,1)"
        CurrentDb.Execute InsertInto, dbFailOnError
        
        '  DeleteRecords = "Delete * From t01Month"   '<<-- already defined
        CurrentDb.Execute DeleteRecords, dbFailOnError
    End Function

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by ssanfu View Post
    This is a long shot, but "Month()" is a built in function in Access.
    I don't think it's a long shot, it's the issue. If you create a function with the same name as a built-in function, it will take precedence. I did that in an app with the Round() function. Created my own so it got used instead of the built-in function (didn't want bankers rounding).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you very much. I would not have lost a day if I was more aware, I knew Month() is a reserved word. Adding the f, solved my issue. In my post one, the error message didn't help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. function has the wrong number of arguments
    By Darla in forum Programming
    Replies: 6
    Last Post: 02-09-2017, 02:03 PM
  2. Replies: 3
    Last Post: 09-09-2015, 11:26 AM
  3. Replies: 3
    Last Post: 07-18-2015, 05:02 PM
  4. Replies: 3
    Last Post: 02-12-2014, 03:36 PM
  5. HELP - Invalid number of arguments
    By jgelpi16 in forum Programming
    Replies: 3
    Last Post: 07-22-2010, 09:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums