date rent due problem

[highway of diamonds]

Hi,

Been a while since I've used access. What I got is a table of
properties to rent whch has a Tenancy start date. The rent is due on
the same day of the month as the tenancy start date. To make it easy for
staff I'm trying to show in a rent due cololumn the actual day the rent
is due this month. This I can do using this:

Rent Due: IIf([start date],IIf(Day([start
date])<Day(Date()),D ateValue(Day([start date]) & "/" &
Month(Date())+1 ),DateValue(Day ([start date]) & "/" & Month(Date()))) ,"n/a")

Works ok, but when I use a query on it to find the rents due in say the
next seven days using:

Between Date() And Date()+"7"

It works ok until it goes past the end of the month, it then returns all
due dates.

I have the same problem if I use selected dates:

Between DateValue([start date?]) And DateValue([end date?])

I seem to be going round in circles so have reverted to my old friend
usenet seeking the answer.

Can anyone help. I realise I may be doing it the wrong way totally so if
you could enlighten me one way or the other it would be appreciated.

TIA

R.

[Arno R]

Re: date rent due problem

==>> Between Date() And Date() + 7 (don't use the quote's here)

--
Hope this helps
Arno R


"highway of diamonds" <thered@TRASHnt lworld.com> schreef in bericht news:r6X1e.516$ _I5.63@newsfe5-win.ntli.net...[color=blue]
> Hi,
>
> Been a while since I've used access. What I got is a table of
> properties to rent whch has a Tenancy start date. The rent is due on
> the same day of the month as the tenancy start date. To make it easy for
> staff I'm trying to show in a rent due cololumn the actual day the rent
> is due this month. This I can do using this:
>
> Rent Due: IIf([start date],IIf(Day([start
> date])<Day(Date()),D ateValue(Day([start date]) & "/" &
> Month(Date())+1 ),DateValue(Day ([start date]) & "/" & Month(Date()))) ,"n/a")
>
> Works ok, but when I use a query on it to find the rents due in say the
> next seven days using:
>
> Between Date() And Date()+"7"
>
> It works ok until it goes past the end of the month, it then returns all
> due dates.
>
> I have the same problem if I use selected dates:
>
> Between DateValue([start date?]) And DateValue([end date?])
>
> I seem to be going round in circles so have reverted to my old friend
> usenet seeking the answer.
>
> Can anyone help. I realise I may be doing it the wrong way totally so if
> you could enlighten me one way or the other it would be appreciated.
>
> TIA
>
> R.[/color]

[pietlinden@hotmail.com]

Re: date rent due problem

So you grab the tenancy start date. and then you figure out what day of
the current month the rent will be due:

Public Function DateRentDue(ByV al dtTenancyStart As Date) As Date
DateRentDue = DateSerial(Year (Date), Month(Date),
Day(dtTenancySt art))
End Function

example run:
?DateRentDue(#5/1/03#)
3/1/2005

?daterentdue(#4/17/01#)
3/17/2005

Of course, you can do all this in a query (it's just a bit easier to
test in VBA). Just use this:
DateSerial(Year (Date), Month(Date), Day([tblLease].[TenancyStart]))

[highway of diamonds]

Re: date rent due problem

Arno R wrote:[color=blue]
> ==>> Between Date() And Date() + 7 (don't use the quote's here)
>[/color]
Thing is Access puts the quotes in - not me.

R.

[highway of diamonds]

Re: date rent due problem

pietlinden@hotm ail.com wrote:[color=blue]
> So you grab the tenancy start date. and then you figure out what day of
> the current month the rent will be due:
>
> Public Function DateRentDue(ByV al dtTenancyStart As Date) As Date
> DateRentDue = DateSerial(Year (Date), Month(Date),
> Day(dtTenancySt art))
> End Function
>
> example run:
> ?DateRentDue(#5/1/03#)
> 3/1/2005
>
> ?daterentdue(#4/17/01#)
> 3/17/2005
>
> Of course, you can do all this in a query (it's just a bit easier to
> test in VBA). Just use this:
> DateSerial(Year (Date), Month(Date), Day([tblLease].[TenancyStart]))
>[/color]
So to go back to my original query:

Rent Due: IIf([start date],IIf(Day([start
date])<Day(Date()),D ateSerial(Year( date),Month(dat e),Day([start
date]),DateSerial(Ye ar(date),Month( date)+ 1,Day([start date]),"n/a")

Should do it then? May I ask what, in simple terms, Dateserial actually
does? Save me time, Thanks.

My VBA is worse than my queries!

R.

[pietlinden@hotmail.com]

Re: date rent due problem

what data types is your "start date"? If it's text, then that *might*
explain why the QBE is misinterpreting what you intend. If it's text,
try replacing [start date] with cdate([start date]) and see if that
fixes it.

no need for VBA at all in this...

[highway of diamonds]

Re: date rent due problem

pietlinden@hotm ail.com wrote:[color=blue]
> what data types is your "start date"? If it's text, then that *might*
> explain why the QBE is misinterpreting what you intend. If it's text,
> try replacing [start date] with cdate([start date]) and see if that
> fixes it.
>
> no need for VBA at all in this...
>[/color]
I need a rest from this... no I've just tried using Cdate and dateserial.

What I have noticed if I sort into order it only sorts by the day part
of date, i.e. 31/03/05 comes after 01/04/05 and 02/04/05 etc.

I also changed my start date to midlenght date format -this displays
28-Mar-05 but the queried dates show as 28/03/2005 so it looks like
they're strings an not date formats - even after using cdate.

My last attempt was this:

Rent Due: IIf([start date],IIf(Day([start
date])<Day(Date()),C Date(DateSerial (Year(Date()),M onth(Date())+1, Day([start
date]))),CDate(DateS erial(Year(Date ()),Month(Date( )),Day([start
date])))),"n/a")

I need to leave it alone right now, I been at it too long. Will try any
more ideas that are posted tomorrow, thanks for your help. May need to
try another approach to the problem

R.

[pietlinden@hotmail.com]

Re: date rent due problem

looks like your regional settings are set to a non-US format. What
happens when you sort the dates? They should sort in date order. Do
they?

[highway of diamonds]

Re: date rent due problem

pietlinden@hotm ail.com wrote:[color=blue]
> looks like your regional settings are set to a non-US format. What
> happens when you sort the dates? They should sort in date order. Do
> they?
>[/color]
Yes, regional settings are uk.

When I sort they do not seem to sort sort as date values, 01/04/2005
will come before 02/04/2005 but 31/03/2005 will come after both. So it
looks like thare are sorting as strings?

R.

[highway of diamonds]

Re: date rent due problem

highway of diamonds wrote:[color=blue]
> pietlinden@hotm ail.com wrote:[color=green]
>> looks like your regional settings are set to a non-US format. What
>> happens when you sort the dates? They should sort in date order. Do
>> they?
>>[/color]
> Yes, regional settings are uk.
>
> When I sort they do not seem to sort sort as date values, 01/04/2005
> will come before 02/04/2005 but 31/03/2005 will come after both. So it
> looks like thare are sorting as strings?
>
> R.[/color]
Sorted it using:

Rent Due: IIf([start
date],IIf(DateSerial (Year(Date()),M onth(Date()),Da y([start
date]))<Date(),DateA dd("m",1,DateSe rial(Year(Date( )),Month(Date() ),Day([start
date]))),DateSerial( Year(Date()),Mo nth(Date()),Day ([start date]))),Null)

Also used Is Not Null as criteria in [start date]

Thanks all

R.