Invalid Procedure call or arguement

[cardgunner]

I'm not experienced in code at all.

I can't figure out what is wrong with this code

Me.expiredate.Value = IIf([payterm] = "X", Forms![orderfrm]!orderdate, DateAdd([payterm], 1, Forms![orderfrm]!orderdate))

the whole thing is

Private Sub cboproductid_AfterUpdate()

  Me.txtamount.Value = DLookup("[amount]", "producttbl", "[productid] = Forms![orderfrm]!cboproductid") 'Populate the Item's Price.
  Me.productname.Value = DLookup("[productname]", "producttbl", "[productid] = Forms![orderfrm]!cboproductid")
  Me.updatepuchased.Value = IIf([payterm] = "X", "Yes", "No")
  Me.expiredate.Value = IIf([payterm] = "X", Forms![orderfrm]!orderdate, DateAdd([payterm], 1, Forms![orderfrm]!orderdate))
End Sub

I replaced the true/false parts with "01/01/2000", "02/02/2008" and it works fine.

using the dateadd code If I don't select a productid with a payterm as X it works great. But soon as I select a Productid whose payterm is X i get

Run-time error '5'

Invalid procedure call or arguement

I think it's telling me there is something wrong with my iif statement but I don't see it.

What am I missing or doin wrong?

 

[gemma-the-husky]

Me.expiredate.Value = IIf([payterm] = "X", Forms![orderfrm]!orderdate, DateAdd([payterm], 1, Forms![orderfrm]!orderdate))

in the last (false response) you have the expression

DateAdd([payterm], 1, Forms![orderfrm]!orderdate)

I think both the true false expressions are evaluated, so if payterm is X, then this expression WILL still be evaluated and may cause an error, as "X" isnt a permitted argument for dateadd function

so you might needsimply
If([payterm] = "X" then
expiredate = Forms![orderfrm]!orderdate
else
expiredate = DateAdd([payterm], 1, Forms![orderfrm]!orderdate)
end if

 

[cardgunner]

Thanks for the reply.

I fill it in and I get a compile error: Expected: Expression

I have it written like this

Me.expiredate.Value = If([payterm] = "X" then
expiredate = Forms![orderfrm]!orderdate
Else
expiredate = DateAdd([payterm], 1, Forms![orderfrm]!orderdate)
End If

I'm trying a couple things with that but first response is the error.

 

[boblarson]

Nope, syntax is wrong. You need:

If([payterm] = "X" then
  Me.expiredate = Forms![orderfrm]!orderdate
Else
  Me.expiredate = DateAdd([payterm], 1, Forms![orderfrm]!orderdate)
End If

 

[cardgunner]

Sorry Bob I tried as

If([payterm] = "X" then
  Me.expiredate = Forms![orderfrm]!orderdate
Else
  Me.expiredate = DateAdd([payterm], 1, Forms![orderfrm]!orderdate)
End If

and like

Me.expiredate.Value = If([payterm] = "X" then
        Me.expiredate = Forms![orderfrm]!orderdate
        Else
        Me.expiredate = DateAdd([payterm], 1, Forms![orderfrm]!orderdate)
End If

with the same compile error
Is there anything needs before the "If" I have it following the

Private Sub cboproductid_AfterUpdate()

  Me.txtamount.Value = DLookup("[amount]", "producttbl", "[productid] = Forms![orderfrm]!cboproductid") 'Populate the Item's Price.
  Me.productname.Value = DLookup("[productname]", "producttbl", "[productid] = Forms![orderfrm]!cboproductid")
  Me.updatepuchased.Value = IIf([payterm] = "X", "Yes", "No")

 

[boblarson]

Instead of trying to guess, why don't you put a breakpoint in and F8 through the code so you can see where the error occurs. Or, post your db so we can.

 

[cardgunner]

Bob,

I hate being an idiot but I don't know what a breakpoint is.

As far as posting the db, it's my clients business and that would violate our contract. I would have to take the tables out and such.

I'll try to find out about this breakpoint.

Thanks.

 

[boblarson]

You're fortunate. I happen to have a "quick tutorial" on that on my website:

http://www.btabdevelopment.com/main...kpointtostepthroughcode/tabid/58/Default.aspx

 

[cardgunner]

Thanks for the lesson.


I'm not sure what it was supposed to do but I'm back to square one I think.

I posted some screenshots of what I did and what I got.

Is there something I have to add after the then?

 

[boblarson]

Okay, three things.

1. You can't put the breakpoint on the event header. You need to select the first EXECUTABLE step, so that would be the first line of Me.txtAmount.value.

2. Your syntax error is that you have a right parenthesis before the [payterm] but you also don't refer to [payterm] that way (sorry I didn't have that one in the original code I suggested). It should be:
If Me.payterm = "X" Then

3. You really would benefit from working through this set of tutorials:
http://www.functionx.com/vbaccess/index.htm

 

[raskew]

The DateAdd() function will bomb if fed anything other than a valid interval designation. As pointed out earlier, the Iif() function tests both the True and False portions, and thus errors-out when then DateAdd() function is fed "X" as the interval.

The following eliminates the Iif() function and returns the current date if provided with an invalid interval, else performs the DateAdd() function when provided a valid interval.

Public Function NewDate(strInterval As String) As Date

Dim a As Date

   a = Date

   If InStr("yyyydmq", strInterval) > 0 Then
      NewDate = DateAdd(strInterval, 1, a)
   Else
      NewDate = a
   End If


End Function

Modify as necessary to specify the start date if other than current date.

HTH - Bob

 

[boblarson]

The DateAdd() function will bomb if fed anything other than a valid interval designation. As pointed out earlier, the Iif() function tests both the True and False portions, and thus errors-out when then DateAdd() function is fed "X" as the interval.

The following eliminates the Iif() function and returns the current date if provided with an invalid interval, else performs the DateAdd() function when provided a valid interval.

Public Function NewDate(strInterval As String) As Date

Dim a As Date

   a = Date

   If InStr("yyyydmq", strInterval) > 0 Then
      NewDate = DateAdd(strInterval, 1, a)
   Else
      NewDate = a
   End If


End Function

Modify as necessary to specify the start date if other than current date.

HTH - Bob

But the If...Then...Else that I posted (provided the [payterm] is changed to Me.payterm or Me!payterm does NOT test the DateAdd because UNLESS there are other incorrect designators, other than X in the table.

 

[cardgunner]

Bob Larson,

Again a HUGE THANKS!

I took out the "(" and added Me.expiredate and it works perfectly. At least for right now.

I have not tried tofit the other suggestion in. I'm sure with enough help I could be told how to take that and replace what I had, but that is for a differnt day.

What I have is great.

That website at first glance looks to be a Mecca of information. I'm hoping this is what I've been looking for. I really need to learn code and functions but could not find a good source to teach me. I've built about a dozen db over the last 6 years on wizards, macros, and forum help with code and issues and such. I really want to bring it top the next step.

Hopefully this site can bring me there. I'm really excited.

Thank you Bob Larson!

 

[gemma-the-husky]

cardgunner, did you follow what was wrong in the first place

you were trying to say

myvalue = (if mytest is true then use "valueA" else use "valueB")

this didnt work, because the programming language still evaluated both eexpressions to generate valuea and valueb, and fialed because in some cases the expression for valueb was illegal

the alternative was to say

if mytest is true then
myvalue = valuea
else
myvalue = valueb
end if

this doesnt fail because it vb now only evealuates sufficient expressions to find the correct answer

but you cant say

myvalue = if mytest is true then
myvalue = valuea
else
myvalue = valueb
end if

because this is combining two syntaxes illegally.
------------------

in the end i think bob offered you an alternative methid of coding

 

[cardgunner]

Gemma,

I think I understand it now.

However I would never had understood it without Bob's and Forum help.

I have not tried raskew's code though, no offense raskew.

But I do understand why mine failed.

Thanks for the post and the information.

If only I can find as helpful posters for a nagging Report Server problem for my fulltime job.