Making a Field Negative in a Query

[michaelak117]

Currently in order to change my field from a positive number to a negative number I have to change the name of the field and multiply the old name by -1.

There has to be a quicker, easier, more efficient way to do this, Anyone?

Thanks

Mike

 

[boblarson]

Well, you could just change the values in the table (but I'm sure that isn't an option).

So, in reality you are basically on to the only way you can do it as you can't use the same name in a query for a calculated field for one that already exists. However, you can take that query (provided you don't leave the original field name) and create another query based on it and assign the original name back to the calculated field.

 

[michaelak117]

What way could I make the table field formated as negative numbers so that everday they come in and are negative?

 

[boblarson]

What way could I make the table field formated as negative numbers so that everday they come in and are negative?

You need to look at my post in your other thread. Your design may be flawed and need fixing.

 

[michaelak117]

I asked a question about more automation and making numbers negative and you interpret that it may be flawed, I may not be an expert but I dont understand that. Its actually pretty reliable and quick and easy functioning I just want to improve speed and automation. I am def open to any suggestions if I am expressing myself clearly enough for you to see what kind of database im building. I am just using queries to manipulate data and run daily reports

 

[Brianwarnock]

Don't get uptight, sometimes the questions asked can alert experienced people like Bob to potential or actual design flaws, rmember he is trying to help you.

Brian

 

[michaelak117]

I realize that, I dont feel I was being uptight, just wanted him to know I was confused. I was open to any and all help, we were just getting onthe same page. He was helpful w/ me and we were making references to another thread you may not of seen. Its perceptions, dont forget your reading text and not hearing voice/tone.

Mike

 

[DCrake]

Is the control on your form bound to the field that contains the value you to show as being negative?

Has the user edit rights on this field or is it a calculated field?

Do you want to store the number as a negative in your table?

Will the value always be a negative value?

An alternaive to *-1 is me.field = 0 - me.field

Timesing by -1 will turn a pos into a neg and a neg into a pos

David

 

[gemma-the-husky]

you have several solutions

1. store the data as negative intially

2. store it as postive, but change the sign by multiplying it by -1 in a query

3. don't change the sign in the query, but change the sign when the value is used later.

-----------
none of these are particularly inefficient

the issue with 2, is that the value will become non-updateble, which may not be an issue.

but ordinarily i would say store the data correctly - eg in a stock/inventory situation I would store sales as negative quantities, so you can simply add transaction quantities to give a true movement, without having to consider the sign

it depends what your figures ACTUALLY represent, as to whether this is appropriate for you.

with debtors/creditors/ - payables/receivables its more tricky

in accounting terms these are opposites - on their own terms they are all positives - so at some point you do have to change the sign of one of them, to bring them together

 

[michaelak117]

At the moment I am just creating a new field name and multiplying by -1 in the query.

Yes the numbers will all always be negative. I think the best thing for me to do is just make them negative in the table and as data is appended daily it will also become negative. This would just cut out a step for me. Where would I set this formating?

 

[gemma-the-husky]

wherever you import, or enter the values

if you enter them as plus, immediately do myfield = -myfield in the after update event for the field - although this may look strange, if numbers suddenly change

the other way is to have two fields - a positive value entry field, and a hidden negative field - control source would be (you then probably need a plus and minus value field in your table)

mynegativefield = - mypositivefield

 

[michaelak117]

That makes sense, yet I am unsure about the update event as I import this data into a table. Can I change to negative at that point, or are you referring to once its in the query. Where is this after update event, i am only familiar with this in a form?

 

[The_Doc_Man]

OK, I'm sensing something. And if I'm wrong, don't take it as being critical of your efforts.

First principle: If you can always recalculate it, never store it. (What, never? No, never. What never? ... Well, hardly ever.)

Second principle: Store data as you get it if there is ever an accountability issue in showing what you got. Don't store data in the form you need it if that differs from what you received. (See first principle.)

Third principle: Anything you can do with a table can be done just as well - and often better - in a query. Which is why the second principle works.

Therefore, if you are basing a report on a table, base it on a query instead, and remember that the name on the report is just a label-box. You can make it say anything. The name of the underlying field is never seen by the folks who read the report. So if you need to base a report or other action on the negative of a field, make it happen in a query.

I won't say that you should NEVER base a report on a table. NEVER is a rare word. (See first principle above.) We have found in general practice, however, that things work better through queries, particularly if you need to show something positive sometimes and negative sometimes. Use different base queries for the two cases and your problem is solved. Queries, coupled with judicious use of computational formulas and the FORMAT function, can do a LOT to make your life easier in reports, forms, and general data processing operations.