Help With Report Coding

  • Thread starter Thread starter PaleRider
  • Start date Start date
P

PaleRider

Hi,

I would like to open a report for delinquent bills from a combo box where
the choices are to see only records where bills are delinquent 1-7 days, 1-30
days, 1-60 days or ALL records. How to code combo box to do this?

(tblMain)
Main_ID = autonumber
FName = text
LName = text
Phone = text
ClaimNo = text
DatePaid = DATE/TIME
AmtDue = number
DaysLate = number
Notes = memo

(qryResidents)
includes all fields in tblMain

(frmResidents)
cboDaysLate = control linked to tblMain, DaysLate

(rptResidents)
Control Source = qryResidents

Let me know if you need more info. Thank you for your time.
 
well, i would expect your data table to have a DateDue field, or perhaps a
BillingDate field, if there is a calculable time frame from the date billed.
from that hard date (or calculated due date), it would be easy enough to
write query criteria to pull records where the date is within 7 days before
today's date, or within 30 days before today's date, etc. something like

WHERE DateDue Between Date()-1 And Date()-8

but in looking at the posted fields in tblMain, i don't see a due date or
billing date, only a DatePaid, and a DaysLate. "number of days late" must be
a calculated value, and it must change every day until the bill is paid, in
order to be accurate. so your table design is not normalized, because you're
storing a calculated value rather than raw data. suggest you read up on
relational design principles, and re-think the table(s) design.

hth
 
ruralguy, tina,

Well, I restructered my tables along the lines of what Tina suggested and
modified the code provided by Ruralguy and it works absolutely great. Gives
me exactly what I wanted. I really want to thank both of you for your input
on this. Could not have made this work without your guidance and detailed
explanations. I love this place :-)

The Rider
 
you're very welcome :)


PaleRider said:
ruralguy, tina,

Well, I restructered my tables along the lines of what Tina suggested and
modified the code provided by Ruralguy and it works absolutely great. Gives
me exactly what I wanted. I really want to thank both of you for your input
on this. Could not have made this work without your guidance and detailed
explanations. I love this place :-)

The Rider
Click to expand...
 
Back
Top