Problem with a simple form equation

[hardrock]

Hi All,

In my form, I have a simple equation, but i can't get the output 'y' to work.
The TransType will either return + or - in a loop process and values x and PlannedQTY are read from Textboxes on my form.

The equation i have so far is
y = "" & x & "" & TransType & "" & PlannedQTY ""

but this just outputs something like y = 10+1 I want y to output 11.

Any ideas?

Thanks

 

[pbaldy]

Try

y = x + IIf(TransType = "+", PlannedQTY, -PlannedQTY)

The Eval() function might also work.

 

[hardrock]

Thankyou Pbaldy. That worked a treat!

I still have one more problem. I am looping trough my recordset line by line. I have a recordset column called 'inventory' or (rs!inventory). I want to be able to compare the current inventory value with the count from the previous record. If they don't match, I'd like to make a variable X =0 or if they match X = 1. I tried to figure it out but head is hurting now... Thanks

 

[pbaldy]

Not sure I understand, so it might help to see the code. Offhand it sounds like you need a variable to keep track of the previous record's value. Set it at some point in your loop, then when on the next record you can test:

If rs!inventory = VariableName Then

 

[hardrock]

Hello Paul, so the bit of code that does the magic is as below. Now, thanks to your earlier advice this bit works fine now to calculate planned inventory for 1 item number.

As can be seen, it's a simple ExpectedQTY decrement or increment counter.. if the TransType is + , the value of 'y' will go up by the plannedQTY or go down if TransType is -

From the code, it does a first pass in order to get the PlannedInv value (when W =1), in otherwords, the initial stock count for that item is read here.


As indicated, it works fine for 1 item number, but my table may contain many item numbers, so i need a means to detect either when the value of rs!item or rs!plannedInv changes in the loop, as this will then reset the value of W to 1, to allow a first pass to be initiated once again for the detected change. What is the best way of doing this?


**********************************
W= 1
Set rs = qd.OpenRecordset()
While Not rs.EOF And Not rs.BOF
'*************************************************
Item = rs!Item
OrderNumber = rs!OrderNumber
TransType = rs!TransType
PlannedQTY = rs!PlannedQTY
PlannedInv = rs!Count

If W = 1 Then
y = PlannedInv + IIf(TransType = "+", PlannedQTY, -PlannedQTY)
W = 0
Else

z = IIf(TransType = "+", PlannedQTY, -PlannedQTY)
y = y + z

End If

rs.Edit
rs!ExpectedQTY = y
rs.Update


rs.MoveNext

Wend

 

[pbaldy]

Similar to what I mentioned. You set a variable to the first item before the loop starts. Within the loop, test that variable against the current record. If the same, move on, if different reset your variables, including the item variable.