Results 1 to 6 of 6

Thread: change dates

  1. #1
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520

    Unanswered: change dates

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

    Thanks
    Darasen

  2. #2
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    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.
    else
    if month(release_date ) = april is true then
    calculate days with datediff.

    hope this will be at help
    Last edited by emiel; 10-19-04 at 05:34.

  3. #3
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    This can be done in the query or the report.

    query

    BillAmtaylyRate(iif(ReleaseDate<EndDate, ReleaseDate,EndDate)-iif(IntakeDate<StartDate,StartDate,IntakeDate))

    The same basic formula can be used in a textbox on the report. I'd put it in the query in case you find a need for it elswhere later.

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    that's kind of irritating the is a colon D

  5. #5
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520

    Thanks

    Thanks for the assistance !
    I was close actually but not quitei Iwas using
    Iif([Release_Date]<form[enddate],[releasedate]=[releasedate],[releasedate]=[enddate])

    What is the aylyRate for?
    is it supposed to be DaylyRate ? iether way Access told me it was not a function So I used
    endbill: (IIf([Release_Date]<forms!dateentry_frm!EndDate,[Release_Date],forms!dateentry_frm!EndDate))

    and that works fume !

    Thanks Agian.

    I shall now try to answer some questions in the Newbie forum for good karme
    Darasen

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Daily Rate (sorry it was early), the amount charged per day.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •