How to Hide blank values?

[Joe Cilinceon]

Ok I have included the code that I'm using below. It does work with only one
problem, it will not line up correctly. Now I'm figureing is possebly the
fonts used as they are proportional and fixed width. I could be wrong but
would love to be able to line this up correctly.


Here is an example of what I want

Rent $100.00 (I want these to line up)
Late Fee $18.00
NSF Fee 0.00 (don't show this line if value is 0)

Now I' got it down how to stop the row from printing fine it is the spacing
for the line. I can't get the Item to line up on the Left edge with the
charge lined up on the right edge. I hope that is clear and the code I got
someone site.

Option Compare Database
Option Explicit
Function ChargeBlock$(RNT, ADM, LOK, PLF, PLC, PAF, NFC, PBD, PMC, PCE, PCA)

Dim rt$, ad$, lk$, CR$
Dim lf$, lc$, af$, bd$, cf$
Dim mc$, ce$, ca$

Dim LL, L1, L2 As Integer
LL = 130
CR$ = Chr(13) & Chr(10) 'Carriage Return

If ISB(RNT) Then
rt$ = ""
Else
L1 = Len("Rent")
L2 = Len(Trim(Format(RNT, "$####.00")))
rt$ = "Rent" & Space(LL - (L1 + L2)) & Format(RNT, "$####.00") & CR$
End If

L1 = 0
L2 = 0

If ISB(ADM) Then
ad$ = ""
Else
L1 = Len("Administration Fee")
L2 = Len(Trim(Format(ADM, "$####.00")))
ad$ = "Administration Fee" & Space(LL - (L1 + L2)) & Format(ADM,
"$####.00") & CR$
End If

L1 = 0
L2 = 0

If ISB(LOK) Then
lk$ = ""
Else
L1 = Len("Lock")
L2 = Len(Trim(Format(LOK, "$####.00")))
lk$ = "Lock" & Space(LL - (L1 + L2)) & Format(LOK, "$####.00") & CR$
End If
L1 = 0
L2 = 0
If ISB(PLF) Then
lf$ = ""
Else
L1 = Len("Late Fees")
L2 = Len(Trim(Format(PLF, "$####.00")))
lf$ = "Late Fees" & Space(LL - (L1 + L2)) & Format(PLF, "$####.00")
& CR$
End If
L1 = 0
L2 = 0
If ISB(PLC) Then
lc$ = ""
Else
L1 = Len("Lock Cut")
L2 = Len(Trim(Format(PLC, "$####.00")))
lc$ = "Lock Cut" & Space(LL - (L1 + L2)) & Format(PLC, "$####.00") &
CR$
End If
L1 = 0
L2 = 0
If ISB(PAF) Then
af$ = ""
Else
L1 = Len("Auction Fee")
L2 = Len(Trim(Format(PAF, "$####.00")))
af$ = "Auction" & Space(LL - (L1 + L2)) & Format(PAF, "$####.00") &
CR$
End If
L1 = 0
L2 = 0
If ISB(NFC) Then
NFC = ""
Else
L1 = Len("NFC Fee")
L2 = Len(Trim(Format(NFC, "$####.00")))
cf$ = "NFC Fee" & Space(LL - (L1 + L2)) & Format(NFC, "$####.00") &
CR$
End If
L1 = 0
L2 = 0
If ISB(PBD) Then
PBD = ""
Else
L1 = Len("Previous Balance Due")
L2 = Len(Trim(Format(PBD, "$####.00")))
bd$ = "Previous Balance Due" & Space(LL - (L1 + L2)) & Format(PBD,
"$####.00") & CR$
End If
L1 = 0
L2 = 0
If ISB(PMC) Then
PMC = ""
Else
L1 = Len("Misc. Charges")
L2 = Len(Trim(Format(PMC, "$####.00")))
mc$ = "Misc. Charges" & Space(LL - (L1 + L2)) & Format(PMC,
"$####.00") & CR$
End If
L1 = 0
L2 = 0
If ISB(PCE) Then
PCE = ""
Else
L1 = Len("Credit Earned")
L2 = Len(Trim(Format(PCE, "$####.00")))
ce$ = "Credit Earned" & Space(LL - (L1 + L2)) & Trim(Format(PCE,
"$####.00")) & CR$
End If
L1 = 0
L2 = 0
If ISB(PCA) Then
PCA = ""
Else
L1 = Len("Credit Applied")
L2 = Len(Trim(Format(PCA, "$####.00")))
ca$ = "Credit Applied" & Space(LL - (L1 + L2)) & Format(PCA,
"$####.00") & CR$
End If

ChargeBlock = rt$ & ad$ & lk$ & lf$ & lc$ & af$ & cf$ & bd$ & mc$ & ce$
& ca$

End Function

Function ISB(V) As Integer

If IsNull(V) Or V = "" Then
ISB = True
Else
ISB = False
End If
End Function

 

[Rick B]

Not sure what code has to do with this.

You should simply have two fields on your report. One will print the
description and will be left-justified. The other will print the dollar
value and will be right justified.

To exclude zero values, change your query criteria to exclude them.

 

[Joe Cilinceon]

Not sure what code has to do with this.

You should simply have two fields on your report. One will print the
description and will be left-justified. The other will print the
dollar value and will be right justified.

To exclude zero values, change your query criteria to exclude them.

Ok the code is from a receipt we print at the time of payment. Now I'm
trying to list a Tenant's Ledger. For example the fields in the Ledger Table
is as follows:

LEDGER
LedgerID
Transaction
PaymentDate
RentRate
PaymentAmount
PaidFrom
PaidThru
............................ Now from here on are the only items we can
charge for
Rent
Lock
LateFee
AdmFee
NSFFee
etc.

I need the 2 columns to line up the desc and the amounts. On the receipt it
doesn't matter as it just centers everything but it does here. I might have
to just find a way to exclude the empty fields in the query but boy that
will be some query. The query now is linked to 7 different tables and about
50 pieces of data.

For further information on what this is for. It lists a given rental account
on a storage space. Lists the Tenant's Name, account #, Unit #, Movein Date
and MoveOut Date (if there is one) in the header. The next level lists each
transaction and what it pays. Like

 

[Rick B]

Sorry Joe, I'm still lost. Looks like you would print the Transaction as
one field, the Date as one field, and the Amount as one field. You'd line
them up however you want, and justify them appropriately.

Maybe I'm missing something. Maybe someone else will jump in with some
suggestions.

 

[Joe Cilinceon]

Sorry Joe, I'm still lost. Looks like you would print the
Transaction as one field, the Date as one field, and the Amount as
one field. You'd line them up however you want, and justify them
appropriately.
Maybe I'm missing something. Maybe someone else will jump in with
some suggestions.

Let me try to explain it this way. A tenant comes in and pays us rent. He
doesn't have a late fee, nsf fee, auction fee, didn't buy a lock. Now with a
standard method of printing a report I would have a line for each item with
a 0 amount. I would rather in not make a line at all and only list the
possible charges that are paid. That is what that block of code was doing
that I posted earlier. It was the method I used on another report (payment
receipt) however that format will not work here due to the way the columns
should appear. Thanks for at least trying Rick it is appreciated greatly.

 

[Duane Hookom]

Your issue is that your table structure seems to "commit spreadsheet."

I would consider changing the table structure to resemble the Orders and
Order Details in Northwind. Every charge to a tenant becomes a record in a
table. Each fee should be recorded in its own record, not a boat-load of
fields. I would hate to have to maintain your application if you want to add
another fee type. I can't imagine the design changes that would be required.

 

[Rick B]

Okay, sounds like you might have a bad design then. You seem to enter a
payment and then have several fields where amounts are posted. In 9% of the
cases, only one field would be filled in. You seem to be trying to use
Access to create a spreadsheet (flat file). You seem to have a column for
each of the receipt details, and then you have a column in that same table
for all of the possible payment types (rent, late fee, lock, etc.) That is
a spreadsheet design, not a relational database design.

Instead, you should have at least one-to-many relationship. You should have
a table to store your payment main data and a separate related table to
store details. If a payment of $500 is made, you'd have one record in your
main table. If that payment was all applied to rent, you'd have one record
in your details table. (Basically, you have a debit and one credit).

If a Person made a $500 payment and $400 was for rent, and $100 was for a
late fee, then you'd have one record in your main payment table ($500 debit)
and two records in your detail table ($400 credit and $100 credit).

When you print your reports, it would only print data that is there.

Furthermore, I'd maintain the various available payment "reasons" in a third
table.


TblPayment
RectNumber
RecDate
AptNumber
Amount
etc.


TblPaymentDetails
RectNumber
ReasonNumber
DetailAmount

TblPaymentReasons
ReasonNumber
ReasonDescription
Inactive(Y/N)

 

[Duane Hookom]

Well thought out reply with good examples.

 

[Rick B]

Thanks, Duane!

--
Rick B

Well thought out reply with good examples.

 

[Joe Cilinceon]

Thanks Duane, I'm not sure what "commit spreadsheet" means. I started this
application 14 months ago and asked a lot of questions about setting up the
table structure for each area of this application. When I got to the section
about posting payments and tracking paid thru dates I got buy an accounting
program. Well needless to say I didn't listen and won't since most of the
programs for this business are done in 100% MS Access and they all handle
what I need to do.

Please accept my thanks for your help and if you are willing to help
further with this problem then read on.

What I have is are Tenant tables. This keeps the usual stuff, name address,
phone number with each tenant given a unique CustNo. Now you can have 1
tenant rent several units so I now have a table called Lease. Now Leases has
a unique code based on the CustNo, UnitNo and date rented. This text is the
LedgerID (basically an account number) with the table holding things like
movein date, invoiced etc.

Ok we are now getting to where I'm having the problems. The next table is
the Ledger and it is linked to the account by the LedgerID. Every payment is
handled as a single transaction and it generate a transaction # called
Transaction. Now a record in this table has the LedgerID, Transaction,
PaymentDate, RentRate, PaidFrom, PaidThru, Rent, LateFees, AdmFee, NSF,
Lock, LockCutFee, AuctionFee, PrevBalDue, MiscChg and a couple of fields to
handle overpayment or underpayment.

There is one last table that is tied to the Ledger Table and that is
Payments. This one is fairly simple in that it has the amount paid, payment
method, tracking (check number/credit card app. code). This table setup
makes it possible to have split payments which are very common in this
business.

Now how would you suggest I change the layout of the LEDGER table and in
what way. I was thinking of adding another table called charges which would
have 3 fields. Transaction, ChargeID, ChgAmt. It would be fairly easy to
implement but would also require a Charges Lookup table with ChargeID and
Charge Description.

Thanks again Duane

 

[Joe Cilinceon]

Thank you Rick that is what I was thinking after reading Duane's post I just
answered. I have a similar table that tracks the payments as we often get
split payments. Thank you much Rick I will begin making these changes to the
table structure.

 

[Duane Hookom]

I assume Rick's post has provided the answers to your questions. "commiting
spreadsheet" is a phrase borrowed from Jeff Boyce. It describes a table
structure that resembles a spreadsheet that might be used in Excel.

--
Duane Hookom
MS Access MVP
--

Thanks Duane, I'm not sure what "commit spreadsheet" means. I started this
application 14 months ago and asked a lot of questions about setting up
the table structure for each area of this application. When I got to the
section about posting payments and tracking paid thru dates I got buy an
accounting program. Well needless to say I didn't listen and won't since
most of the programs for this business are done in 100% MS Access and they
all handle what I need to do.

Please accept my thanks for your help and if you are willing to help
further with this problem then read on.

What I have is are Tenant tables. This keeps the usual stuff, name
address, phone number with each tenant given a unique CustNo. Now you can
have 1 tenant rent several units so I now have a table called Lease. Now
Leases has a unique code based on the CustNo, UnitNo and date rented. This
text is the LedgerID (basically an account number) with the table holding
things like movein date, invoiced etc.

Ok we are now getting to where I'm having the problems. The next table is
the Ledger and it is linked to the account by the LedgerID. Every payment
is handled as a single transaction and it generate a transaction # called
Transaction. Now a record in this table has the LedgerID, Transaction,
PaymentDate, RentRate, PaidFrom, PaidThru, Rent, LateFees, AdmFee, NSF,
Lock, LockCutFee, AuctionFee, PrevBalDue, MiscChg and a couple of fields
to handle overpayment or underpayment.

There is one last table that is tied to the Ledger Table and that is
Payments. This one is fairly simple in that it has the amount paid,
payment method, tracking (check number/credit card app. code). This table
setup makes it possible to have split payments which are very common in
this business.

Now how would you suggest I change the layout of the LEDGER table and in
what way. I was thinking of adding another table called charges which
would have 3 fields. Transaction, ChargeID, ChgAmt. It would be fairly
easy to implement but would also require a Charges Lookup table with
ChargeID and Charge Description.

Thanks again Duane

 

[Rick B]

Joe:

Glad I could help!



--
Rick B

Thank you Rick that is what I was thinking after reading Duane's post I
just answered. I have a similar table that tracks the payments as we often
get split payments. Thank you much Rick I will begin making these changes
to the table structure.

 

[Joe Cilinceon]

I assume Rick's post has provided the answers to your questions.
"commiting spreadsheet" is a phrase borrowed from Jeff Boyce. It
describes a table structure that resembles a spreadsheet that might
be used in Excel.

Yes it did Duane. I wished you two had been in the tabledesign group 14
months ago. When I started this application I asked a lot of questions in
regards to doing this section of the project and got the "buy a program"
answer. Well as you can see I didn't take that advise and even with this I
can fix it fairly easily. It seems the minute something to do with tracking
money comes up no one wants to help. So again I thankful to you two.

I have setup and updated the 2 new tables already. I setup a Charges
(Transaction, ChgID, ChgAmt) and tlkpChgDesc (ChgID, ChgDesc) tables. I have
also filled them with the data from the old Ledger Table. I will also have
to redo a the couple of subforms and reports but again this shouldn't be all
that difficult to do.