I have a an Acess 2000 db that tracks when clients enter our "program" and when they are released from it.
so I have Client_Tbl with the field Intake_date and Release_date.
I have a query that asks for a date range from DateEntry_frm the fields Being Startdate and Enddate.
Typically 1 month at a time is being querired.
The query goes to my report where is where the problem is.
The report takes the number of days one is in the program and calculates the month charge for that client. Based on a fixed dollar amount per day. So i am using a datediff. The datediff counts the days between inatke and release.
If i query dates from 04 01 04 to 04 30 04 and Bob has a start date of 04 15 04 the report is only going to calculate from the 15th as it should . My problem is that if Bob started on 03 15 04 I need to change the start date ( or a variable) to 04 01 04 since march has already been charged. Also if the release date in Null ( IE bob is still in the program hence has not been released. I need to calculate the last date of the search range.
I know this can be done in code but in truth I am unsure exactly how or where to put it. in hte report or query or whereever. Any help would be appreciated. Or could it be done in SQL ??
i read your problem several times, because english isn´t my native language, i understand your prob.
first query only a month (IE april -> 04), in your query_frm
after entering the month in the field, use the event on exit, or on update :
then do a sequential check on your table Client_Tbl
first check : month(date_intake) = april, if true then
check release_date if it is NULL then exit the function (still in program), or movenext.
if month(release_date ) = april is true then
calculate days with datediff.
Thanks for the assistance !
I was close actually but not quitei Iwas using
What is the aylyRate for?
is it supposed to be DaylyRate ? iether way Access told me it was not a function So I used
and that works fume !
I shall now try to answer some questions in the Newbie forum for good karme