Comparing values in a query

[kstarnes]

Hi...
I will try to keep this succinct.

I have a query that totals a column in a sub-form. The query also gets a sum from the main form for the same record. Is it possible to have a message/colour change/warning if the total of the column in the sub-form exceeds the other number in the query? I would need this comparison to take place within this query, rather than creating a new one.

Thanks!
Kathryn

 

[ajetrumpet]

I have a query that totals a column in a sub-form. The query also gets a sum from the main form for the same record. Is it possible to have a message/colour change/warning if the total of the column in the sub-form exceeds the other number in the query?

I don't think the color palette is available for queries, or even datasheets, but I may be wrong. "The best way to do it would be to probably use a conditional statement involving the color when you populate the relevant controls on the form.

 

[boblarson]

Kathryn:

Adam is correct in saying that you would need to use a form for this and utilize Conditional Formatting in order to do that.

 

[boblarson]

Also, you really shouldn't let users work in queries or tables directly. You should always use forms to do this as you have more control over things whereas in a query or table, the users can do mostly anything they want and you are powerless to stop them.

 

[ajetrumpet]

Also, you really shouldn't let users work in queries or tables directly.

Gosh, I've never that before!!!

the users can do mostly anything they want and you are powerless to stop them.

That sounds like she's being threatened by Neo's archrival, Agent Smith, and the million copies of his own virus!

 

[boblarson]

Gosh, I've never that before!!! That sounds like she's being threatened by Neo's archrival, Agent Smith, and the million copies of his own virus!

Adam, do I have to take away your crayons?

 

[ajetrumpet]

No, please don't. I'm just drawing a picture of you going mad by repeating yourself a million times. It's really quite amusing.

 

[kstarnes]

Thank you! In this case, I don't think it is worth the cost to implement it correctly-the information is right there in the report already-two numbers right next to each other so If they can't compare them and see which is bigger themselves I'm pretty sure nothing is going to help them-I was just trying to make it as obvious as possible.

 

[kstarnes]

P.S. don't worry I don't have any users messing about in my tables or queries-it took me long enough to figure out how to build them-don't want anyone knocking over my pretty towers for the fun of it!