Re: Help with balance calculation....

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.



"Brook" <Brook@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:35742394-C64D-4FDA-9202-B3415BABFF3B@xxxxxxxxxxxxxxxx
> Would would you need to determine if I needed NZ? What does NZ do?
>
> Brook
>
> "Rob Oldfield" wrote:
>
> > 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.
> >
> >
> >
> > "Brook" <Brook@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:EC897527-82AA-46CF-95B9-91C24D5020C7@xxxxxxxxxxxxxxxx
> > > 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" wrote:
> > >
> > > > 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" <Brook@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > news:9DD57486-EA2E-4105-9395-F873F48E67C4@xxxxxxxxxxxxxxxx
> > > > > 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" wrote:
> > > > >
> > > > > > 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" <Brook@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > > news:A9FFB5EA-DB27-4CDD-B8E8-25E0545CA2C3@xxxxxxxxxxxxxxxx
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >


.

Quantcast