Using Totals (1 Viewer)

[tmyers]

Is it possible to use the Totals function built into Access on a calculated field/control? When I tried it, my only option was Count. I am hoping I can use it for a quick and dirty way to show a running total off a field that is multiplying cost times quantity (and a few other things). The form itself is in datasheet view.

Extended Unit is cost plus other adders such as freight and "Total" is that multiplied by quantity.

 

[The_Doc_Man]

If you wanted a total and what you showed us comes from an actual named query, you could get the sum via an independent control using DSum of that field in that same query. I've not tried that feature that you are using so I would have to research it a bit. Note that if the query exists only the recordsource of what we are seeing, DSum won't work because it DOES require its domain to be a named query. (Or table.)

 

[theDBguy]

Hi. I haven't checked; but if it's not in the options, then probably not. I don't know if you can force the issue using code.

 

[CJ_London]

sounds like your total column is actually text - have you used the format function and right justified the column?

 

[theDBguy]

sounds like your total column is actually text - have you used the format function and right justified the column?

Okay, I got a chance to give it a try. I agree with @CJ_London, I was able to see all the options in my quick test.

 

[Pat Hartman]

If you are using the Format() function, that might be what is making the column into a string. Use the form's format property to format without converting the data to a string.

 

[tmyers]

If you are using the Format() function, that might be what is making the column into a string. Use the form's format property to format without converting the data to a string.

Not quite following completely. Are you referring to this?

The actual expression behind this is:
=[Extended Unit]*[Quantity]
Extended Unit is a large if is null block expression:
Extended Unit: [Price]+IIf(IsNull([Freight]),0,[Freight])+(IIf(IsNull([LampPrice]),0,[LampPrice])*IIf(IsNull([NumberOfLamps]),0,[NumberOfLamps]))

If that isn't what you are referring to, then I am afraid that I lost you.

 

[CJ_London]

perhaps use the nz function instead

Extended Unit: nz([Price],0)+nz([Freight],0)+nz([LampPrice],0)*nz([NumberOfLamps],0)

or wrap in the cCurr function

Extended Unit: cCurr(nz([Price],0)+nz([Freight],0)+nz([LampPrice],0)*nz([NumberOfLamps],0))

 

[tmyers]

Trying the cCurr comes up as undefined.
Edit:
Its cCur. One "r".

 

[tmyers]

I feel dumb now. While messing with that I realized the control was set improperly which is what was causing it.

 

[theDBguy]

I feel dumb now. While messing with that I realized the control was set improperly which is what was causing it.

Glad to hear you got it sorted out.