Nested IIF's

[graeme34 via AccessMonster.com]

Hi I am trying to use the nested IIF statement in some textbox controls on a
report...
Although I am getting a syntax error on them:

txtPostingType has this:
IIf([Credit]>0,IIF([ItemBalance]<0,"Payment(part of " & Me.Credit &")",
"Payment"),Null)

txtItemBalance has this:
=IIF([ItemBalance]=[Debit], [Debit],0)

and txtCredit has this:
=IIf([Credit]>0,IIf([ItemBalance]<0,[Debit],[Credit]),Null)

could somebody please point out my error?

 

[Ken Snell \(MVP\)]

Need a space on either side of the & character:

IIf([Credit]>0,IIF([ItemBalance]<0,"Payment(part of " & Me.Credit & ")",
"Payment"),Null)

What you posted does not have a space on right side of & character after the
Me.Credit portion.

 

[graeme34 via AccessMonster.com]

Hi Ken
Although that was an error on my part......it didnt seem to fix it, as it is
still returning a syntax error (comma) in query expression??
I am also getting this on the non nested IIF is it due to the expression(s) I
have in the underlying query??

Need a space on either side of the & character:

IIf([Credit]>0,IIF([ItemBalance]<0,"Payment(part of " & Me.Credit & ")",
"Payment"),Null)

What you posted does not have a space on right side of & character after the
Me.Credit portion.

Hi I am trying to use the nested IIF statement in some textbox controls on
a

[quoted text clipped - 12 lines]

could somebody please point out my error?

 

[Ken Snell \(MVP\)]

I don't see any obvious error in what you posted otherwise. Unless you have
an embedded " character in the Me.Credit value?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken
Although that was an error on my part......it didnt seem to fix it, as it
is
still returning a syntax error (comma) in query expression??
I am also getting this on the non nested IIF is it due to the
expression(s) I
have in the underlying query??

Need a space on either side of the & character:

IIf([Credit]>0,IIF([ItemBalance]<0,"Payment(part of " & Me.Credit & ")",
"Payment"),Null)

What you posted does not have a space on right side of & character after
the
Me.Credit portion.

Hi I am trying to use the nested IIF statement in some textbox controls
on
a

[quoted text clipped - 12 lines]

could somebody please point out my error?

 

[graeme34 via AccessMonster.com]

Hi Ken (and anybody else who could help me out!)

After standing back and looking at what I am trying to do I'm not too sure it
is allowed....
The three text box control appear on a subreport detail section....inside
each (control source) is the following nested IIF
txtPostingType has this:
=IIf([Credit]>0,IIF([ItemBalance]<0,"Payment(part of " & Me.Credit & ")",
"Payment"),Null)

txtItemBalance has this:
=IIF([ItemBalance]=[Debit], [Debit],0)

and txtCredit has this:
=IIf([Credit]>0,IIf([ItemBalance]<0,[Debit],[Credit]),Null)

Debit, Credit, and ItemBalance are all currency data type.....
I think my problem lies in the fact I am trying to alter the textboxes
differently for each detail line, (I think)
is this allowed....if so how I tried similar code in the format event of the
subreport....but had no joy
Thanks Graeme...

I don't see any obvious error in what you posted otherwise. Unless you have
an embedded " character in the Me.Credit value?

Hi Ken
Although that was an error on my part......it didnt seem to fix it, as it

[quoted text clipped - 19 lines]

 

[graeme34 via AccessMonster.com]

Just a little update...I 've found a long winded way around it...
For some unknown reason....ItemBalance...wasn't appearing in the underlying
'code'
by this I mean if I was in the On format event,and started typing Me.
ItemBalance....then after the . a list of methods or control sources appear...
..ItemBalance was not in this list?
Although in the report design view if I clicked on Field List it appeared
there as well as in the control sourse property of a text box ?????
Therefore I used an Alias expression Balance:ItemBalance in the control
source query of the subreport, then it allowed me to refer to the 'Field'
Balance....I then added the following code on the Format event of the subrep..
..

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Balance <> Me.Debit Then
If Me.Balance < 0 Then
Me.txtItemBalance = 0
Me.txtPostingType = " Payment (part of " & Me.Credit _
& ")"
Me.txtCredit = Me.Debit
Else
Me.txtItemBalance = 0
Me.txtPostingType = "Payment"
Me.txtCredit = Me.Credit
End If
Else
Me.txtItemBalance = Me.Debit
Me.txtPostingType = Null
Me.txtCredit = Null
End If
End Sub
I had to make the txtboxes unbound, therefore I had to bring Balance and
Credit controls back into the report in hidden textboxes....longwinded but it
works!
Although I would be grateful of any simpler suggestions.

Hi Ken (and anybody else who could help me out!)

After standing back and looking at what I am trying to do I'm not too sure it
is allowed....
The three text box control appear on a subreport detail section....inside
each (control source) is the following nested IIF
txtPostingType has this:
=IIf([Credit]>0,IIF([ItemBalance]<0,"Payment(part of " & Me.Credit & ")",
"Payment"),Null)

txtItemBalance has this:
=IIF([ItemBalance]=[Debit], [Debit],0)

and txtCredit has this:
=IIf([Credit]>0,IIf([ItemBalance]<0,[Debit],[Credit]),Null)

Debit, Credit, and ItemBalance are all currency data type.....
I think my problem lies in the fact I am trying to alter the textboxes
differently for each detail line, (I think)
is this allowed....if so how I tried similar code in the format event of the
subreport....but had no joy
Thanks Graeme...

I don't see any obvious error in what you posted otherwise. Unless you have
an embedded " character in the Me.Credit value?

[quoted text clipped - 4 lines]

 

[Ken Snell \(MVP\)]

Not knowing what the subreport's RecordSource SQL statement was to begin
with, my first guess is that it contained more than one field named
ItemBalance, so ACCESS then uses the full Tablename.Fieldname syntax to
identify the field. Or you can use an Alias as an easier and more foolproof
method.

--

Ken Snell
<MS ACCESS MVP>

Just a little update...I 've found a long winded way around it...
For some unknown reason....ItemBalance...wasn't appearing in the
underlying
'code'
by this I mean if I was in the On format event,and started typing Me.
ItemBalance....then after the . a list of methods or control sources
appear...
.ItemBalance was not in this list?
Although in the report design view if I clicked on Field List it appeared
there as well as in the control sourse property of a text box ?????
Therefore I used an Alias expression Balance:ItemBalance in the control
source query of the subreport, then it allowed me to refer to the 'Field'
Balance....I then added the following code on the Format event of the
subrep..
.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Balance <> Me.Debit Then
If Me.Balance < 0 Then
Me.txtItemBalance = 0
Me.txtPostingType = " Payment (part of " & Me.Credit _
& ")"
Me.txtCredit = Me.Debit
Else
Me.txtItemBalance = 0
Me.txtPostingType = "Payment"
Me.txtCredit = Me.Credit
End If
Else
Me.txtItemBalance = Me.Debit
Me.txtPostingType = Null
Me.txtCredit = Null
End If
End Sub
I had to make the txtboxes unbound, therefore I had to bring Balance and
Credit controls back into the report in hidden textboxes....longwinded but
it
works!
Although I would be grateful of any simpler suggestions.

Hi Ken (and anybody else who could help me out!)

After standing back and looking at what I am trying to do I'm not too sure
it
is allowed....
The three text box control appear on a subreport detail section....inside
each (control source) is the following nested IIF
txtPostingType has this:
=IIf([Credit]>0,IIF([ItemBalance]<0,"Payment(part of " & Me.Credit & ")",
"Payment"),Null)

txtItemBalance has this:
=IIF([ItemBalance]=[Debit], [Debit],0)

and txtCredit has this:
=IIf([Credit]>0,IIf([ItemBalance]<0,[Debit],[Credit]),Null)

Debit, Credit, and ItemBalance are all currency data type.....
I think my problem lies in the fact I am trying to alter the textboxes
differently for each detail line, (I think)
is this allowed....if so how I tried similar code in the format event of
the
subreport....but had no joy
Thanks Graeme...

I don't see any obvious error in what you posted otherwise. Unless you
have
an embedded " character in the Me.Credit value?

[quoted text clipped - 4 lines]

could somebody please point out my error?

 

[graeme34 via AccessMonster.com]

Hi Ken,
Thanks for the explanation..the actual SQL statement of the recordsourse only
contained one refernce to ItemBalance, although this was an expression....
ItemBalance: [Debit]-[Credit]....would this be the cause?

Not knowing what the subreport's RecordSource SQL statement was to begin
with, my first guess is that it contained more than one field named
ItemBalance, so ACCESS then uses the full Tablename.Fieldname syntax to
identify the field. Or you can use an Alias as an easier and more foolproof
method.

Just a little update...I 've found a long winded way around it...
For some unknown reason....ItemBalance...wasn't appearing in the

[quoted text clipped - 67 lines]

 

[Ken Snell \(MVP\)]

Sounds like your query's SQL is ok for having only one "use" of ItemBalance.
Sometimes, in order to use a field in a Control Source expression for a
textbox, there needs to be a control (can be hidden) that is bound to that
field on the report; this then makes the "field" available for other
expressions to use. Otherwise, I have no other ideas.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the explanation..the actual SQL statement of the recordsourse
only
contained one refernce to ItemBalance, although this was an expression....
ItemBalance: [Debit]-[Credit]....would this be the cause?

Not knowing what the subreport's RecordSource SQL statement was to begin
with, my first guess is that it contained more than one field named
ItemBalance, so ACCESS then uses the full Tablename.Fieldname syntax to
identify the field. Or you can use an Alias as an easier and more
foolproof
method.

Just a little update...I 've found a long winded way around it...
For some unknown reason....ItemBalance...wasn't appearing in the

[quoted text clipped - 67 lines]

could somebody please point out my error?

 

[graeme34 via AccessMonster.com]

Hi Ken.....thanks for your postings......the unpredictability of Access eh
I have at least found a way around it, albeit longwinded.
Thanks again
Graeme.

Sounds like your query's SQL is ok for having only one "use" of ItemBalance.
Sometimes, in order to use a field in a Control Source expression for a
textbox, there needs to be a control (can be hidden) that is bound to that
field on the report; this then makes the "field" available for other
expressions to use. Otherwise, I have no other ideas.

Hi Ken,
Thanks for the explanation..the actual SQL statement of the recordsourse

[quoted text clipped - 13 lines]