code failure

  • Thread starter Thread starter Sam \(antha\)
  • Start date Start date
S

Sam \(antha\)

Apologies for this cross-post. I think it belongs here.

I have stared at this (control source) code for a long
time and cannot see the problem. Perhaps someone can help
me.

My test for [framePricing]=1 works successfully, as do
the prior tests. However, I cannot get return for
[framePricing]=2. I am convinced I am not placing
parenthesis properly, but when I change from below I get
the wrong number of arguments error.

Be kind, I am a (still being) self-taught amateur.

Thanks,
Sam

=IIf([frameService]<>3 Or [dogcount]=0,0,
IIf([framepricing]=1,
IIf([frameHSType]=1 And [dogcount]<4,
[PHS1231NetH]*[HSHolidayDays],
IIf([frameHSType]=1 And [dogcount]>3,
[PHS1241NetH]*[HSHolidayDays],
IIf([frameHSType]=2 And [dogcount]<4,
[PHS2431NetH]*[HSHolidayDays],
IIf([frameHSType]=2 And [dogcount]>3,
[PHS2441NetH]*[HSHolidayDays],

IIf([framepricing]=2,
IIf([frameHSType]=1 And [dogcount]<4,
[PHS1232NetH]*[HSHolidayDays],
IIf([frameHSType]=1 And [dogcount]>3,
[PHS1242NetH]*[HSHolidayDays],
IIf([frameHSType]=2 And [dogcount]<4,
[PHS2432NetH]*[HSHolidayDays],
IIf([frameHSType]=2 And [dogcount]>3,
[PHS2442NetH]*[HSHolidayDays])))))))))))
 
Sam (antha) said:
Apologies for this cross-post. I think it belongs here.

I have stared at this (control source) code for a long
time and cannot see the problem. Perhaps someone can help
me.

My test for [framePricing]=1 works successfully, as do
the prior tests. However, I cannot get return for
[framePricing]=2. I am convinced I am not placing
parenthesis properly, but when I change from below I get
the wrong number of arguments error.

Be kind, I am a (still being) self-taught amateur.

Thanks,
Sam

=IIf([frameService]<>3 Or [dogcount]=0,0,
IIf([framepricing]=1,
IIf([frameHSType]=1 And [dogcount]<4,
[PHS1231NetH]*[HSHolidayDays],
IIf([frameHSType]=1 And [dogcount]>3,
[PHS1241NetH]*[HSHolidayDays],
IIf([frameHSType]=2 And [dogcount]<4,
[PHS2431NetH]*[HSHolidayDays],
IIf([frameHSType]=2 And [dogcount]>3,
[PHS2441NetH]*[HSHolidayDays],

IIf([framepricing]=2,
IIf([frameHSType]=1 And [dogcount]<4,
[PHS1232NetH]*[HSHolidayDays],
IIf([frameHSType]=1 And [dogcount]>3,
[PHS1242NetH]*[HSHolidayDays],
IIf([frameHSType]=2 And [dogcount]<4,
[PHS2432NetH]*[HSHolidayDays],
IIf([frameHSType]=2 And [dogcount]>3,
[PHS2442NetH]*[HSHolidayDays])))))))))))
Click to expand...

It seems likely that you need to close off one or more of those IIf
calls sooner than the end of the whole controlsource expression.
However, I'm not eager to jump in and start guessing what your logic is
supposed to be. Could you state the intended logic of the expression
clearly in words? One thing I see is that the field [HSHolidayDays] is
essentially constant and could be filtered out of the more complex
nested IIf() calls. I *think* your intention is along these lines:

If [frameService]<>3 Or [dogcount]=0
Then

Return 0

Else

Return [HSHolidayDays] * the value returned by:

If [framepricing]=1
Then

When [frameHSType]=1 And [dogcount]<4
Return [PHS1231NetH]

When [frameHSType]=1 And [dogcount]>3
Return [PHS1241NetH]

When [frameHSType]=2 And [dogcount]<4
Return [PHS2431NetH]

When [frameHSType]=2 And [dogcount]>3
Return [PHS2441NetH]

Else If [framepricing]=2
Then

When [frameHSType]=1 And [dogcount]<4
Return [PHS1232NetH]

When [frameHSType]=1 And [dogcount]>3
Return [PHS1242NetH]

When [frameHSType]=2 And [dogcount]<4
Return [PHS2432NetH]

When [frameHSType]=2 And [dogcount]>3
Return [PHS2442NetH]

End If

Is that a correct interpretation? You may be getting a "wrong number of
arguments" error because there's no "else" clause in your IIf expression
for what to return if [framepricing] is neither 1 nor 2.

I think you could simplify this expression by using the Choose()
function instead of IIf() for the various "when" clauses.
 
Sam (antha) said:
Dirk, thank you. Yes, your interpretation is correct.
However, even though I follow your explanation of what's
missing, I am not anywhere near the ability level to do
that. Is there a simplified approach for me?
Click to expand...

If you can tell me what should be returned in the following cases, I'll
see if I can translate that into a working controlsource expression.
These are the undefined cases; what should be returned for each case?

[framepricing] is neither 1 nor 2

[framepricing] = 1, but [frameHSType] is neither 1 nor 2

[framepricing] = 2, but [frameHSType] is neither 1 nor 2

I expect you're going to tell me that these cases can't happen. We can
either allow for them -- possibly returning 0 or Null -- or we can
restructure the logic to use simpler "if/else" logic rather than case
logic.
 
Thanks again, Dirk.

[framePricing] and [frameHSType] are each option groups
with only two choices each, as the user selects from each
option group.

The code I posted originally:

1. does return the 0 properly if [frameService]<>3 or
[DogCount]=0.

2. does return values correctly where [framePricing]=1,
for each [frameHSType]=1 and [frameHSType]=2.

3. does NOT return anything for [framePricing]=2. Well,
does return an empty field, overwriting 0 or anything
else there. (I know that's some kind of clue!)

4. does return "err" when I place -- ,"err" -- after
the final [HSHolidayDays] and ahead of the first of the
last ")".

In sum, all works fine except [framePricing]=2. I know I
am leapfrogging it by not placing a ")" correctly, but
cannot see where.

If you can offer simplicity, I will gladly accept - and
learn -- maybe!

Sam





-----Original Message-----
Dirk, thank you. Yes, your interpretation is correct.
However, even though I follow your explanation of what's
missing, I am not anywhere near the ability level to do
that. Is there a simplified approach for me?
Click to expand...

If you can tell me what should be returned in the following cases, I'll
see if I can translate that into a working controlsource expression.
These are the undefined cases; what should be returned for each case?

[framepricing] is neither 1 nor 2

[framepricing] = 1, but [frameHSType] is neither 1 nor 2

[framepricing] = 2, but [frameHSType] is neither 1 nor 2

I expect you're going to tell me that these cases can't happen. We can
either allow for them -- possibly returning 0 or Null -- or we can
restructure the logic to use simpler "if/else" logic rather than case
logic.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
I think I have it now. Thanks for the guidance.

-----Original Message-----
Dirk, thank you. Yes, your interpretation is correct.
However, even though I follow your explanation of what's
missing, I am not anywhere near the ability level to do
that. Is there a simplified approach for me?
Click to expand...

If you can tell me what should be returned in the following cases, I'll
see if I can translate that into a working controlsource expression.
These are the undefined cases; what should be returned for each case?

[framepricing] is neither 1 nor 2

[framepricing] = 1, but [frameHSType] is neither 1 nor 2

[framepricing] = 2, but [frameHSType] is neither 1 nor 2

I expect you're going to tell me that these cases can't happen. We can
either allow for them -- possibly returning 0 or Null -- or we can
restructure the logic to use simpler "if/else" logic rather than case
logic.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Sam (antha) said:
I think I have it now. Thanks for the guidance.
Click to expand...

I was just about to put something together for you, but I'm happy to see
that you figured it out for yourself. Post back in this thread if you
run into any snags.
 
Back
Top