#ERROR - Pulling my hair out

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am retrieving a value called extendedprice that is contained within a sub
form called "Summary of vat 1 subform". If there is a value present I want
the calculation to multiply by 13.5 and divide by 100. If there is no value
present I would like the formula to return a zero and not #Error. Can anyone
help....I'm going out of my mind.

The subform is contained with a form called orders.

Thanks



=IIf([Forms]![orders]![summary of vat 1
subform].[HasData],[Forms]![orders]![summary of vat 1
subform].[Extendedprice]*13.5/100,0)
 
=IIf([Forms]![orders]![summary of vat 1
subform].[Form].[HasData],[Forms]![orders]![summary of vat 1
subform].[Extendedprice]*13.5/100,0)
Click to expand...

try

=IIf([Forms]![orders]![summary of vat 1 subform].[Form]![HasData], _
[Forms]![orders]![summary of vat 1 subform].[Form]![Extendedprice]*13.5/100,
0)

Note: linecontinuation mark in this example...

Krgrds,
Perry

Pat Eire said:
I am retrieving a value called extendedprice that is contained within a
sub
form called "Summary of vat 1 subform". If there is a value present I
want
the calculation to multiply by 13.5 and divide by 100. If there is no
value
present I would like the formula to return a zero and not #Error. Can
anyone
help....I'm going out of my mind.

The subform is contained with a form called orders.

Thanks



=IIf([Forms]![orders]![summary of vat 1
subform].[HasData],[Forms]![orders]![summary of vat 1
subform].[Extendedprice]*13.5/100,0)
Click to expand...
 
Thanks Perry for the fast reponse, it did't work though......

Pat

Perry said:
=IIf([Forms]![orders]![summary of vat 1
subform].[Form].[HasData],[Forms]![orders]![summary of vat 1
subform].[Extendedprice]*13.5/100,0)
Click to expand...

try

=IIf([Forms]![orders]![summary of vat 1 subform].[Form]![HasData], _
[Forms]![orders]![summary of vat 1 subform].[Form]![Extendedprice]*13.5/100,
0)

Note: linecontinuation mark in this example...

Krgrds,
Perry

Pat Eire said:
I am retrieving a value called extendedprice that is contained within a
sub
form called "Summary of vat 1 subform". If there is a value present I
want
the calculation to multiply by 13.5 and divide by 100. If there is no
value
present I would like the formula to return a zero and not #Error. Can
anyone
help....I'm going out of my mind.

The subform is contained with a form called orders.

Thanks



=IIf([Forms]![orders]![summary of vat 1
subform].[HasData],[Forms]![orders]![summary of vat 1
subform].[Extendedprice]*13.5/100,0)
Click to expand...
Click to expand...
 
hahaha
I'm trying here :-)

You left out the line continuation mark, as indicated previously, not?
Krgrds,
Perry

Pat Eire said:
Thanks Perry for the fast reponse, it did't work though......

Pat

Perry said:
=IIf([Forms]![orders]![summary of vat 1
subform].[Form].[HasData],[Forms]![orders]![summary of vat 1
subform].[Extendedprice]*13.5/100,0)
Click to expand...

try

=IIf([Forms]![orders]![summary of vat 1 subform].[Form]![HasData], _
[Forms]![orders]![summary of vat 1
subform].[Form]![Extendedprice]*13.5/100,
0)

Note: linecontinuation mark in this example...

Krgrds,
Perry

Pat Eire said:
I am retrieving a value called extendedprice that is contained within a
sub
form called "Summary of vat 1 subform". If there is a value present I
want
the calculation to multiply by 13.5 and divide by 100. If there is no
value
present I would like the formula to return a zero and not #Error. Can
anyone
help....I'm going out of my mind.

The subform is contained with a form called orders.

Thanks



=IIf([Forms]![orders]![summary of vat 1
subform].[HasData],[Forms]![orders]![summary of vat 1
subform].[Extendedprice]*13.5/100,0)
Click to expand...
Click to expand...
Click to expand...
 
Yep, I did - No joy..... :-(

Perry said:
hahaha
I'm trying here :-)

You left out the line continuation mark, as indicated previously, not?
Krgrds,
Perry

Pat Eire said:
Thanks Perry for the fast reponse, it did't work though......

Pat

Perry said:
=IIf([Forms]![orders]![summary of vat 1
subform].[Form].[HasData],[Forms]![orders]![summary of vat 1
subform].[Extendedprice]*13.5/100,0)

try

=IIf([Forms]![orders]![summary of vat 1 subform].[Form]![HasData], _
[Forms]![orders]![summary of vat 1
subform].[Form]![Extendedprice]*13.5/100,
0)

Note: linecontinuation mark in this example...

Krgrds,
Perry

"Pat Eire" <Pat (e-mail address removed)> schreef in bericht

I am retrieving a value called extendedprice that is contained within a
sub
form called "Summary of vat 1 subform". If there is a value present I
want
the calculation to multiply by 13.5 and divide by 100. If there is no
value
present I would like the formula to return a zero and not #Error. Can
anyone
help....I'm going out of my mind.

The subform is contained with a form called orders.

Thanks



=IIf([Forms]![orders]![summary of vat 1
subform].[HasData],[Forms]![orders]![summary of vat 1
subform].[Extendedprice]*13.5/100,0)
Click to expand...
Click to expand...
Click to expand...
 
Just an idea, which may be countered by the experts here, but try:

=IIf([Forms]![orders]![summary of vat 1
subform].[HasData],IIF(IsError([Forms]![orders]![summary of vat 1
subform]),0,[Forms]![orders]![summary of vat 1
subform]).[Extendedprice]*13.5/100,0)

Adds in an 'IsError' check within the IIF statement, so that if your
[Forms]![orders]![summary of vat 1 subform] value would normally return an
error, it shows a '0' instead.

This is assuming that there is potentially a value in [summary of vat 1
subform] and you want to see it if it is there.
 
Can said:
Just an idea, which may be countered by the experts here, but try:

=IIf([Forms]![orders]![summary of vat 1
subform].[HasData],IIF(IsError([Forms]![orders]![summary of vat 1
subform]),0,[Forms]![orders]![summary of vat 1
subform]).[Extendedprice]*13.5/100,0)

Adds in an 'IsError' check within the IIF statement, so that if your
[Forms]![orders]![summary of vat 1 subform] value would normally return an
error, it shows a '0' instead.

This is assuming that there is potentially a value in [summary of vat 1
subform] and you want to see it if it is there.
Click to expand...


I can't see the original question, but, since this is a
forms group, I thought I should mention that HasData only
works in reports.

Another common way to get #Error is if the text box is named
the same a field in the expression. The text box name
should be different from the names of fields in the
form/report's record source table/query.

In the later versions of Access, the Form property of a
subform control is required.

In the case where a subform might not have any records, the
expression should be:

=IIf(IsError([summary of vat 1 subform].Form.Extendedprice),
0,[summary of vat 1subform].Form.[Extendedprice]*13.5/100)
 
Back
Top