Nz Function question

  • Thread starter Thread starter Joe Cilinceon
  • Start date Start date
J

Joe Cilinceon

Does Nz work with a Date. I have a payment posting form that will take all
kinds of payments. In one situation someone may not be paying rent so the
paid thru date will not change. I'm trying to set the date field to Null in
this case. I've tried tmpPaidThru = Null and get and error. This data is
save using an SQL append routine. I also tried Nz(tmpPaidThru) but it puts
in the time.
 
Setting a value to null sometimes doesn't work, could setting it's value to 0 work?
 
Joe,

Yes, the Nz() function does work with a Date/Time field, but it doesn't
sound like what you want. The purpose of the Nz() function is to assign
a value to a field which is curently Null, whereas you are trying to set
a field to Null which currently has a value. Am I right? It is a bit
confusing, because it sounds like you are adding a new record, so why
don't you just leave the tmpPaidThru control blank? Otherwise, your
suggestion of code such as...
Me.tmpPaidThru = Null
.... should work, but you didn't mention what error you received, so
can't comment further on this. But then, you mention about an "SQL
append routine" so maybe you could give some more details about this,
might help to know what you're doing.
 
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 ;"
 
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 said:
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 ;"
Click to expand...
 
Hi Steve

I realy do appreaciate the help. I asked about 20 times for advise on doing
a table layout for this project and only got "go buy an accounting program"
or hire me to do it for you. I did manage to do is get an inside one of the
better applications for this business and see how their tables are done. It
gave me some idea but I'm not building a program on thier scale so much
isn't needed.

This is a one person operation just trying to find and easy way to keep
track of 455 storage units. We don't have $3000 to spend on an application
at least not till 2006 since other things like paint, door replacement,
concrete drive way repair are all on the agenda for this year. I do own
Office XP Developer, a new computer and though I've not writen a program in
15 years I figure I can at least beat the paper system used here till Nov 1.

Now with that said I am open to suggestions on how could I improve the
database design. Below is a very short version of what is does and how I
have it designed.

We rent self storage spaces (455 total) with 500 current and non current
tenants on record. A tenant can rent more than one unit, can transfer from
one unit to the next or move out and we have even had the swap units. It is
also not uncommen to vacate a unit in the AM and rent it in the PM to
someone else. When they rent we prorated first months till the end of the
current month so every one is due on the first. A trasfer we have to
transfer the un used rent to the new unit and close the ledger on the old.
We then gererate a receipt and a rental agreement based on the tenant, unit
and ledger data that must signed by both us and the tenant (legal). It also
gererates Late Letters on the 10th of the month, Invoices on the 11th,
lockcut letterson the 15th and auction letters on the last day of the month,
in addition it creates NSF letters (bad checks), balance due/ partial
payment letters.Weekly reports about the property such as occupancy,
delinquency, and finiancial data. It also tracks our quartely compititon
reports, employee records (3 of us total), bank deposits, petty cash and
other expenses.

Tenant table
CustNo (autonumber)
Name, Address, Phone number and such is stored here just for the tenant.
(The follow tables may or may not have records, if none give no records
created)
Tenant Contact table.
CustNo (ties it to the Tenant file
(Here is kept emergency contact information or in the case of a company
the contact who pay an such
Tenant Notes table
CustNo (same as above)
(Every time we speak to a tenant in regards to anything business related
we note it with a date and time stamp)
TenantLetters table
Custno (again is the tie)
Every letter generated is logged here automatically.

UNITS (455 records)
UnitID (The units number 1 to 455) Integer
Type (type unit ties to the UnitData Table) Integer
Status (rented, vacant, damaged, combined with another unit, etc)
Integer

UnitData (8 records cover it all.)
Type (Integer and unique)
Size (5x5, 5x10, 10x10, RV Parking etc)
SQFT (square footage)

(We can vacate a unit this morning and rent it again in the afternoon to a
different tenant)
LEASES (ties unit and tenant together)
LedgerID (text field made up of CustNO & UnitNo & StartDate (20050128
would be today)
StartDate
EndDate
LockStatus (locked out or not)
GateCode
Invoiced
AutoPay
(much more here but I'm sure you get the idea)

Now for the part I asked for help with how to keep track of and post
payments

LEDGER
Transaction (autonumber) 1 trans 1 number
LedgerID Who and what
PaymentDate
Rent
Fees
(here on you see it is a break down of what covers what)

PAYMENT (allows split payments as there can be only 1 Ledger record but many
payments on the same transaction)
Transaction (links to LEDGER
PaymentAmount
Method (Integer ties it to a lookup table) Cash, Check, CRCD, Money
Order, Travels checks etc
Tracking (Check number and such

Now you have the basic layout of this database. There are several lookup
support tables such as Movein Type, Payment Type ect. The Tenants, Units and
such are all done in Bound forms and works great including having a copy of
their ID/Picture displayed with their record. Now as for payments those are
done on an unbound form as I use a series of queries to track the PaidThru
dates, assign fees on the 10th of the month, track lock cut fees after 45
days, Lien fees after 60 days. We also use this BalanceDue query to gererate
Invoices, and other letters sent out on set days of the month.

All this was started from scratch on Nov. 1, 2004 and is up and working
since Dec 4th, I'm working on improving some of the features now such as
auto reminders, reservations, waiting list, true auto pay (have it done in a
batch instead of 1 card at a time by hand now), batch payments (more than
one unit paid at one time in one check) . Gate control so spaces are locked
and unlocked based on the delinquency status automatically (with manual
override in place).


--
Thanks

Joe Cilinceon



SNIPPED
 
Joe,

Thanks for the further detail. However, my comments were in relation to
your previous post. First of all, why don't you just base your form on
a table, or more likely a query based on the applicable tables, rather
than what you are apparently doing which is to enter the data into
unbound controls on a form and then use over-complicated procedures to
transfer this data to the tables? And secondly, I was referring to all
the fields like Rent, AdmistrationFee, LateFees, NSFCheckFee,
LockCutFee, AuctionFee, MiscChg, etc, which don't appear in your latest
description, but would appear to indicate the need for a separate
Charges table.

Sorry to hear that you had the experience of repeatedly asking for help
and not getting what you needed.
 
Steve the form is using a BalanceDue query to load the fields we are not
entering all of this in. Let me try again to explain this as it is seems to
be complicated to most when I try to explain it. First the forms fields are
filled depending on what one is paying. For these choices we have a button
on the form such as Apply Rent, Apply Previous Balance, Apply NSF. Even an
Apply Credit if available. Perhaps an example would help more.

EXAMPLE: A tenant comes in on the 10th of the month with his check in hand,
however his check is made out for only the rent and doesn't cover the late
fee due on the 10th. Now he can pay the rent with the check and the late fee
with cash, credit card or another check. He can even say he will send the
late fee with his next payment. Now the form I have, allows us to take the
total payment and Zero out some things like the late fee which can either be
waved (forgotten) or added to a Balance Due field. Once this is done a
subform accepts the payment amount, method of pay and tracking # if required
(check #).
Once we hit the Post payment button the querys which you saw only one of
updates the tenant account to show all changes made by the payment.

This system now allows for split payments which are very common for us.
Posting a payment takes less than 30 seconds with a printed receipt/or
BalanceDue letter printed. Now we also must explain any changes from what is
owed. By this I mean if a tenant has been with us 6 months and no late fees
I might decide to wave it, however I must be able to explain it in a report.
I just pick the reason for the wave from a combo box and it is done. I can
also track any tenants payment from beginning to end of their stay
regardless.

How would you suggest I setup a charges table for this? Now I have one
record for each transaction or payment charge record, which ever you prefer
to call it. I also have multiple records (if payment is made using say cash
and check). I really do appreciate the input Steve.
 
Back
Top