Sum Function - sum option not available for column of numbers

[Slappy]

There is one column in my query that I want to add up. The column is
comprised of numbers that were selected from a drop down list on a form.
When I use the sum function to create a "total row", the only available
options for this column are "none" and "count". It is as if the program sees
this column as text. I'm not sure what is wrong.

Any help would be greatly appreciated.

 

[KC-Mass]

You need to forget the query and create the "Total Row" in your form.

You are not going to reference the fields in your query but the controls in
your form.

Thus if you have a column in your query named SalesTax and and that appears
in a list control named
lstTaxes in which you make multiple selections then you will add up the
value of each selected item.
Walking the ItemsSelected would look like :

For Each varItm In ctl.ItemsSelected
Debug.Print ctl.ItemData(varItm)
Next varItm

Regards

Kevin

 

[Slappy]

Sorry I'm not getting it.

I was actually trying to get the query to spit back the right number (the
sum of the column of numbers) so I could work forward from there and put the
result on my report. I didn't have any luck with the report. I made the
report using the wizard. The sum function gives me a number that is not the
sum of the column of numbers.

What I am trying to do is create a time sheet for various tasks with the
actual time taken to do each task selected from a drop down list (0.1, 0.2,
0.3, 0.4 etc.). A recordset consists of a text field (eg. "called client and
discussed problem") and a time field (eg. 0.3 - representing 0.3 hours or 18
minutes).

This drop down list is based on another table with two fields, (1) ID and
(2) Time. The Time field contains the various possible entries (0.05, 0.1,
0.2, 0.3, 0.4 etc.).

It looks to me like the sum function in the report is adding up the ID
column and not the Time column (even though the Time column is what appears
in the report and in the query).

 

[Duane Hookom]

It seems that you have used a lookup field in your table design. Many of us
consider lookup fields as horrible. They mostly lead to confusion because one
value is displayed and another is stored.

If you want to include the actual looked up value in your query, you need to
add the lookup table and the appropriate field in your query. If your looked
up field is number (appears right-aligned) you can perform math on the
column.

If the column is actually defined as text, you will need to convert it to
numeric using CDbl() or Val() or similar.

 

[Slappy]

Thanks very much Duane. Your answer explains the problem (as well as some
other problems I have been having).

Thanks also to KC-Mass for a prompt reply to my question.