Help With Report Coding

[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.

 

[ruralguy via AccessMonster.com]

I would use the ComboBox to set a variable, say intLate, to the value you
want using 0 for ALL and then open your report with an If...Else...EndIf
sequence

If intlate > 0 Then
DoCmd.OpenReport "rptResidents", , , "[DaysLate] <= " & intLate
Else
DoCmd.OpenReport "rptResidents"
End If

 

[tina]

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

 

[PaleRider]

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

 

[tina]

you're very welcome

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