subtracting query values

[emshim]

How do i subtract one value, worked out using a query, from another value, worked out using a query, and display it in a text box?

 

[Alc]

You could use a third query to subtract the value of the first query from the second.
Make the third query the source for the textbox.

 

[emshim]

You could use a third query to subtract the value of the first query from the second.
Make the third query the source for the textbox.

how do i subtract using a query though? I dont really know access that well, but am trying to work it out! could you explain what i will need to write? I have totalquantityallocated and totalquantity and want to subtract the 1st from the 2nd.

Thanks for the help.

Em

 

[ajetrumpet]

how do i subtract using a query though? I have totalquantityallocated and totalquantity and want to subtract the 1st from the 2nd.

If these values have been calculated in your query, you need to refer to that query in your expression for the text box. If your form's recordsource is the query itself though, no need to reference the query, just the fields where the values are stored (If your final value has already been calculated in the query though, just provide the field as the control source for your text box).

Use the expression builder, it's easy to build statements with it...

 

[emshim]

thanks, i think i cracked most of it. I now press a button and the value is displayed in a field in the query, just gotta work out how 2 get that into the tet box, as at the moment i am just getting "#Name?". In my text box control source i have clicked on the expression builder and selected the name of the query and the field name. Is there anything else i need to type in to get the correct value? or could it be something to do with the data type?

em

 

[emshim]

ah, i need to beable to refresh the text box once the button has been clicked. Do you know the code for this? I know how to do it if it was a requery but not just to refresh the text box to find the information from the field in the query.

Thanks!

Em

 

[emshim]

its ok i worked it out! i cant believe i can be real dumb sometimes. I ended up changing my text box to a list box instead, so i could use:

Me.lstFree.Requery

in my code, where lstFree is the list box. Just thought id explain incase any1 else was as stupid as me!!! hah

thanks for the help

em

 

[ajetrumpet]

as at the moment i am just getting "#Name?". In my text box control source i have clicked on the expression builder and selected the name of the query and the field name.

That error tells you its invalid. What is the form's recordsource?? What's the expression?? [query]![txtbox]??

Might be more behind it too depending on your query SQL statement....

If you can't get it, maybe you could post the actual statement that feeds the fields involved....