Displaying simple Query with Sum Expr into a textbox

[jasonbarnes]

Does anyone know how to do this? I've tried setting the control source to


[QrySum]![SUM] for example and it doesn't work. I get the generic #name error. I've seen one solution where someone used Dlookup. I just think should be much easier.

[Rawb]

I'm not sure exactly what you're trying to do. . .

If you have a Query that returns a Sum already, you can just reference that Field directly:

SELECT Sum([MyField]) AS SumField FROM MyTABLE

In this case you'd just use the following as your Form Field's Control Source:

=[SumField]

If you're dealing with a Query that returns multiple Records, then you can do the Summing in the Form without having to change the Query itself. In this instance, you don't want to use the Form Object's Control Source (leave it blank). Instead, use its Default Value.

=Sum([MyField])

[jasonbarnes]

Clicking the ... beside control source pops up the expression builder. From that I selected the Query then the SUM field. There is only one sum/record returned so I don't understand why this doesn't work. I even tried taking the [Qry] from in front which would put the syntax as your post suggest still with no luck.

Is it anything to do with not having a sub on the form that calls the Qry when the form is open. Sorry I'm having to reintroduce my self to the VB aspect after having a little 5 or so years ago.

[Rawb]

Well, from what you just said, I think you're doing everything correctly. I'm not 100% sure though because I've don't use the Expression Builder. . .

Is SUM the name of an actual field in your Query? Because if so, that's most likely your problem: SUM is a reserved word in both Access and SQL.

Try changing the SUM field to something else (even just adding another "M" to the end) and see if that fixes your problem.

[jasonbarnes]

No, the name of the Field is SUMofTEUS. I'll try doing it without the expression builder later today. Thanks for your help so far.

[Rawb]

I just did a little looking around and it appears that you can't reference a Query that isn't bound to your Form in the same say you can reference other Forms or Reports.

Even though I don't like using the function, try using DLookup() in the Control Source and see if that works for what you want:

=DLookup("SUMofTEUS","MyQuery")

If you change MyQuery to the name of your Query, it should work. . .

[jasonbarnes]

Still couldn't get the DLookup to work. I did however get it to work by binding the query to the form.

Help me with this one. If I do my sum as well as other various calculations. Such as which percentage of this column is not null etc. multiple times across different columns. Couldn't I disregard the QrySUM and just create variables in VBA for all the various indicators I was trying to capture. Then print them in textboxes. Or is it possible to bind multiple tables or queries to the same form in VBA without conflicts in naming.

[jasonbarnes]

I left out the imporant detail of only binding on table I'm taking my indicators from to this form.

[Rawb]

You can't bind more than one Database Object (a Table or a Query) to a single Form.

If you want all that data to appear in a single Form, you can do it though. It just takes a bit more work (and knowledge).

There are several ways to do what you're asking, but the way I'd use would be to make an unbound Form with empty Textboxes for the relevant data. Then I'd set up a VBA "On Load" event for the Form that filled in the data.

[jasonbarnes]

I'll guess I'll have to break down this weekend and see if they have a good book for VBA/Access development at Barnes and Noble. My Sum works.

But when I try the following neither one works.
control source =count([column] is not null)/count([column]autonumber)


Basically it would measure the amount of one column that is not null based on the total number in the column. We have to large amounts of data cleansing so we try to measure and put a percentage on certain columns as the data goes through the process.

Hopefully I can get further down the line this weekend. Thanks again.