Wrong number of arguments Results

Hello friends,

I have this as a control source on a txtbox on my report.
I get the error, "The expression you entered contains the wrong number of arguments"


	Code:
	 =IIF(([MinOfStatusCode]=0,"BOR/Scope",IIF([MinOfStatusCode]=1,
"Pri",IIF([MinOfStatusCode]=2,"In Prog",IIF([MinOfStatusCode]=3,"Compl",
IIF([MinOfStatusCode]=4,"Held",IIF([MinOfStatusCode]=5,"Not Strt",
IIF([MinOfStatusCode]=6,"Defer",IIF([MinOfStatusCode]=7,"UAT",
IIF([MinOfStatusCode]>7,"Closed",""))))))))))

Here's what i am trying to get:
statusCode --- shortStat
0 = BOR/Scope
1 = Pri
2 = In Prog
3 = Compl
4 = Held
5 = Not Strt
6 = Defer
7 = UAT
8 = Closed
9 = Reqmts


Help me here please? I am stuck *begging*

Good day, I am having trouble with an IIF statement. Can someone tell me if this is doable. I get an erro that says wrong number of arguments. What I am looking for is that when a Hospital is selected that factor is displayed for, for example if Methodist is select then 85.00 dollars is displayed. Not sure if OR operator is correct to use. Can someone help me please.

Hospital Factor: CCur(IIf([HospitalName]="Baptist Health Systems",98.00 Or [HospitalName]="Christus Santa",100.00 Or [HospitalName]="HCA",75.00 Or [HospitalName]="Methodist",85.00 Or [HospitalName]="University Health Systems",99.00))

I keep getting a "Wrong number of arguments used with function in query expression" when I try to run my query.

I'm no expert with Access but all I'm trying to do is join two tables containing some customer details (DT_CONTRACTOR,Movex Customers) and use part of the postcode from DT_CONTRACTOR to check which region the postcode is in. The regions are held in the Poscodes table. For info some of the postcodes in the are blank, some are incomplete but the majority are fully complete. An example of the Postcodes table and the code are below.

Postcodes table

Region Poscode

Edinburgh EH26 9
Edinburgh EH26 0

Code

SELECT dbo_DT_CONTRACTOR.Account_No, dbo_DT_CONTRACTOR.Contractor_Name, dbo_DT_CONTRACTOR.Address_Line1, dbo_DT_CONTRACTOR.Address_Line2, dbo_DT_CONTRACTOR.Address_Line3, dbo_DT_CONTRACTOR.Address_Line4, dbo_DT_CONTRACTOR.Address_Line5, dbo_DT_CONTRACTOR.Post_Code, [Movex Customers].OKSMCD
FROM dbo_DT_CONTRACTOR INNER JOIN [Movex Customers] ON dbo_DT_CONTRACTOR.Account_No = [Movex Customers].OKCUNO
WHERE (IIf(Len(Left([dbo_DT_CONTRACTOR].[Post_Code],(InStr([dbo_DT_CONTRACTOR].[Post_Code],' '))))+1) = 1,[dbo_DT_CONTRACTOR].[Post_Code],"EH26 9") In (SELECT Postcode FROM Postcodes);

Any help much appreciated as I thought this would be simple to do and has now taken me 2 days to get nowhere.

Stuart

Hi,

I'm trying to query an Access database with an asp page hit with a hidden form from an ecommerce site. It was working fine until I added a new variable ("speed"), and now I get the following error:

Microsoft VBScript runtime error '800a01c2'
Wrong number of arguments or invalid property assignment: 'updateUser'
/purchase_proc.asp, line 47


Line 47 of purchase_proc.asp is the following:


call updateUser(session("userid"),session("name"),sessi on("pass"),firstname,lastname,address1,address2,ci ty,state,zip,country,phone,email,message,views,spe ed)


And it comes from a jscript function as follows:


Function updateUser(userid,name,pass,firstname,lastname,add ress1,address2,city,state,zip,country,phone,email, message,views,speed)
sql = "UPDATE users SET "
sql = sql & "name = '" & name & "', "
sql = sql & "pass = '" & pass & "', "
sql = sql & "firstname = '" & firstname & "', "
sql = sql & "lastname = '" & lastname & "', "
sql = sql & "address1 = '" & address1 & "', "
sql = sql & "address2 = '" & address2 & "', "
sql = sql & "city = '" & city & "', "
sql = sql & "state = '" & state & "', "
sql = sql & "zip = '" & zip & "', "
sql = sql & "country = '" & country & "', "
sql = sql & "phone = '" & phone & "', "
sql = sql & "email = '" & email & "', "
sql = sql & "message = '" & message & "', "
sql = sql & "views = " & views & " "
sql = sql & "speed = '" & speed & "', "
sql = sql & "WHERE userid = " & userid & ";"
Set DBConn8 = Server.CreateObject("ADODB.Connection")
DBConn8.Open getDbCon()
Set oRS8 = DBConn8.Execute(sql)
if DBConn8.errors.count> 0 then
for counter = 0 to DBConn8.errors.count
response.write "Database Error #" & DBConn8.errors(counter).number & " -> " & DBConn8.errors(counter).description & "
"
next
end if
session("expires") = expires
set DBConn8 = nothing
End Function


Like I said, since I added "speed", I've been getting this worng number of arguments error. But as far as I can see the arguments with the call and the function are the same. I'm at a loss, mostly because I'm a newbie with asp, access, and the script languages. Any suggestions will be helpful. Thanks

Hi.

I'm getting "Argument not optional" on the highlighted line:

	Code:
	Private Function getTableFieldNames(tableName As String) As Collection
    Dim fieldNames As Collection
    Dim Rst As Recordset
    Dim f As field
    Dim fieldIdx As Integer
    
    Set fieldNames = New Collection
    Set Rst = CurrentDb.OpenRecordset(tableName)

    fieldIdx = 0
    For Each f In Rst.Fields
        ' Skip first field (ID).
        If fieldIdx  0 Then
            fieldNames.Add ("[" & f.Name & "]")
        End If
        fieldIdx = fieldIdx + 1
    Next
    Rst.Close
    
    getTableFieldNames = fieldNames
End Function

I have no other functions named "getTableFieldNames". If I change that line to:

	Code:
	Set getTableFieldNames = fieldNames

the error goes away (is using Set here is correct?) but further along in the execution of the code, anything that uses the return value of that function has an error:

Quote: Run-time error '450':

Wrong number of arguments or invalid property assignment Not really sure what to do...

Thanks.

i have table works. and each record (work) has work_status. i need to count, how many works has work_status = 3
i wrote i query
dim b as integer
b = Count("SELECT [work_status] FROM [Works] WHERE [work_status] = 3")

compile wrote: wrong number of arguments or invalid prperty assigment.

I don' know, where i have made mistake. Could you help me?

I Am trying to insert data into a table --- some of the fields are coming from a different form (frmHold)



DoCmd.RunSQL "INSERT INTO tblReportResults1 (AppID, DocumentID, ReportType, RptDate, ProjOff, ProjContactPerson,) VALUES (" & Forms!frmHold.AppIDHold, Forms!frmHold.DocumentIDHold, Me.ReportType, Me.RptDate, Me.ProjOff, Me.ProjContactPerson

I've verified that all of the fields are in the table and there is good data to be inserted.

Please Hellp

I am having problems with this code, not sure where i am going wrong.
My code looks for values in a table prior to appending, if no records are present in the table an error message is displayed, allowing the user to choose to open a form to input new data in.
I get this message: "Argument not optional", pointing at my OpenConnection.
My sub is available in another form and I declared it as public as well as the variables. bellow is the code. They work in my form1, as I tested them before.

Code in form1:

	Code:
	
Option Explicit
' The member variables used throughout the project
Public XMLDoc As MSXML2.DOMDocument40
Public cnn As ADODB.Connection
Public rst As ADODB.Recordset
Public TypeName As String

' constant used throughout the code for when we want to
' save the document
Const XMLPath As String = "N:DatabasesXmlFiledownload.xml"

Public Sub OpenConnection()
Set cnn = CurrentProject.Connection
End Sub

Public Sub CloseConnection()
cnn.Close
Set cnn = Nothing
End Sub

Code in current form2:


	Code:
	Private Sub cmdValidateInput_Click()
On Error GoTo Err_cmdValidateInput_Click
Dim sSQL As String
Dim intResponse As Variant

'Check to see if there are any values in the POPImport table.



intResponse = MsgBox("There are no values in the POP Import table. Would you like to add values now?", _
              vbYesNo + vbQuestion, "Edit Table")
sSQL = "SELECT Count(qryPOPImport.intOrderNumber) AS CountOfOrder " & _
       "FROM qryPOPImport;"

       
'Open connection to the database
OpenConnection
'set the recordset
Set rst = New ADODB.Recordset
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open sSQL, cnn, adCmdText

'count the number of orders in the POP import table.
'Evaluate to proceed with the order processing

If rst.Fields("CountOfOrder") = 0 Then

'Give user the option to enter the new records.
If intResponse = vbYes Then
DoCmd.OpenForm "frmPOPImport", acFormDS, acEdit
Else
MsgBox "No new values"
End If

Else

'update Sku values in POP Import table
DoCmd.SetWarnings False
DoCmd.OpenQuery "udtImportPOPSku", acViewNormal, acReadOnly
DoCmd.SetWarnings True

ProcessPOPImport

End If

rst.Close
Set rst = Nothing
' Close Connection to database
CloseConnection
     
'Error handling
Exit_cmdValidateInput_Click:
    Exit Sub

Err_cmdValidateInput_Click:
    MsgBox Err.Description
    Resume Exit_cmdValidateInput_Click

End Sub



I thank those who run this show, as this website has helped me tremendously! I have a problem. I'm trying to use the following expression in an update query and keep getting the same error message that the function has the wrong number of arguments. I've pasted the arguments separately, since it is soooo long! Please help!

=IIf([ps]![Step]

Hi, I'm designing a form and I want to calculate how long our hotel tenants stay. So I have the form open and I am in the property sheet under the data tab. I am using the expression builder, and I see DateDiff has this format: DateDiff («interval», «date1», «date2», «firstweekday», «firstweek»)

I put in this: DateDiff("d",([End Date],Date())-[Start Date])

I'm getting a wrong number of arguments error. I was also getting another error last night related to the "d" portion of the equation. Obviously I am pretty new and unsophisticated at this stuff so I thought I would check with the experts here for some help.

Thanks.

hi y'all -

I have a report control whose control source is:
=IIf([srptInventoryUsage(b)].[Report].[HasData]=-1,[srptInventoryUsage(b)].[Report]![SumOfUnitPrice],0)

because I needed it to return a value even if empty. Now, in the group footer I need to SUM this control...and I tried:

=SUM(IIf([srptInventoryUsage(b)].[Report].[HasData]=-1,[srptInventoryUsage(b)].[Report]![SumOfUnitPrice],0))

which ends up asking me for a value. and I tried:

=IIf(Sum([srptInventoryUsage(b)].[Report].[HasData]=-1,[srptInventoryUsage(b)].[Report]![SumOfUnitPrice],0))

Which says it has the wrong number of arguments. Can someone tell me how to do this??

p.s. - I also tried changing the runningsum property to Over Group, but it accumulated totals through groups instead of restarting on each new group.....

Thank you!

Jennifer

[This message has been edited by JCross (edited 04-08-2002).]

[This message has been edited by JCross (edited 04-08-2002).]

I use the following to determine the % of current paid of the current owed.

=([CURRENT PAID]/[CURRENT OWED])*100

This works fine...but when there is no data in the current paid and current owed fields I get some type of formula in the place where, I would think, 0 or an empty field should go. I can't see the entire entry because the field is too narrow.
(Some of the Paid and Owed fields are either empty(null) or contain $0.00)

Can someone tell me how to either have the % field come up empty or with a 0 when there is a record with no data to create the % amount or when the data in the fields is $0.00.

With the help of another I have also tried this:

=IIF([CURRENT OWED])=0,0,IIF([CURRENT PAID])=0,0,nz([CURRENT PAID])/nz([CURRENT OWED])*100)))

But I get this error when I try to use this one: "The expression you entered has a function containing the wrong number of arguments "

I put the following in a text box on my report, but keep receiving an error message of "The expression you entered has a function containing the wrong number of arguments". What have I done wrong? How can I fix this?

=Count(IIF([Tpye of Scholarship]="Line"))

Thanks,
TIM

Hi

I have an issue with group footers in a report (see: http://www.access-programmers.co.uk/...d.php?t=183047)

As my footer ignores all but the last record in a group, i am using an IIF statement to get the values i want to display in the group footer (the detail will only display on screen, and only the footer is printed to create a summary report)

The statement is as follows:


=iif(count(iif([Jan]=”Checked”, 1, 0)=1, “Checked”, “”))


A vehicle is only going to be checked up to once a month, so this should work, but i am getting an error message "the expression you entered has a function with a wrong number of arguments" and i thought i'd move my post now that i was no longer trying to work at query level. (please see my other post for an excel file explaining what i am trying to achieve visually)



Thanks for your time and help

Send Object Issues

--------------------------------------------------------------------------------

I am trying to send a query to 3 e-mail recipients using send object in a macro. I use the TO: line and separate each email with a comma

For some reason, it only emails the last email on the list? It works in some of my access db's but not others. I converted to VB and noticed that each e-mail is not seperated with quotes so I added them and then this causes a wrong number of arguments error? help

I am using Group Wise 6.5

help

I'm working on an Access application that uses Excel and Outlook automation to create and send spreadsheets by email. The spreadsheets need to be protected against editing of locked cells, but enabled for insertion of rows.

When I do that natively in Excel, whilst recording a macro, it comes out with this VBA:

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowInsertingRows:=True

However, Access clearly needs the statement presented differently - it's

(with 'objActiveWkb' already set as objExcel.Application.ActiveWorkBook - this bit works, because I've been using it to push data into cells, etc)

objActiveWkb.Worksheets(1).Protect ("mypasswordhere")

What I can't work out is how to pass the AllowInsertingRows parameter. I've tried:

objActiveWkb.Worksheets(1).Protection.AllowInserti ngRows = True
objActiveWkb.Worksheets(1).Protect ("mypasswordhere")

- but I get 'Run time error 450 - wrong number of arguments or invalid property assignment)

What seems to work is:
objActiveWkb.Worksheets(1).Protect ("mypasswordhere"), AllowInsertingRows = True

Except that it doesn't work - still won't let me insert rows in the resulting spreadsheet, and when I manually unprotect it, then protect again, the checkbox next to Allow Inserting Rows is not checked.

Help!

I've added a reference to a vb.NET .tlb file. One of the methods in it is:


	Code:
	Public Function getMessageList(ByVal agency As Agency) As  Receipt
            Dim results() As Object = Me.Invoke("getMessageList", New Object() {agency})
            Return CType(results(0),Receipt)
End Function

The Receipt class is defined as:

	Code:
	    Partial Public Class Receipt
        
        Private referenceField() As Reference
        
        Private confirmationField() As Confirmation
        
        Private idField As String
        
        Private timestampField As Date
        
        Private timestampFieldSpecified As Boolean
        
        Private agencyidField As String
        
        '''
          _
        Public Property reference() As Reference()
            Get
                Return Me.referenceField
            End Get
            Set
                Me.referenceField = value
            End Set
        End Property
        
        '''
          _
        Public Property confirmation() As Confirmation()
            Get
                Return Me.confirmationField
            End Get
            Set
                Me.confirmationField = value
            End Set
        End Property
        
        '''
          _
        Public Property id() As String
            Get
                Return Me.idField
            End Get
            Set
                Me.idField = value
            End Set
        End Property

If I create a vb.NET usage of this to get all the receipts I do something like this:


	Code:
	                

     Dim messageListReceipt As ECitationService.Receiptservice.getMessageList(agency)

     'Now iterate through the list of reference IDs and retrieve
     'the actual info.
     For i As Integer = 0 To messageListReceipt.reference.GetUpperBound(0)

       Dim messageId = messageListReceipt.reference(i).id

     Next

I'm trying to do the same thing in VBA but I'm having trouble being able to get all the messageID's. I'm not sure if it's my variable declarations or how I'm trying to use them. A very basic sample of where I'm at right now (with all the other stuff stripped out):


	Code:
	Private Sub fillMessagesButton_Click()
Dim myMessages As Long
Dim myTrACS As BadgerTracks2010.QuickClerkBTrACS
Dim myAgency As BadgerTracks2010.Agency
Dim myService As ECitationService
Dim messageListReceipt As BadgerTracks2010.Receipt
Dim i As Long
Dim citationSuccessful As Boolean
Dim messageID As String
    
        
        Set myTrACS = New BadgerTracks2010.QuickClerkBTrACS
        Set myService = myTrACS.getTrACSService(Me.txtUserName, Me.txtPassword)
        Set myAgency = New BadgerTracks2010.Agency
        myAgency.id = Me.txtUserName
        myMessages = myService.getMessageCount(myAgency)
        
        
        Debug.Print "Message count: " & myMessages
        
        If myMessages > 0 Then
    
            Set messageListReceipt = myService.getMessageList(myAgency)
            For i = 0 To (myMessages - 1)
            
                messageID = messageListReceipt.id
            
                Debug.Print "Message #: " & i & ", " & messageID
                
            Next
                
        End If
                
End Sub

This keeps printing the same messageID. If I change the line

messageID = messageListReceipt.id

to

messageID = messageListReceipt.Reference(i).id to match the way it works in VB.NET I get "wrong number of arguments or invalid property assignment" error message.

Could someone please point me to why I can't loop through all the message ID's in the messagelist in VBA like I can in vb.NET?

Thanks
Lution

I have the following code in a continuous form's Before update event:


	Code:
	Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Nz(Me.Controls, "")) = "" Then
Dim Response As Integer
Response = MsgBox("You must fill in all fields", vbOK)
 
Me.TagNum.SetFocus
End If
 
End Sub

When I test the code I get the following error:

Quote: Runtime Error '450':

Wrong number of Arguments or invalid property assignment and the red line in the code above is highlighted. I have googled this but most hits involve excel or give explanations that I don't understand. This is the first time I have ever tried to check all controls on a form at once, and would really prefer one error message to one for each control (that seems tedious) So any help in figuring this out would be helpful, as I can't even get to test my message box to see if I have it set up correctly.

IIf(day()>ReturnDate, DateDiff("d", day(), ReturnDate), 0)
.
.
.
I intend it to be:

"If today's date is > ReturnDate,
Find the difference between Today's date and the ReturnDate,
Otherwise, return '0' as the number of days."

Could somebody tell me why this formula isn't working out for me?
It keeps saying that this function 'contains the wrong number of arguments'.

The date values on the 'ReturnDate' field are calculated dates from another query.

Thanks in advance!

Disconnected ADO recordset in Access 2007.

I apply the Filter or Find properties (either do the same)
Results is Error 450 (Wrong number of arguments or invalid property assignment)

These property assignments only have one argument.
The recordset field is adVarChar, 200

I have checked the filter/find clause and they look good.

From the Locals window:

	Code:
	strFilter = "fieldname='myvalue'"

The only wrong number of arguments I can see is the one too many arguments Access is having about this line of code.


Not finding an answer? Try a Google search.