Sum subform total with tax

[Guest]

I have a database set up to help me track my expenses, a free version of
quicken I guess. I have a table and a subtable to mimic a receipt. I set up
a form and a subform for easy entry. I have an iif function set up on my
subform to add tax on receipt items if the tax button is checked to give a
"total w/ tax" column.
I would like to set up a text box on the main form that shows the receipt
total. I am able to do it in a subform text box with the expression
=Sum(IIf([Tax]=Yes,Round([Amount]*1.0825,2),[Amount])). This makes the total
receipt amount show up on every row, which is fine I guess but I would like
it to just show up in one place on the main form. When I try I get #error in
my box. I have set up a query that tabulates this data but I can seem to
reference that either, I get #name?.
I'm fairly new to access but am a wiz at excel so this is getting
frustrating with my formulas not working, sorry "expressions." Any help
would be great.

 

[Michel Walsh]

Hi,


Try to rename the control name to something that won't be an existing field.

Remove the =Yes. Just:

=Sum( iif( Tax, Round(Amount*1.0825, 2 ), Amount ) )


should do.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I get an #Error, should we try soemthing with the query?

Hi,


Try to rename the control name to something that won't be an existing field.

Remove the =Yes. Just:

=Sum( iif( Tax, Round(Amount*1.0825, 2 ), Amount ) )


should do.


Hoping it may help,
Vanderghast, Access MVP

I have a database set up to help me track my expenses, a free version of
quicken I guess. I have a table and a subtable to mimic a receipt. I set
up
a form and a subform for easy entry. I have an iif function set up on my
subform to add tax on receipt items if the tax button is checked to give a
"total w/ tax" column.
I would like to set up a text box on the main form that shows the receipt
total. I am able to do it in a subform text box with the expression
=Sum(IIf([Tax]=Yes,Round([Amount]*1.0825,2),[Amount])). This makes the
total
receipt amount show up on every row, which is fine I guess but I would
like
it to just show up in one place on the main form. When I try I get #error
in
my box. I have set up a query that tabulates this data but I can seem to
reference that either, I get #name?.
I'm fairly new to access but am a wiz at excel so this is getting
frustrating with my formulas not working, sorry "expressions." Any help
would be great.

 

[Michel Walsh]

Hi,


I don't think so. You can see if the query, in the query designer works fine
to get a definitive diagnostic, though.




Can you execute, in the immediate debug window:


? Round( 1.123212, 2)


If not, you have a reference problem (Tools | Reference... then uncheck the
reference(s) starting their description with MISSING ).



Vanderghast, Access MVP

I get an #Error, should we try soemthing with the query?

Hi,


Try to rename the control name to something that won't be an existing
field.

Remove the =Yes. Just:

=Sum( iif( Tax, Round(Amount*1.0825, 2 ), Amount ) )


should do.


Hoping it may help,
Vanderghast, Access MVP


message

I have a database set up to help me track my expenses, a free version of
quicken I guess. I have a table and a subtable to mimic a receipt. I
set
up
a form and a subform for easy entry. I have an iif function set up on
my
subform to add tax on receipt items if the tax button is checked to
give a
"total w/ tax" column.
I would like to set up a text box on the main form that shows the
receipt
total. I am able to do it in a subform text box with the expression
=Sum(IIf([Tax]=Yes,Round([Amount]*1.0825,2),[Amount])). This makes the
total
receipt amount show up on every row, which is fine I guess but I would
like
it to just show up in one place on the main form. When I try I get
#error
in
my box. I have set up a query that tabulates this data but I can seem
to
reference that either, I get #name?.
I'm fairly new to access but am a wiz at excel so this is getting
frustrating with my formulas not working, sorry "expressions." Any
help
would be great.

 

[Guest]

I get the results that I want in the query and in the subform. I'm wanting
that number to show up in a text box on main form. The round expression
works fine.

Hi,


I don't think so. You can see if the query, in the query designer works fine
to get a definitive diagnostic, though.




Can you execute, in the immediate debug window:


? Round( 1.123212, 2)


If not, you have a reference problem (Tools | Reference... then uncheck the
reference(s) starting their description with MISSING ).



Vanderghast, Access MVP

I get an #Error, should we try soemthing with the query?

Hi,


Try to rename the control name to something that won't be an existing
field.

Remove the =Yes. Just:

=Sum( iif( Tax, Round(Amount*1.0825, 2 ), Amount ) )


should do.


Hoping it may help,
Vanderghast, Access MVP


message
I have a database set up to help me track my expenses, a free version of
quicken I guess. I have a table and a subtable to mimic a receipt. I
set
up
a form and a subform for easy entry. I have an iif function set up on
my
subform to add tax on receipt items if the tax button is checked to
give a
"total w/ tax" column.
I would like to set up a text box on the main form that shows the
receipt
total. I am able to do it in a subform text box with the expression
=Sum(IIf([Tax]=Yes,Round([Amount]*1.0825,2),[Amount])). This makes the
total
receipt amount show up on every row, which is fine I guess but I would
like
it to just show up in one place on the main form. When I try I get
#error
in
my box. I have set up a query that tabulates this data but I can seem
to
reference that either, I get #name?.
I'm fairly new to access but am a wiz at excel so this is getting
frustrating with my formulas not working, sorry "expressions." Any
help
would be great.

 

[Guest]

It sounds like you were answering someone else's question.

Hi,



And if you have changed the name of the control?


Example, in Northwind, create a new report, based on table Categories. Bring
the CategoryName field in the detail section of that new report. In the
control, change the Control Source to:

="+" & CategoryName


as per the included picture, and then, if you switch to preview, you get the
#error.


Now, back in design mode, change the control name into, say, Agaguk, (first
line of the properties displayed in the picture) and the error disappear!


Hoping it may help,
Vanderghast, Access MVP

I get the results that I want in the query and in the subform. I'm wanting
that number to show up in a text box on main form. The round expression
works fine.

Hi,


I don't think so. You can see if the query, in the query designer works
fine
to get a definitive diagnostic, though.




Can you execute, in the immediate debug window:


? Round( 1.123212, 2)


If not, you have a reference problem (Tools | Reference... then uncheck
the
reference(s) starting their description with MISSING ).



Vanderghast, Access MVP


I get an #Error, should we try soemthing with the query?

:

Hi,


Try to rename the control name to something that won't be an existing
field.

Remove the =Yes. Just:

=Sum( iif( Tax, Round(Amount*1.0825, 2 ), Amount ) )


should do.


Hoping it may help,
Vanderghast, Access MVP


message
I have a database set up to help me track my expenses, a free version
of
quicken I guess. I have a table and a subtable to mimic a receipt.
I
set
up
a form and a subform for easy entry. I have an iif function set up
on
my
subform to add tax on receipt items if the tax button is checked to
give a
"total w/ tax" column.
I would like to set up a text box on the main form that shows the
receipt
total. I am able to do it in a subform text box with the expression
=Sum(IIf([Tax]=Yes,Round([Amount]*1.0825,2),[Amount])). This makes
the
total
receipt amount show up on every row, which is fine I guess but I
would
like
it to just show up in one place on the main form. When I try I get
#error
in
my box. I have set up a query that tabulates this data but I can
seem
to
reference that either, I get #name?.
I'm fairly new to access but am a wiz at excel so this is getting
frustrating with my formulas not working, sorry "expressions." Any
help
would be great.