Help with balance calculation....

[Brook]

Hi all,

I have a checkingacctform I use for my business with the following relevent
fields:

Debit (txtbox)
Crebit (txtbox)
Reconciled (yes/no checkbox)

I would like to have a field on my form for both Debit and credit so that
if I l click the reconciled yes/no box the Debit and/or Credit total will
update with a new balance?

Does anyone have any ideas?

thanks,

Brook

 

[Rob Oldfield]

Basically, it'll just be code in the after update event of the checkbox
along the lines of...

if me.reconciled then
'code to get new debit/credit info
endif

If you want any more information than that, then you'd need to specify
exactly where those new credit/debit figures are coming from.

 

[Brook]

Rob,

Thanks for the post. Here is what I currently have, the problem I'm having
is that when I click the reconciled button for a Debit, the debit balance
works fine but then clears the anything (includeing the default value of
$0.00) from the txtcredit balance.

I cannot figure it out:

So if I reconcile a Credit Item, the balance will show up in the txtCredit
but if I then reconcile a Debit Item, the Credit balance disappears and the
Debit balance shows up properly, and if I click another debit, it totals
properly.

I have been racking my brain with this...

Any ideas?

All are set to currency, 2 decimals, default value of 0

Begin Code:
Private Sub Reconciled_AfterUpdate()
If Me.Reconciled = True Then
Me.txtDebit = Me.txtDebit + Me.Debit
Else
If Me.Reconciled = False Then
Me.txtDebit = Me.txtDebit - Me.Debit
End If
End If

If Me.Reconciled = True Then
Me.txtCredit = Me.txtCredit + Me.Credit
Else
If Me.Reconciled = False Then
Me.txtCredit = Me.txtCredit - Me.Credit
End If

End Code

 

[Rob Oldfield]

What are me.debit and me.credit? Fields on the underlying query? If so,
what does that query look like (i.e. post the sql)?

 

[Brook]

Rob,

Yes, those re fields in my query...

Here is the sql to the query..

SELECT tblcheckingaccount.ID, tblcheckingaccount.datereconciled,
tblcheckingaccount.Reconciled, tblcheckingaccount.accountid,
tblcheckingaccount.accounttype, tblcheckingaccount.Payee,
tblcheckingaccount.Date, tblcheckingaccount.Debit, tblcheckingaccount.Credit,
CCur(Nz([Credit],0)+Nz([Debit],0)) AS Debit_Credit,
tblcheckingaccount.Balance, tblcheckingaccount.Description,
tblcheckingaccount.Notes, tblcheckingaccount.Paymentcat,
tblcheckingaccount.Reference
FROM tblcheckingaccount
ORDER BY tblcheckingaccount.ID;

Thanks,

Brook

 

[Rob Oldfield]

Without seeing your data I can't be sure but I'd guess that, for each
record, either debit or credit is going to be null. In which case your code
is going to fail because one or other of the Me.txtDebit = Me.txtDebit -
Me.Debit type lines is going to be using a null argument. And
AnyNumber+null comes out as null.

Think you might need a couple of nz functions in there.

Rob,

Yes, those re fields in my query...

Here is the sql to the query..

SELECT tblcheckingaccount.ID, tblcheckingaccount.datereconciled,
tblcheckingaccount.Reconciled, tblcheckingaccount.accountid,
tblcheckingaccount.accounttype, tblcheckingaccount.Payee,
tblcheckingaccount.Date, tblcheckingaccount.Debit, tblcheckingaccount.Credit,
CCur(Nz([Credit],0)+Nz([Debit],0)) AS Debit_Credit,
tblcheckingaccount.Balance, tblcheckingaccount.Description,
tblcheckingaccount.Notes, tblcheckingaccount.Paymentcat,
tblcheckingaccount.Reference
FROM tblcheckingaccount
ORDER BY tblcheckingaccount.ID;

Thanks,

Brook

What are me.debit and me.credit? Fields on the underlying query? If so,
what does that query look like (i.e. post the sql)?


and
the so
that total
will

 

[Brook]

Would would you need to determine if I needed NZ? What does NZ do?

Brook

Without seeing your data I can't be sure but I'd guess that, for each
record, either debit or credit is going to be null. In which case your code
is going to fail because one or other of the Me.txtDebit = Me.txtDebit -
Me.Debit type lines is going to be using a null argument. And
AnyNumber+null comes out as null.

Think you might need a couple of nz functions in there.

Rob,

Yes, those re fields in my query...

Here is the sql to the query..

SELECT tblcheckingaccount.ID, tblcheckingaccount.datereconciled,
tblcheckingaccount.Reconciled, tblcheckingaccount.accountid,
tblcheckingaccount.accounttype, tblcheckingaccount.Payee,
tblcheckingaccount.Date, tblcheckingaccount.Debit, tblcheckingaccount.Credit,
CCur(Nz([Credit],0)+Nz([Debit],0)) AS Debit_Credit,
tblcheckingaccount.Balance, tblcheckingaccount.Description,
tblcheckingaccount.Notes, tblcheckingaccount.Paymentcat,
tblcheckingaccount.Reference
FROM tblcheckingaccount
ORDER BY tblcheckingaccount.ID;

Thanks,

Brook

What are me.debit and me.credit? Fields on the underlying query? If so,
what does that query look like (i.e. post the sql)?


Rob,

Thanks for the post. Here is what I currently have, the problem I'm
having
is that when I click the reconciled button for a Debit, the debit balance
works fine but then clears the anything (includeing the default value of
$0.00) from the txtcredit balance.

I cannot figure it out:

So if I reconcile a Credit Item, the balance will show up in the
txtCredit
but if I then reconcile a Debit Item, the Credit balance disappears and
the
Debit balance shows up properly, and if I click another debit, it totals
properly.

I have been racking my brain with this...

Any ideas?

All are set to currency, 2 decimals, default value of 0

Begin Code:
Private Sub Reconciled_AfterUpdate()
If Me.Reconciled = True Then
Me.txtDebit = Me.txtDebit + Me.Debit
Else
If Me.Reconciled = False Then
Me.txtDebit = Me.txtDebit - Me.Debit
End If
End If

If Me.Reconciled = True Then
Me.txtCredit = Me.txtCredit + Me.Credit
Else
If Me.Reconciled = False Then
Me.txtCredit = Me.txtCredit - Me.Credit
End If

End Code

:

Basically, it'll just be code in the after update event of the checkbox
along the lines of...

if me.reconciled then
'code to get new debit/credit info
endif

If you want any more information than that, then you'd need to specify
exactly where those new credit/debit figures are coming from.


Hi all,

I have a checkingacctform I use for my business with the following
relevent
fields:

Debit (txtbox)
Crebit (txtbox)
Reconciled (yes/no checkbox)

I would like to have a field on my form for both Debit and credit so
that
if I l click the reconciled yes/no box the Debit and/or Credit total
will
update with a new balance?

Does anyone have any ideas?

thanks,

Brook

 

[Brook]

Rob,

Well, why would checking the reconcile check box for a debit nullify the
credit balance (txtcredit)?

Brook

Without seeing your data I can't be sure but I'd guess that, for each
record, either debit or credit is going to be null. In which case your code
is going to fail because one or other of the Me.txtDebit = Me.txtDebit -
Me.Debit type lines is going to be using a null argument. And
AnyNumber+null comes out as null.

Think you might need a couple of nz functions in there.

Rob,

Yes, those re fields in my query...

Here is the sql to the query..

SELECT tblcheckingaccount.ID, tblcheckingaccount.datereconciled,
tblcheckingaccount.Reconciled, tblcheckingaccount.accountid,
tblcheckingaccount.accounttype, tblcheckingaccount.Payee,
tblcheckingaccount.Date, tblcheckingaccount.Debit, tblcheckingaccount.Credit,
CCur(Nz([Credit],0)+Nz([Debit],0)) AS Debit_Credit,
tblcheckingaccount.Balance, tblcheckingaccount.Description,
tblcheckingaccount.Notes, tblcheckingaccount.Paymentcat,
tblcheckingaccount.Reference
FROM tblcheckingaccount
ORDER BY tblcheckingaccount.ID;

Thanks,

Brook

What are me.debit and me.credit? Fields on the underlying query? If so,
what does that query look like (i.e. post the sql)?


Rob,

Thanks for the post. Here is what I currently have, the problem I'm
having
is that when I click the reconciled button for a Debit, the debit balance
works fine but then clears the anything (includeing the default value of
$0.00) from the txtcredit balance.

I cannot figure it out:

So if I reconcile a Credit Item, the balance will show up in the
txtCredit
but if I then reconcile a Debit Item, the Credit balance disappears and
the
Debit balance shows up properly, and if I click another debit, it totals
properly.

I have been racking my brain with this...

Any ideas?

All are set to currency, 2 decimals, default value of 0

Begin Code:
Private Sub Reconciled_AfterUpdate()
If Me.Reconciled = True Then
Me.txtDebit = Me.txtDebit + Me.Debit
Else
If Me.Reconciled = False Then
Me.txtDebit = Me.txtDebit - Me.Debit
End If
End If

If Me.Reconciled = True Then
Me.txtCredit = Me.txtCredit + Me.Credit
Else
If Me.Reconciled = False Then
Me.txtCredit = Me.txtCredit - Me.Credit
End If

End Code

:

Basically, it'll just be code in the after update event of the checkbox
along the lines of...

if me.reconciled then
'code to get new debit/credit info
endif

If you want any more information than that, then you'd need to specify
exactly where those new credit/debit figures are coming from.


Hi all,

I have a checkingacctform I use for my business with the following
relevent
fields:

Debit (txtbox)
Crebit (txtbox)
Reconciled (yes/no checkbox)

I would like to have a field on my form for both Debit and credit so
that
if I l click the reconciled yes/no box the Debit and/or Credit total
will
update with a new balance?

Does anyone have any ideas?

thanks,

Brook

 

[Rob Oldfield]

Sorry. I assumed that because it was included in your query, that you knew
how it worked.

One of the major things that you have to get used to in Access is how to
cope with null (i.e. blank) values. They cause a problem because if you,
for example, want to add two things up then, if one of them is null, then
the addition will come out as null. The intuitive thing to expect is that
if you say z=x+y, where x is 3 and y is null, then z will come out as 3.
But it doesn't work. It's null as well. Similar with comparisons - if you
have something that says 'if x=y' and both x and y are null, then you'd
expect a 'True' result, but no, that's null again.

And what I think is happening is that where you're doing things like
Me.txtDebit - Me.Debit then me.debit is null and it's going bang.

The nz function basically turns null values into 'live' things that can be
used. So if x is null, then nz(x,43) is 43 (i.e. it will return x if x
isn't null, but a 0 if it is)

So... try replacing me.credit with nz(me.credit,0) and me.debit with
nz(me.debit,0) in your code.

Would would you need to determine if I needed NZ? What does NZ do?

Brook

Without seeing your data I can't be sure but I'd guess that, for each
record, either debit or credit is going to be null. In which case your code
is going to fail because one or other of the Me.txtDebit = Me.txtDebit -
Me.Debit type lines is going to be using a null argument. And
AnyNumber+null comes out as null.

Think you might need a couple of nz functions in there.

Rob,

Yes, those re fields in my query...

Here is the sql to the query..

SELECT tblcheckingaccount.ID, tblcheckingaccount.datereconciled,
tblcheckingaccount.Reconciled, tblcheckingaccount.accountid,
tblcheckingaccount.accounttype, tblcheckingaccount.Payee,
tblcheckingaccount.Date, tblcheckingaccount.Debit, tblcheckingaccount.Credit,
CCur(Nz([Credit],0)+Nz([Debit],0)) AS Debit_Credit,
tblcheckingaccount.Balance, tblcheckingaccount.Description,
tblcheckingaccount.Notes, tblcheckingaccount.Paymentcat,
tblcheckingaccount.Reference
FROM tblcheckingaccount
ORDER BY tblcheckingaccount.ID;

Thanks,

Brook

:

What are me.debit and me.credit? Fields on the underlying query?

If
so,

what does that query look like (i.e. post the sql)?


Rob,

Thanks for the post. Here is what I currently have, the problem I'm
having
is that when I click the reconciled button for a Debit, the debit balance
works fine but then clears the anything (includeing the default

value
of

$0.00) from the txtcredit balance.

I cannot figure it out:

So if I reconcile a Credit Item, the balance will show up in the
txtCredit
but if I then reconcile a Debit Item, the Credit balance

disappears
and

the
Debit balance shows up properly, and if I click another debit, it totals
properly.

I have been racking my brain with this...

Any ideas?

All are set to currency, 2 decimals, default value of 0

Begin Code:
Private Sub Reconciled_AfterUpdate()
If Me.Reconciled = True Then
Me.txtDebit = Me.txtDebit + Me.Debit
Else
If Me.Reconciled = False Then
Me.txtDebit = Me.txtDebit - Me.Debit
End If
End If

If Me.Reconciled = True Then
Me.txtCredit = Me.txtCredit + Me.Credit
Else
If Me.Reconciled = False Then
Me.txtCredit = Me.txtCredit - Me.Credit
End If

End Code

:

Basically, it'll just be code in the after update event of the checkbox
along the lines of...

if me.reconciled then
'code to get new debit/credit info
endif

If you want any more information than that, then you'd need to specify
exactly where those new credit/debit figures are coming from.


Hi all,

I have a checkingacctform I use for my business with the following
relevent
fields:

Debit (txtbox)
Crebit (txtbox)
Reconciled (yes/no checkbox)

I would like to have a field on my form for both Debit and

credit
so

that
if I l click the reconciled yes/no box the Debit and/or Credit total
will
update with a new balance?

Does anyone have any ideas?

thanks,

Brook

 

[Rob Oldfield]

Because I think that either one or the other will be null. (See other post)

Rob,

Well, why would checking the reconcile check box for a debit nullify the
credit balance (txtcredit)?

Brook

Without seeing your data I can't be sure but I'd guess that, for each
record, either debit or credit is going to be null. In which case your code
is going to fail because one or other of the Me.txtDebit = Me.txtDebit -
Me.Debit type lines is going to be using a null argument. And
AnyNumber+null comes out as null.

Think you might need a couple of nz functions in there.

Rob,

Yes, those re fields in my query...

Here is the sql to the query..

SELECT tblcheckingaccount.ID, tblcheckingaccount.datereconciled,
tblcheckingaccount.Reconciled, tblcheckingaccount.accountid,
tblcheckingaccount.accounttype, tblcheckingaccount.Payee,
tblcheckingaccount.Date, tblcheckingaccount.Debit, tblcheckingaccount.Credit,
CCur(Nz([Credit],0)+Nz([Debit],0)) AS Debit_Credit,
tblcheckingaccount.Balance, tblcheckingaccount.Description,
tblcheckingaccount.Notes, tblcheckingaccount.Paymentcat,
tblcheckingaccount.Reference
FROM tblcheckingaccount
ORDER BY tblcheckingaccount.ID;

Thanks,

Brook

:

What are me.debit and me.credit? Fields on the underlying query?

If
so,

what does that query look like (i.e. post the sql)?


Rob,

Thanks for the post. Here is what I currently have, the problem I'm
having
is that when I click the reconciled button for a Debit, the debit balance
works fine but then clears the anything (includeing the default

value
of

$0.00) from the txtcredit balance.

I cannot figure it out:

So if I reconcile a Credit Item, the balance will show up in the
txtCredit
but if I then reconcile a Debit Item, the Credit balance

disappears
and

the
Debit balance shows up properly, and if I click another debit, it totals
properly.

I have been racking my brain with this...

Any ideas?

All are set to currency, 2 decimals, default value of 0

Begin Code:
Private Sub Reconciled_AfterUpdate()
If Me.Reconciled = True Then
Me.txtDebit = Me.txtDebit + Me.Debit
Else
If Me.Reconciled = False Then
Me.txtDebit = Me.txtDebit - Me.Debit
End If
End If

If Me.Reconciled = True Then
Me.txtCredit = Me.txtCredit + Me.Credit
Else
If Me.Reconciled = False Then
Me.txtCredit = Me.txtCredit - Me.Credit
End If

End Code

:

Basically, it'll just be code in the after update event of the checkbox
along the lines of...

if me.reconciled then
'code to get new debit/credit info
endif

If you want any more information than that, then you'd need to specify
exactly where those new credit/debit figures are coming from.


Hi all,

I have a checkingacctform I use for my business with the following
relevent
fields:

Debit (txtbox)
Crebit (txtbox)
Reconciled (yes/no checkbox)

I would like to have a field on my form for both Debit and

credit
so

that
if I l click the reconciled yes/no box the Debit and/or Credit total
will
update with a new balance?

Does anyone have any ideas?

thanks,

Brook