Minusing a number?

[SteveC24]

Hello all,

I have a stock control database, which (very simplified), has a table in, called "Modifiers".

I thought it would be nice to keep both additions and losses of stock all in one table - and show it on a form under two different tabs.

What I would really like to do is have the user enter numbers in the form, but for the numbers in the "Losses" section, for them to be "minused", so that a Sum() will work out the correct total.

SO, with 5 additions, and 2 losses (entered just like that, no minus numbers), I want the Sum() to return 3, NOT 7.

Hope that makes sense! Or would it just be easier to bung additions and losses in two seperate tables?

Thanks very much indeed!

 

[Sergeant]

In your Losses text box, in the afterUpdate event, you could do:
Me.txtLosses = Me.txtLosses * -1

 

[ghudson]

I would prefer to use two tables since I would relate this to an inventory type of db.

 

[Pat Hartman]

I would recommend a single table as you have it. I have created dozens of applications that manage transactions both financial and inventory and the concept is the same. You have transaction types that define the purpose of the transaction. I the case where the user doesn't want to enter minus signs, your transaction needs to identify the normal sign of the transaction. I do this by using 1 for positive and -1 for negative. That way I don't have to use any IIf() statements, I can just multiply the value of the transaction by the SignIndicator to get a plus or minus result:

Select YourAmt * YourSign As ProperSign, .... From YourTable;

If the user doesn't mind storing the sign but just wants to enter it, do the calculation in the AfterUpdate event of the amount field.

 

[SteveC24]

Hello,

thank you for your suggestions.

Although I would prefer to keep it all in the one table, I think maybe ghudson has the more sensible idea.

Pat, I would like to do what you suggest, but don't really want the user to see any - numbers. So, they should add it like "5", then when they recall it later, it still say "5", but really be storing "-5".

Hope that makes sense!

 

[Sergeant]

I like Pat's idea. (which will do exactly what you want, but with one table)
You can store the number as an absolute (positive) and add a field for the sign...in which you would store 1 or -1
The user would not have to enter it, it could be derived from context, i.e...if it's recorded on the losses form...the -1 would be the default value...and no one has to even see it.

 

[SteveC24]

OK - I think I have done it - all in one table too!

I figured that actually, I don't need to minus the number. When I want the total, which I use on the main form (with the additions and losses in subforms), I use the following code:

=Nz(DSum("[Quantity]","tblFishModifiers","[FishID]=" & "[FishID] and " & "[Modifier]=" & """+""")-DSum("[Quantity]","tblFishModifiers","[FishID]=" & "[FishID] and " & "[Modifier]=" & """-"""),0)

This may be over-complicating matters - but it seems to work - and at present, I don't think I need to put the total anywhere else, and even if I do - this method seems to work quite well.

Means I don't have to faff about with minusing numbers all over the place!

Thank you very much for your help - AGAIN!

 

[SteveC24]

Slight change of result - I have indeed done it, but I think my code in the "TOTAL" box may well be wrong...and I can't see why.

The following:

=Nz(DSum("[Quantity]","tblFishModifiers","[FishID]=" & "[FishID] And " & "[Modifier]=" & """+""")-DSum("[Quantity]","tblFishModifiers","[FishID]=" & "[FishID] And " & "[Modifier]=" & """-"""),0)

Works great, but it is not taking into account which "FishID" you are on. THe rest of it works great, but it is working it out for all fish, not just the current one.

There is a field on the record called "FishID", which it should be using, but it is not bothering to look at that.

What's up?

 

[ghudson]

Try the code in the OnCurrent event to populate the text box [not in the actual text box].

 

[SteveC24]

ghudson,

Have just tried that, and it hasn't helped, although the calculation is performed much quicker, which is good!

Any other ideas?

 

[Pat Hartman]

This may work better. I moved the [FishID] outside the quotes so that it would be evaluated based on the form's current record.

=Nz(DSum("[Quantity]","tblFishModifiers","[FishID]=" & [FishID] & " and " & "[Modifier]=" & """+""")-DSum("[Quantity]","tblFishModifiers","[FishID]=" & [FishID] & " and " & "[Modifier]=" & """-"""),0)

But I'm not sure why you think that is simpler than:
=DSum("Nz([Quantity]) * [Modifier]", "tblFishModifiers", "[FishID] = " & [FishID])

PS - when using the Nz() function, be sure to enclose the correct part of the expression.

 

[SteveC24]

Thanks Pat Hartman - that worked a treat.

I think I see what you are doing with the second (MUCH shorter!) expression, but it would not work on my database - I am probably going about it in some strange fashion!

The value of "Modifier" will only ever be either "+" or "-" indicating if that record is an addition or a subtraction. The "Quantity" field is then used to determine the amount of addition or subtraction.

Hope that makes sense! And thank you again, both of you!

 

[Pat Hartman]

I feel like I am speaking a foreign language. You are using "+" and "-" as the values for Modifier and I am suggesting a numeric field with a value of 1 and -1 instead.

 

[SteveC24]

Ah, I see!

I apologise for not understanding you - but there wasn't really any need to be rude.

I shall modify my code to do what you have suggested, and thank you whole-heartedly for your assistance.

Can't help being thick I am afraid...I was born that way!

 

[RuralGuy]

In Pat's defense, I see or feel no rude there. I believe you are being just a little bit too touchy.

 

[SteveC24]

Yeah, probably - it is all this working from home...obviously not good for me!

Don't mean to cause any offence or upset - just ignore me...everyone else does!


Thank you again for your help - it is very very VERY much appreciated!