Coding for a single Debits/Credits Field

[Robert T]

Hello:

I'm relatively new to Access and would appreciate help with the following
design issue. I have two different applications that were created in Alpha
Five. One is a Checkbook app and the other one is a PayBills application and
now I'm converting them to Access 2003 databases.

Both applications have separate fields for DEBITS and CREDITS. Several
people in the Access Newsgroups suggested that I convert the above to a
design with ONE field for both, with CREDITS being negative and DEBITS being
positive numbers.

I want ensure that DEBITS are automatically entered as positive numbers and
CREDITS are automatically entered as negative numbers. You cannot depend upon
the data entry person to do such manually.

I have a text field where the only two entries allowed is 'Debit" or
"Credit". I can use that as the reference field but that's where I need help
in making sure the numbers are automatically formatted properly.

Thanks,
RT

 

[Jeff Boyce]

Robert

Can you count on your data entry folks to correctly identify whether an
amount is a DB or a CR? If so, another approach might be to use a single
field to hold the amount, and a option group to select DB or CR.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Robert

I left out ... and YOU can do the correct assigning of positive/negative,
based on which type of transaction.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brian Bastl]

Robert,

Use the after update of the numeric textbox control. I'm envisioning
somthing like:

Private Sub MyAmount_AfterUpdate()
If Not IsNull(Me.MyDebitCreditControl) Or
Len(Nz(Me.MyDebitCreditControl, 0) > 0 Then
If Me.MyDebitCreditControl = "Debit" Then
Me.MyAmount = Abs(Me.MyAmount)
Else
Me.MyAmount = Abs(Me.MyAmount) * -1
End If
End If
End Sub

HTH,
Brian

 

[Robert T]

Hello Jeff:

I guess I didn't explain my throught process clearly the first time,
however, that's exactly the idea.

If the field Trans_Type is a "Debit", then the amount field will
automatically be a positive number and conversely, if Trans_Type is a
"Credit", then it should automatically be a negative number.

The only decision the user should make is whether it's a "Debit" or
"Credit". Then they should enter the amount and Access will decide if it's a
positive or a negative number.

 

[Robert T]

Hello Brian:

I think you've come very close to what is needed.

[1] The text box "Debit" or "Credit" is a required field and those are the
only two choices allowed so I would hope the field can't be skipped and it
must be either a "Debit" or a "Credit". Would that eliminate the need for
these two lines:

If Not IsNull(Me.MyDebitCreditControl) Or
Len(Nz(Me.MyDebitCreditControl, 0) > 0 Then

The rest of the script looks good, however, would you be kind enough to
explain the ABS() function? I looked it up in help but it still isn't clear
to me.

Thanks,
RT

 

[Brian Bastl]

Robert,

[1] The text box "Debit" or "Credit" is a required field and those are the
only two choices allowed so I would hope the field can't be skipped and it
must be either a "Debit" or a "Credit". Would that eliminate the need for
these two lines:

If Not IsNull(Me.MyDebitCreditControl) Or
Len(Nz(Me.MyDebitCreditControl, 0) > 0 Then

Were you to change the textbox to a combobox, that would be even better:
1) you wouldn't have to worry about mis-spelled words
2) you could eliminate Len(Nz(Me.MyDebitCreditControl, 0) > 0 which checks
for a zero length string.

You still want the Not IsNull portion of it, and you will want to add some
code in the Before update event of the Amount control to cancel the update
and undo the amount entered if your DebitCredit control has been passed
over, or... you could use the OnEnter procedure for the Amount control
instead to send focus back to the DebitCredit control if nothing had been
selected..

Private Sub MyAmount_AfterUpdate()
If Not IsNull(Me.MyDebitCreditControl) Then
If Me.MyDebitCreditControl = "Debit" Then
Me.MyAmount = Abs(Me.MyAmount)
Else
Me.MyAmount = Abs(Me.MyAmount) * -1
End If
End If
End Sub

Private Sub MyAmount_BeforeUpdate(Cancel As Integer)
If IsNull(Me.MyDebitCreditControl) Then
Cancel = True
Me.Undo
End If
End Sub

or

Private Sub MyAmount_Enter()
If IsNull(Me.MyDebitCreditControl) Then
Me.MyDebitCreditControl.SetFocus
End If
End Sub

The rest of the script looks good, however, would you be kind enough to
explain the ABS() function? I looked it up in help but it still isn't clear
to me.

Abs() is the absolute value function.
Abs(3) = 3
Abs(-3) = 3
Abs(-3) * -1 = -3

HTH,
Brian

 

[Robert T]

Hi Brian:

That's great stuff, exactly what I was looking for. The only change I would
make is to probably throw in a MSG box if I decide to go with the OnEnter box
advising the user they must enter a "Debit" or Credit".

Once again, could you please explain the purpose of the ABS() function?

Thanks so much, I truly appreciate your excellent input. If I have any
questions after I try your suggestions, I'll post them here.

RT

 

[Brian Bastl]

Robert,

the Abs() function converts whatever number in your textbox control to
positive number, so you don't have to worry whether your users input a
positive or negative number. They can just input the amount (expressed in
positive terms), and the coding will take care of the rest.

If a positive result is required, then you use: Abs(MyAmountControl).
If a negative result is required, you must first force the number to be
positive like above, and then you multiply it by (-1). Therefore, a credited
transaction would force the number to be negative with: Abs(MyAmountControl)
* -1

HTH,
Brian

 

[Robert T]

Hi Brian:

Your coding is working perfectly. The only change I made was adding a MsgBox
to inform the user that the Trans_Type field requires a response of "Debit"
or "Credit".
Thanks so much for your valuable input.

Robert