how can I change all currency fields from positive to negative

[Johnie Karr]

I am creating an Invoice Application for a company. They need to select
Invoice or Credit Memo. If Credit Memo is selected it should change all
values to negative, if Invoice is selected it changes all values to positive.

I have made it change all exisiting data by using:

Me!Line1Item = -Me!Line1Item

If the value is positive this will change it to negative, and if the value
is negative it will change it to positive.

2 Issues with this:
If the user manually changes that field, while Credit Memo is selected, it
will make it positive. Also, any non existant fields (ie. $0.00) are
neutral, so once edited will make them positive. Then you change Credit Memo
to Invoice and it changes the positves to negative.

There has to be a way to make the field negative, not the value of the
field. I don't know VBA, but I am using it for this program. PHP and MySQL
is where I normally develop.

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

 

[Alex Dybenko]

Hi,
you can do this 2 ways:
- you can store always positive values, and then use query to change Credit
Memo to negative - IIF([CreditMemo], -[Line1Item], [Line1Item]) as
NewLine1Item. This query you can use to calculations, etc
- you can use form's BeforeUpdate event to change Line1Item:
if [CreditMemo] then
me.Line1Item= - ABS(me.[Line1Item])
else
me.Line1Item=ABS(me.[Line1Item])
end if

assuming that CreditMemo is some Boolean flag

 

[Allen Browne]

Johnie, you have 2 fields:
- one is the dollar value;
- the other (a transaction type) indicates whether it is an Invoice or a
Credit.

How about setting up the TransactionType field as a Number field, using the
value:
1 for Invoice;
-1 for Credit Memo
(or the reverse if you prefer.)
Mark it as a Required field, and set the Validation Rule to:
1 or -1
so other values are not possible.

Then in a query, you can enter this into a fresh column in the Field row:
[TransactionType] * [Amount]
The multiplier causes one type to be positive, and the other type to be
negative.

You can use an combo box for the TransactionType, with properties:
Row Source Type Value List
Row Source 1; "Invoice"; -1; "Credit Memo"
Column Count 2
Column Widths 0
To the user, they are just selecting the type, but the multiplier does what
you need.

 

[Ron]

I have created a similar application and used the 2 field approach
(Transaction type & Amount ) that the others are recommending. MAKE SURE
you validity check the user input that they amount they are entering is
always positive. You will have users that try to enter credit memos as
negative invoices and could cause you a lot of heartache down the road....
trust me, i know.

best of luck,
Ron

 

[Johnie Karr]

Thank you everyone for your replies and help.

Forgive my ignorance, but do I need two fields for every currency field?

Right now I have the 16 or so currency fields, and one drop box with Invoice
and Credit Memo choices.

I guess I just don't fully understand what you mean with the two fields.

I will try playing around with the 'before update' that Alex refered me to.

Thanks again, and I have a seperate question.....When I try to type my
response in the reply window, certain letters require me to use my shift key
in order to type them. It's almost as if sticky keys is turned on for just
that window. If I type certain letters, it brings up the set up voice
recogniztion feature. I had to type this in notepad and copy and paste it
over.

I've never seen a problem like this before, has any of you?

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

 

[Johnie Karr]

Just a side question...btw, the letters work correctly now for some
reason...anyway, what is ABS that Alex is pointing me to? I'm assuming that
we are telling the field something for after update?

Hi,
you can do this 2 ways:
- you can store always positive values, and then use query to change Credit
Memo to negative - IIF([CreditMemo], -[Line1Item], [Line1Item]) as
NewLine1Item. This query you can use to calculations, etc
- you can use form's BeforeUpdate event to change Line1Item:
if [CreditMemo] then
me.Line1Item= - ABS(me.[Line1Item])
else
me.Line1Item=ABS(me.[Line1Item])
end if

assuming that CreditMemo is some Boolean flag

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I am creating an Invoice Application for a company. They need to select
Invoice or Credit Memo. If Credit Memo is selected it should change all
values to negative, if Invoice is selected it changes all values to
positive.

I have made it change all exisiting data by using:

Me!Line1Item = -Me!Line1Item

If the value is positive this will change it to negative, and if the value
is negative it will change it to positive.

2 Issues with this:
If the user manually changes that field, while Credit Memo is selected, it
will make it positive. Also, any non existant fields (ie. $0.00) are
neutral, so once edited will make them positive. Then you change Credit
Memo
to Invoice and it changes the positves to negative.

There has to be a way to make the field negative, not the value of the
field. I don't know VBA, but I am using it for this program. PHP and
MySQL
is where I normally develop.

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

 

[Brian Bastl]

ABS = Absolute Value of a number
ABS(-1) = 1
ABS(-2) = 2
ABS(2) = 2

Brian

Just a side question...btw, the letters work correctly now for some
reason...anyway, what is ABS that Alex is pointing me to? I'm assuming that
we are telling the field something for after update?

Hi,
you can do this 2 ways:
- you can store always positive values, and then use query to change Credit
Memo to negative - IIF([CreditMemo], -[Line1Item], [Line1Item]) as
NewLine1Item. This query you can use to calculations, etc
- you can use form's BeforeUpdate event to change Line1Item:
if [CreditMemo] then
me.Line1Item= - ABS(me.[Line1Item])
else
me.Line1Item=ABS(me.[Line1Item])
end if

assuming that CreditMemo is some Boolean flag

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I am creating an Invoice Application for a company. They need to select
Invoice or Credit Memo. If Credit Memo is selected it should change all
values to negative, if Invoice is selected it changes all values to
positive.

I have made it change all exisiting data by using:

Me!Line1Item = -Me!Line1Item

If the value is positive this will change it to negative, and if the value
is negative it will change it to positive.

2 Issues with this:
If the user manually changes that field, while Credit Memo is selected, it
will make it positive. Also, any non existant fields (ie. $0.00) are
neutral, so once edited will make them positive. Then you change Credit
Memo
to Invoice and it changes the positves to negative.

There has to be a way to make the field negative, not the value of the
field. I don't know VBA, but I am using it for this program. PHP and
MySQL
is where I normally develop.

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

 

[Johnie Karr]

Private Sub CertInput_BeforeUpdate(Cancel As Integer)
If CRMEM = "Credit Memo" Then
Me!CertInput = -Abs(Me.[CertInput])
Else
Me!CertInput = Abs(Me.[CertInput])
End If
End Sub

This is what I have, and everytime I run the form, once I type in a value it
says there is an error in the BeforeUpdate and cannot save the data. Then I
click Debug and it highlights this line:
Me!CertInput = Abs(Me.[CertInput])
If I have Credit Memo selected then it updates the line for that.

What is wrong with that line? CertInput is a Currency Field.

Thank you everyone for your replies and help.

Forgive my ignorance, but do I need two fields for every currency field?

Right now I have the 16 or so currency fields, and one drop box with Invoice
and Credit Memo choices.

I guess I just don't fully understand what you mean with the two fields.

I will try playing around with the 'before update' that Alex refered me to.

Thanks again, and I have a seperate question.....When I try to type my
response in the reply window, certain letters require me to use my shift key
in order to type them. It's almost as if sticky keys is turned on for just
that window. If I type certain letters, it brings up the set up voice
recogniztion feature. I had to type this in notepad and copy and paste it
over.

I've never seen a problem like this before, has any of you?

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

I am creating an Invoice Application for a company. They need to select
Invoice or Credit Memo. If Credit Memo is selected it should change all
values to negative, if Invoice is selected it changes all values to positive.

I have made it change all exisiting data by using:

Me!Line1Item = -Me!Line1Item

If the value is positive this will change it to negative, and if the value
is negative it will change it to positive.

2 Issues with this:
If the user manually changes that field, while Credit Memo is selected, it
will make it positive. Also, any non existant fields (ie. $0.00) are
neutral, so once edited will make them positive. Then you change Credit Memo
to Invoice and it changes the positves to negative.

There has to be a way to make the field negative, not the value of the
field. I don't know VBA, but I am using it for this program. PHP and MySQL
is where I normally develop.

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

 

[Johnie Karr]

Runtime error '2115' is the error I get.

Private Sub CertInput_BeforeUpdate(Cancel As Integer)
If CRMEM = "Credit Memo" Then
Me!CertInput = -Abs(Me.[CertInput])
Else
Me!CertInput = Abs(Me.[CertInput])
End If
End Sub

This is what I have, and everytime I run the form, once I type in a value it
says there is an error in the BeforeUpdate and cannot save the data. Then I
click Debug and it highlights this line:
Me!CertInput = Abs(Me.[CertInput])
If I have Credit Memo selected then it updates the line for that.

What is wrong with that line? CertInput is a Currency Field.

Thank you everyone for your replies and help.

Forgive my ignorance, but do I need two fields for every currency field?

Right now I have the 16 or so currency fields, and one drop box with Invoice
and Credit Memo choices.

I guess I just don't fully understand what you mean with the two fields.

I will try playing around with the 'before update' that Alex refered me to.

Thanks again, and I have a seperate question.....When I try to type my
response in the reply window, certain letters require me to use my shift key
in order to type them. It's almost as if sticky keys is turned on for just
that window. If I type certain letters, it brings up the set up voice
recogniztion feature. I had to type this in notepad and copy and paste it
over.

I've never seen a problem like this before, has any of you?

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

I am creating an Invoice Application for a company. They need to select
Invoice or Credit Memo. If Credit Memo is selected it should change all
values to negative, if Invoice is selected it changes all values to positive.

I have made it change all exisiting data by using:

Me!Line1Item = -Me!Line1Item

If the value is positive this will change it to negative, and if the value
is negative it will change it to positive.

2 Issues with this:
If the user manually changes that field, while Credit Memo is selected, it
will make it positive. Also, any non existant fields (ie. $0.00) are
neutral, so once edited will make them positive. Then you change Credit Memo
to Invoice and it changes the positves to negative.

There has to be a way to make the field negative, not the value of the
field. I don't know VBA, but I am using it for this program. PHP and MySQL
is where I normally develop.

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

 

[Johnie Karr]

The problem is it needs to be in after update, not before update.

Thanks everyone for your help, I'll mark this answered now!

Johnie Karr
Data Management Technologies
www.datamt.org

Private Sub CertInput_BeforeUpdate(Cancel As Integer)
If CRMEM = "Credit Memo" Then
Me!CertInput = -Abs(Me.[CertInput])
Else
Me!CertInput = Abs(Me.[CertInput])
End If
End Sub

This is what I have, and everytime I run the form, once I type in a value it
says there is an error in the BeforeUpdate and cannot save the data. Then I
click Debug and it highlights this line:
Me!CertInput = Abs(Me.[CertInput])
If I have Credit Memo selected then it updates the line for that.

What is wrong with that line? CertInput is a Currency Field.

Thank you everyone for your replies and help.

Forgive my ignorance, but do I need two fields for every currency field?

Right now I have the 16 or so currency fields, and one drop box with Invoice
and Credit Memo choices.

I guess I just don't fully understand what you mean with the two fields.

I will try playing around with the 'before update' that Alex refered me to.

Thanks again, and I have a seperate question.....When I try to type my
response in the reply window, certain letters require me to use my shift key
in order to type them. It's almost as if sticky keys is turned on for just
that window. If I type certain letters, it brings up the set up voice
recogniztion feature. I had to type this in notepad and copy and paste it
over.

I've never seen a problem like this before, has any of you?

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org

I am creating an Invoice Application for a company. They need to select
Invoice or Credit Memo. If Credit Memo is selected it should change all
values to negative, if Invoice is selected it changes all values to positive.

I have made it change all exisiting data by using:

Me!Line1Item = -Me!Line1Item

If the value is positive this will change it to negative, and if the value
is negative it will change it to positive.

2 Issues with this:
If the user manually changes that field, while Credit Memo is selected, it
will make it positive. Also, any non existant fields (ie. $0.00) are
neutral, so once edited will make them positive. Then you change Credit Memo
to Invoice and it changes the positves to negative.

There has to be a way to make the field negative, not the value of the
field. I don't know VBA, but I am using it for this program. PHP and MySQL
is where I normally develop.

Thanks,
Johnie Karr
Data Management Technologies
www.datamt.org