date rent due problem

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()),DateValue(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.

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@TRASHntlworld.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()),DateValue(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]

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(ByVal dtTenancyStart As Date) As Date
DateRentDue = DateSerial(Year(Date), Month(Date),
Day(dtTenancyStart))
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]))

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.

re: date rent due problem

pietlinden@hotmail.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(ByVal dtTenancyStart As Date) As Date
> DateRentDue = DateSerial(Year(Date), Month(Date),
> Day(dtTenancyStart))
> 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()),DateSerial(Year(date),Month(date),Da y([start
date]),DateSerial(Year(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.

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

re: date rent due problem

pietlinden@hotmail.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()),CDate(DateSerial(Year(Date()),Month( Date())+1,Day([start
date]))),CDate(DateSerial(Year(Date()),Month(Date()),Da y([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.

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?

re: date rent due problem

pietlinden@hotmail.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.

re: date rent due problem

highway of diamonds wrote:[color=blue]
> pietlinden@hotmail.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()),Month(Date()),Day([start
date]))<Date(),DateAdd("m",1,DateSerial(Year(Date()),Mo nth(Date()),Day([start
date]))),DateSerial(Year(Date()),Month(Date()),Day([start date]))),Null)

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

Thanks all

R.