query function with #error

JaredNJames

Registered User.
Local time
Today, 23:11
Joined
Jul 7, 2007
Messages
87
Hi, i know ive asked this in another forum, but i just read a thread on a query function which may be able to deal with this so thought id post it here:

the function i just saw:- Expr1: IIf([Sold],="","0",[Sold])
this does nothing to help my problem though so any ideas?

i have three fields; [boughtin], [sold] and [instock]. [boughtin] comes from one query and [sold] from another.

[instock] = [boughtin] - [sold]

because [sold] only has a value once an invoice has been created for that item, it means that if you try to view an item that hasnt been sold yet, you see how many have been bought in, but no sold value and then because of this, no stock value. (you just get an '#error' value in the stock box.

basically what i need is this:
if both [boughtin] and [sold] are present, then the calculation is done and a [stock] value is shown.
if only [boughtit] is present then the stock value simply shows the [boughtin] value.

[boughtin] is located on one subform (frm_stocksub) and [sold] is one another (frm_stocksubsub).

i need a code to put into the after update event of the main combo on the main form so that it detects this and then displays the approriate value once both queries have run.

a simple idea of what i want is:
If [Sold] has no value Then
[InStock] = [BoughtIn]
Else
[InStock]=[BoughtIn]-[Sold]
End If

Obviously there is far more detail in terms of locating the particular text boxes, but i hope it gives you an idea of what i want.

Thanks
Jared James
 
You simply need to use the Nz() function for the Null values.
[InStock]=[BoughtIn]-Nz([Sold],0)
 
that code doesnt work, when the query doesnt have a value, the subform fields dont show and so the calculation box shows '#error'.
 

Users who are viewing this thread

Back
Top Bottom