Re: Nz Function question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steve Schapel (schapel_at_mvps.org.ns)
Date: 01/28/05 

Date: Fri, 28 Jan 2005 19:56:10 +1300

Joe,

Thanks for the further explanation, and I am pleased to know that you
have now got it working.

Of course, getting it working was your goal, so you may not want any
other comments. But I will just say a couple of things. First one is
that one of the incredible strengths of Access is the ability to easily
use bound forms. I am not sure whether the form you are using is a
bound form or not, but either way it is apparent that there would be
much simpler ways of achieving your purpose here, by way of data being
directly entered to your tables via the form. Secondly, your project is
probably well under way now, and it may be hard to change, but there are
certain aspects of your table design that are destined to make life
unnecessarily difficult for you. If there is any way you could make the
data structure more normalised, it would be worth the effort, in my opinion. 

-- 
Steve Schapel, Microsoft Access MVP
Joe Cilinceon wrote:
> Thanks Steve. Now what I have is a form for taking all types of payments 
> involved in self storage . These payments are posted to 2 tables (Ledger & 
> Payments). Now a tenant can pay other charges as you can see below that are 
> not always rent. Only when rent is paid are the PaidFrom and PaidThru fields 
> have dates other wise they will be blank or Null.
> 
> Now I've done it with If then statements as you can see below in the SQL 
> statement. I did try Me.txtPaidThru = Null and got an error "Improper use of 
> Null". I also tried to leave the text box blank and got a syntax error due 
> to the ## in the SQL statement for a date. I tried the Nz() function but it 
> puts a time in the field even though it is marked as a short date. I've got 
> it working now by simply using a set of if then statements in the SQL string 
> to stop these fields for being appended, hence they are left Null.
> 
>      'Append Payment to the LEDGER
>     strSQL = "INSERT INTO LEDGER ( Transaction, LedgerID, "
>     strSQL = strSQL & "PaymentDate, PaymentAmount, "
> 
>     ' here I stop the 4 fields that are part of the rent
>     If PayRent = 1 Then            'The PayRent is Public var. that is 
> either 1 to pay rent or 0 no rent.
>         strSQL = strSQL & "RentRate, PaidFrom, PaidThru, Rent, "
>     End If
>     strSQL = strSQL & "AdmistrationFee, Lock, LateFees, NSFCheckFee, "
>     strSQL = strSQL & "LockCutFee, AuctionFee, MiscChg, MiscChgDesc, Waved, 
> "
>     strSQL = strSQL & "WaveDesc, RentAllowance, RentAllReason, 
> CreditApplied, "
>     strSQL = strSQL & "CreditEarned, CreditReason, PreviousBalDue, 
> BalanceDue)"
>     strSQL = strSQL & " SELECT "
>     strSQL = strSQL & Me.txtTrans & " AS Transaction, '"
>     strSQL = strSQL & Me.txtLedgerID & "' AS LedgerID, #"
>     strSQL = strSQL & Date & "# As PaymentDate, "
>     strSQL = strSQL & [fsubTakePayment].Form![txtTotalPaid] & " AS 
> PaymentAmount, "
> 
>     ' here I limit the fields unless paying rent
>     If PayRent = 1 Then
>         strSQL = strSQL & Me.Rate & " AS RentRate, "
>         strSQL = strSQL & "#" & Me.NewPaidFrom & "# AS PaidFrom, "
>         strSQL = strSQL & "#" & Me.txtPaidThru & "# AS PaidThru, "
>         strSQL = strSQL & Me.TotalRentPaid & " AS Rent, "
>     End If
>     strSQL = strSQL & Me.AdmFee & " AS AdmistrationFee, "
>     strSQL = strSQL & Me.PurLock & " AS Lock, "
>     strSQL = strSQL & Me.LateFeesPaid & " AS LateFees, "
>     strSQL = strSQL & Me.PayNSFFee & " AS NSFCheckFee, "
>     strSQL = strSQL & Me.LockCutPaid & " AS LockCutFee, "
>     strSQL = strSQL & Me.AuctionPaid & " AS AuctionFee, "
>     strSQL = strSQL & Me.MiscChgs & " AS MiscChg, "
>     strSQL = strSQL & Nz(Me.CboMiscChg, 0) & " AS MiscChgDesc, "
>     strSQL = strSQL & Me.FeesWaved & " AS Waved, "
>     strSQL = strSQL & Nz(Me.cboWaveDesc, 0) & " AS WaveDesc, "
>     strSQL = strSQL & Me.RENTALLOWENCE & " AS RentAllowance, "
>     strSQL = strSQL & Nz(Me.CboRentAllow, 0) & " AS RentAllReason, "
>     strSQL = strSQL & Nz(Me.CreditsApp, 0) & " AS CreditApplied, "
>     strSQL = strSQL & tmpCreditEarned & " AS CreditEarned, "
>     strSQL = strSQL & tmpCreditReason & " AS CreditReason, "
>     strSQL = strSQL & Me.PrevBal & " AS PreviousBalDue, "
>     strSQL = strSQL & Me.txtBalanceDue & " AS BalanceDue ;"
>

Relevant Pages

  • Re: How to bring a small claims court action?
    ... >the rent until two payments had been missed (the tenant finally left after ... >missing 3 payments). ... I wonder if they would be liable for a months rent too ...
    (uk.legal)
  • Re: buy to let mortgages
    ... you can always borrow the shortfall ... or capital re-) payments will be met. ... rent to be around 125% of the interest payments. ... requirement automatically forces a 20% deposit. ...
    (uk.finance)
  • Re: Is my Ex girlfriend liable for her half of the rent ?
    ... and promise to pay half of the rent, she signed the lease on the ... understanding that the rent had *already been* paid for the forthcoming ... and payments for a year, ... If the evidence is as stated and there is no other significant ...
    (uk.legal)
  • Re: Nz Function question
    ... Thanks Steve. ... Now what I have is a form for taking all types of payments ... Now a tenant can pay other charges as you can see below that are ... Only when rent is paid are the PaidFrom and PaidThru fields ...
    (microsoft.public.access.formscoding)
  • Re: query records by month
    ... You might try a query whose SQL looks something like this: ... If a person makes no payments in a given month, ... If it's possible for one person to rent more than one "thing" (whatever ...
    (microsoft.public.access.queries)