Next 7 days from calculated date value (1 Viewer)

[DenisCooper]

Hi,

in a query i have setup, i work out when a tenant is next due to pay their rent.

Looks like this

Tenant Last Payment Date Payment Terms Next Payment Date
User1 01/07/13 Monthly 01/08/13
User2 01/07/13 Weekly 07/07/13

The next payment date is calculated using an IF statement and DateAdd in the query, so if it is weekly it adds 7 days, if it is monthly it adds 1 month.

What i am trying to do is write sub query of this one which shows which tenants are due to pay within the next 7 days.

But the access query seems to ignore the criteria I'm putting in....in the next payment date criteria i have tried specifying >Now() + 7 or >Date() +7 but neither seem to work, it just shows up every record...

any ideas?

 

[ypma]

Try <Date()-"7" this would only show records that are over due by 7 days

Hope it works

 

[StarGrabber]

Hi Denis,

if you want the tenants which are due within the next 7 days, you should put it this way:

<Now()+7 or <Date()+7

The way you put it, the query shows you those which are due the week after next.

 

[gemma-the-husky]

in the interest of accuracy

a) you probably want date() rather than now()
b) you probably want +6 rather than +7

but neither of which should have a great problem as it stands.

i agree with stargrabber. you probably want <

ie the due date is less than a week after today. (ie it wil be due on oer before that date.)

 

[DenisCooper]

hi,

tried all variations and it just seems to ignore the criteria and show everything...SQL query is shown here


SELECT qryMaxPaymentDate.cboTenant, qryMaxPaymentDate.MaxOfDateDue, qryMaxPaymentDate.cboPaymentTerms, IIf([qryMaxPaymentDate.cboPaymentTerms]=2,DateAdd("ww",1,[MaxOfDateDue]),IIf([qryMaxPaymentDate.cboPaymentTerms]=3,DateAdd("ww",2,[MaxOfDateDue]),IIf([qryMaxPaymentDate.cboPaymentTerms]=4,DateAdd("ww",4,[MaxOfDateDue]),IIf([qryMaxPaymentDate.cboPaymentTerms]=1,DateAdd("m",1,[MaxOfDateDue]),"n/a")))) AS calcNextPayDueDate, tblLease.PaymentAmount
FROM qryMaxPaymentDate INNER JOIN tblLease ON qryMaxPaymentDate.cboTenant = tblLease.cboTenant
WHERE (((IIf([qryMaxPaymentDate.cboPaymentTerms]=2,DateAdd("ww",1,[MaxOfDateDue]),IIf([qryMaxPaymentDate.cboPaymentTerms]=3,DateAdd("ww",2,[MaxOfDateDue]),IIf([qryMaxPaymentDate.cboPaymentTerms]=4,DateAdd("ww",4,[MaxOfDateDue]),IIf([qryMaxPaymentDate.cboPaymentTerms]=1,DateAdd("m",1,[MaxOfDateDue]),"n/a")))))<Date()+6));

i've tried doing a subquery of this query and it still ignores the criteria...any help appreciated.

 

[DenisCooper]

intrestingly though, if i just put date() then it works ok

 

[DenisCooper]

i think i've got it working by using this as the filter

<Date()+6 AND >Date()

thanks for help