Re: Using database fields in a calculation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

1 way:

iOnhand = CLng("0" & rs_stock.Fields.Item("Qtyonhand").Value)
iSold = CLng("0" & rs_stock.Fields.Item("Qtycommitsales").Value)

iBalance = iOnHand - iSold

The important thing is that you CLng your values so that the script knows
it's dealing with numeric values. Also, the whole "0" & nonsense is to
handle any case where your query is returning nulls (which it hopefully
isn't).



You could also return this in your query

SELECT Qtyonhand, Qtycommitsales, Qtyonhand-Qtycommitsales AS Qtybalance
FROM...

Ray at work




"Shoraz Hosein" <shoraz@xxxxxxxxx> wrote in message
news:%23$m$%23AjUFHA.3344@xxxxxxxxxxxxxxxxxxxxxxx
>
> I need to use some database fields for calculations but I an not certain
> about the syntax. For example I want the Onhand value to subtract the
> Qty Committed to sales.
> In a seperate cell in a table I want the balance to show up.
> The fields are
> (1)<%=(rs_stock.Fields.Item("Qtyonhand").Value)%>
> (2)<%=(rs_stock.Fields.Item("Qtycommitsales").Value)%>
>
> All I want to do is subtract the (2) from (1)
> Thanks.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***


.

Relevant Pages

  • Re: New comer to reports
    ... I want the query &/or the report to pull the "Op Bal" from the products table & then add or subtract what I ... Then the next entry is a different product ID. ... Two entries later we send out 3 units of 250 pcs. ...
    (microsoft.public.access.reports)
  • Re: Percent on sum total
    ... Thank you since your first suggestion in placing the expression in query ... create the expression if we need to subtract 6 percent from the country china ... Could this be a field in query not allowing for the calculation ... Add to your report to the right of your InvAmt ...
    (microsoft.public.access.reports)
  • Re: what is wrong with this statement
    ... The dash sign means "subtract the value on the right from the value on ... UPDATE is a Reserved word, and Reserved words should not be used to ... characters and the underscore character), or one may put brackets around the ... 2-I have not been able to figure out how I can do thsi query by typing ...
    (comp.databases.ms-access)
  • Re: Query Word 2003
    ... Just in case there is any misunderstanding, the code I showed isn't VBA code - it's the code in a "Word field." ... As far as DATABASE fields are concerned, ... I select Query Option s, Filter Records, Sort Records and Select Fields, when I remove the fields from the select records option, and leave only the field I wand in my report, when I complete the report all the fields are showing up in the report. ...
    (microsoft.public.word.docmanagement)
  • RE: How do I subtract records in one table from another table
    ... not valid anymore? ... In this case you could use a Delete Query. ... I can't find that feature in Access. ... > can I subtract the records in table A from table B? ...
    (microsoft.public.access.gettingstarted)