Why =sum([BilledAmount]) doesn't work

[V.P.]

Hi all,
On my report,I set a textbox's control source =sum([BilledAmount]) but
doesn't work, when i run it has #Error.
My original data type for BilledAmount colume is Text, but when I query I
used CDbl(BilledAmount) to convert to double. What should I do in able to sum
at the footnote of the report?
Thanks

 

[Duane Hookom]

You can't sum text values. You can easily use:
=sum(Val([BilledAmount]) )

Since there is no "footnote of the report" I assume you mean the Report
Footer section (not the Page Footer).

 

[V.P.]

Hi Duane,
Thanks for your reply. Yes, Report Footer. I tried your idea but still
doesn't work.

My problems are: the table is linked table, and all data type of the fields
of that table are text. So when I created a query for the report, I convert
the field that I want to Double by CDbl(FieldName), so I can format that
field to Currency but I can't do sum, I'm not sure what I need to do in order
to use sum function...

You can't sum text values. You can easily use:
=sum(Val([BilledAmount]) )

Since there is no "footnote of the report" I assume you mean the Report
Footer section (not the Page Footer).
--
Duane Hookom
MS Access MVP

Hi all,
On my report,I set a textbox's control source =sum([BilledAmount]) but
doesn't work, when i run it has #Error.
My original data type for BilledAmount colume is Text, but when I query I
used CDbl(BilledAmount) to convert to double. What should I do in able to
sum
at the footnote of the report?
Thanks

 

[Duane Hookom]

Can you provide more detail than "still doesn't work". We don't know if you
are getting an error or wrong value or whatever.

What is the actual field name and how is it displayed (left or
right-aligned) in the datasheet view of the report's record source query?
--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for your reply. Yes, Report Footer. I tried your idea but still
doesn't work.

My problems are: the table is linked table, and all data type of the
fields
of that table are text. So when I created a query for the report, I
convert
the field that I want to Double by CDbl(FieldName), so I can format that
field to Currency but I can't do sum, I'm not sure what I need to do in
order
to use sum function...

You can't sum text values. You can easily use:
=sum(Val([BilledAmount]) )

Since there is no "footnote of the report" I assume you mean the Report
Footer section (not the Page Footer).
--
Duane Hookom
MS Access MVP

Hi all,
On my report,I set a textbox's control source =sum([BilledAmount]) but
doesn't work, when i run it has #Error.
My original data type for BilledAmount colume is Text, but when I query
I
used CDbl(BilledAmount) to convert to double. What should I do in able
to
sum
at the footnote of the report?
Thanks

 

[V.P.]

Hi Duane,

Sorry for I wasn't clear with my question.
- It still give me this "#Error" in my sum's textbox.
- The field name AmountBilled.
- When I run the query, the data value display on the right-aligned of the
column. (I know this AmountBilled is now double or numberic cuz I have other
fields and they display on the left side, and also in report, I can't set
format to currency like AmountBilled)
Hope this clear my problem...

Can you provide more detail than "still doesn't work". We don't know if you
are getting an error or wrong value or whatever.

What is the actual field name and how is it displayed (left or
right-aligned) in the datasheet view of the report's record source query?
--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for your reply. Yes, Report Footer. I tried your idea but still
doesn't work.

My problems are: the table is linked table, and all data type of the
fields
of that table are text. So when I created a query for the report, I
convert
the field that I want to Double by CDbl(FieldName), so I can format that
field to Currency but I can't do sum, I'm not sure what I need to do in
order
to use sum function...

You can't sum text values. You can easily use:
=sum(Val([BilledAmount]) )

Since there is no "footnote of the report" I assume you mean the Report
Footer section (not the Page Footer).
--
Duane Hookom
MS Access MVP

Hi all,
On my report,I set a textbox's control source =sum([BilledAmount]) but
doesn't work, when i run it has #Error.
My original data type for BilledAmount colume is Text, but when I query
I
used CDbl(BilledAmount) to convert to double. What should I do in able
to
sum
at the footnote of the report?
Thanks

 

[Duane Hookom]

If the field is right-aligned in the datasheet view, this suggests the field
is numeric. You should be able to use:
=Sum([YourField])
in the control source of a text box in the Report Footer section. The #Error
suggests the text box is in the Page Footer, not the Report Footer.

Are there definitely records returned in the report? Have you tried creating
a new report with the same record source and just one control.

Section: Report Footer
Name: txtSum
Control Source:=Sum([Your Field Name])


--
Duane Hookom
MS Access MVP

Hi Duane,

Sorry for I wasn't clear with my question.
- It still give me this "#Error" in my sum's textbox.
- The field name AmountBilled.
- When I run the query, the data value display on the right-aligned of the
column. (I know this AmountBilled is now double or numberic cuz I have
other
fields and they display on the left side, and also in report, I can't set
format to currency like AmountBilled)
Hope this clear my problem...

Can you provide more detail than "still doesn't work". We don't know if
you
are getting an error or wrong value or whatever.

What is the actual field name and how is it displayed (left or
right-aligned) in the datasheet view of the report's record source query?
--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for your reply. Yes, Report Footer. I tried your idea but still
doesn't work.

My problems are: the table is linked table, and all data type of the
fields
of that table are text. So when I created a query for the report, I
convert
the field that I want to Double by CDbl(FieldName), so I can format
that
field to Currency but I can't do sum, I'm not sure what I need to do in
order
to use sum function...


:

You can't sum text values. You can easily use:
=sum(Val([BilledAmount]) )

Since there is no "footnote of the report" I assume you mean the
Report
Footer section (not the Page Footer).
--
Duane Hookom
MS Access MVP

Hi all,
On my report,I set a textbox's control source =sum([BilledAmount])
but
doesn't work, when i run it has #Error.
My original data type for BilledAmount colume is Text, but when I
query
I
used CDbl(BilledAmount) to convert to double. What should I do in
able
to
sum
at the footnote of the report?
Thanks

 

[V.P.]

Hi Duane,
Thanks. It work. You're right, I put the sum in Page Footer. So I tried
with Report Footer and it work.

By the way, I have date field in text data type, and I would like to convert
it to date data type (it's a link table to Lotus database so I can't change
the table structure, but have to work around to find the way only). Do you
know how to convert text( like 04/07/06) to date so when the user want to
sort (order by) date then it sort as nornal date?

Thanks in advance,

If the field is right-aligned in the datasheet view, this suggests the field
is numeric. You should be able to use:
=Sum([YourField])
in the control source of a text box in the Report Footer section. The #Error
suggests the text box is in the Page Footer, not the Report Footer.

Are there definitely records returned in the report? Have you tried creating
a new report with the same record source and just one control.

Section: Report Footer
Name: txtSum
Control Source:=Sum([Your Field Name])


--
Duane Hookom
MS Access MVP

Hi Duane,

Sorry for I wasn't clear with my question.
- It still give me this "#Error" in my sum's textbox.
- The field name AmountBilled.
- When I run the query, the data value display on the right-aligned of the
column. (I know this AmountBilled is now double or numberic cuz I have
other
fields and they display on the left side, and also in report, I can't set
format to currency like AmountBilled)
Hope this clear my problem...

Can you provide more detail than "still doesn't work". We don't know if
you
are getting an error or wrong value or whatever.

What is the actual field name and how is it displayed (left or
right-aligned) in the datasheet view of the report's record source query?
--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for your reply. Yes, Report Footer. I tried your idea but still
doesn't work.

My problems are: the table is linked table, and all data type of the
fields
of that table are text. So when I created a query for the report, I
convert
the field that I want to Double by CDbl(FieldName), so I can format
that
field to Currency but I can't do sum, I'm not sure what I need to do in
order
to use sum function...


:

You can't sum text values. You can easily use:
=sum(Val([BilledAmount]) )

Since there is no "footnote of the report" I assume you mean the
Report
Footer section (not the Page Footer).
--
Duane Hookom
MS Access MVP

Hi all,
On my report,I set a textbox's control source =sum([BilledAmount])
but
doesn't work, when i run it has #Error.
My original data type for BilledAmount colume is Text, but when I
query
I
used CDbl(BilledAmount) to convert to double. What should I do in
able
to
sum
at the footnote of the report?
Thanks

 

[Duane Hookom]

Try CDate([YourTextdateField])


--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks. It work. You're right, I put the sum in Page Footer. So I tried
with Report Footer and it work.

By the way, I have date field in text data type, and I would like to
convert
it to date data type (it's a link table to Lotus database so I can't
change
the table structure, but have to work around to find the way only). Do
you
know how to convert text( like 04/07/06) to date so when the user want to
sort (order by) date then it sort as nornal date?

Thanks in advance,

If the field is right-aligned in the datasheet view, this suggests the
field
is numeric. You should be able to use:
=Sum([YourField])
in the control source of a text box in the Report Footer section. The
#Error
suggests the text box is in the Page Footer, not the Report Footer.

Are there definitely records returned in the report? Have you tried
creating
a new report with the same record source and just one control.

Section: Report Footer
Name: txtSum
Control Source:=Sum([Your Field Name])


--
Duane Hookom
MS Access MVP

Hi Duane,

Sorry for I wasn't clear with my question.
- It still give me this "#Error" in my sum's textbox.
- The field name AmountBilled.
- When I run the query, the data value display on the right-aligned of
the
column. (I know this AmountBilled is now double or numberic cuz I have
other
fields and they display on the left side, and also in report, I can't
set
format to currency like AmountBilled)
Hope this clear my problem...


:

Can you provide more detail than "still doesn't work". We don't know
if
you
are getting an error or wrong value or whatever.

What is the actual field name and how is it displayed (left or
right-aligned) in the datasheet view of the report's record source
query?
--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for your reply. Yes, Report Footer. I tried your idea but
still
doesn't work.

My problems are: the table is linked table, and all data type of the
fields
of that table are text. So when I created a query for the report, I
convert
the field that I want to Double by CDbl(FieldName), so I can format
that
field to Currency but I can't do sum, I'm not sure what I need to do
in
order
to use sum function...


:

You can't sum text values. You can easily use:
=sum(Val([BilledAmount]) )

Since there is no "footnote of the report" I assume you mean the
Report
Footer section (not the Page Footer).
--
Duane Hookom
MS Access MVP

Hi all,
On my report,I set a textbox's control source
=sum([BilledAmount])
but
doesn't work, when i run it has #Error.
My original data type for BilledAmount colume is Text, but when I
query
I
used CDbl(BilledAmount) to convert to double. What should I do in
able
to
sum
at the footnote of the report?
Thanks

 

[V.P.]

Hi Duane,
Thanks for help. I'm appreacited it!

Try CDate([YourTextdateField])


--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks. It work. You're right, I put the sum in Page Footer. So I tried
with Report Footer and it work.

By the way, I have date field in text data type, and I would like to
convert
it to date data type (it's a link table to Lotus database so I can't
change
the table structure, but have to work around to find the way only). Do
you
know how to convert text( like 04/07/06) to date so when the user want to
sort (order by) date then it sort as nornal date?

Thanks in advance,

If the field is right-aligned in the datasheet view, this suggests the
field
is numeric. You should be able to use:
=Sum([YourField])
in the control source of a text box in the Report Footer section. The
#Error
suggests the text box is in the Page Footer, not the Report Footer.

Are there definitely records returned in the report? Have you tried
creating
a new report with the same record source and just one control.

Section: Report Footer
Name: txtSum
Control Source:=Sum([Your Field Name])


--
Duane Hookom
MS Access MVP

Hi Duane,

Sorry for I wasn't clear with my question.
- It still give me this "#Error" in my sum's textbox.
- The field name AmountBilled.
- When I run the query, the data value display on the right-aligned of
the
column. (I know this AmountBilled is now double or numberic cuz I have
other
fields and they display on the left side, and also in report, I can't
set
format to currency like AmountBilled)
Hope this clear my problem...


:

Can you provide more detail than "still doesn't work". We don't know
if
you
are getting an error or wrong value or whatever.

What is the actual field name and how is it displayed (left or
right-aligned) in the datasheet view of the report's record source
query?
--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for your reply. Yes, Report Footer. I tried your idea but
still
doesn't work.

My problems are: the table is linked table, and all data type of the
fields
of that table are text. So when I created a query for the report, I
convert
the field that I want to Double by CDbl(FieldName), so I can format
that
field to Currency but I can't do sum, I'm not sure what I need to do
in
order
to use sum function...


:

You can't sum text values. You can easily use:
=sum(Val([BilledAmount]) )

Since there is no "footnote of the report" I assume you mean the
Report
Footer section (not the Page Footer).
--
Duane Hookom
MS Access MVP

Hi all,
On my report,I set a textbox's control source
=sum([BilledAmount])
but
doesn't work, when i run it has #Error.
My original data type for BilledAmount colume is Text, but when I
query
I
used CDbl(BilledAmount) to convert to double. What should I do in
able
to
sum
at the footnote of the report?
Thanks