Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Question Unanswered: HELP: Week commencing calculation including blank weeks?

    Hi wonder if you can help me?
    i have a field in access which states the week commencing for a persons appointment using another field (TCI):

    W/C TCI date: IIf([TCI] Is Null,Null,([TCI]-Weekday([TCI])+2))

    but I need the week commencing even if there are no appointments that week?

    is this possible?

    Thanks for your help!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Sorry, I at least don't fully understand you're problem. Could you give us a little information on the tables/fields involved? An example of what you're after, and what you're actually getting and the gap between the two might also go a long ways to helping us help you out.

    Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Jan 2010
    Posts
    2
    sorry i am new to this..
    thanks for the reply

    ok let me try to explain... I have set up some data around patients who have future appointments and how long they are currently waiting. So i created the above field to place them into a week so we know how many patients are expected that week. But i still need to show a week where there are no patients waiting so i have consistant weeks being calculated. If there are no patients waiting say Week Commencing 12th April then its not shown.??

    this is my SQL to the query i am running if that helps? i put in bold where i am stuck...
    SELECT IIf([OP] In ("W401","W411","421"),"Knee",IIf([OP] In ("W371","W381","W391"),"Hip","Other")) AS HipOrKnee, Int([RTT]/7) AS RTTwks, IIf([RTTwks]<3,"0-3w",IIf([RTTwks]<6,"3-6w",IIf([RTTwks]<9,"6-9w",IIf([RTTwks]<12,"9-12",IIf([RTTwks]<=15,"12-15",IIf([RTTwks]>15,"over15w",False)))))) AS Bands, DatePart("ww",[TCI]) AS [Week no], IIf([TCI] Is Null,Null,([TCI]-Weekday([TCI])+2)) AS [W/C TCI date], KG_FinalEnglishPTLQry.Cons, KG_FinalEnglishPTLQry.PathwayLocator, KG_FinalEnglishPTLQry.[W Length], KG_FinalEnglishPTLQry.[W Date], KG_FinalEnglishPTLQry.TCI, KG_FinalEnglishPTLQry.DateAddedToWaitlist, KG_FinalEnglishPTLQry.RTT, KG_FinalEnglishPTLQry.CountOfField14, KG_FinalEnglishPTLQry.[Number of Outpatient Cancellations or DNA's], KG_FinalEnglishPTLQry.PCG, KG_FinalEnglishPTLQry.UnitNo, KG_FinalEnglishPTLQry.RefDate, KG_FinalEnglishPTLQry.MaxOfAttendanceDate, KG_FinalEnglishPTLQry.MaxOfLastDNAOrPatientCancell edDate, KG_FinalEnglishPTLQry.RefSource, KG_FinalEnglishPTLQry.RefSourceName, KG_FinalEnglishPTLQry.RefSourceInitiator, KG_FinalEnglishPTLQry.Specialty, KG_FinalEnglishPTLQry.SpecialtyCode, KG_FinalEnglishPTLQry.PatientName, KG_FinalEnglishPTLQry.Status, KG_FinalEnglishPTLQry.OP, KG_FinalEnglishPTLQry.[OP Name], KG_FinalEnglishPTLQry.RefSubscriptID
    FROM KG_FinalEnglishPTLQry
    WHERE (((KG_FinalEnglishPTLQry.Cons)="BANJ" Or (KG_FinalEnglishPTLQry.Cons)="BANR" Or (KG_FinalEnglishPTLQry.Cons)="RAOJ"));

    Thanks for your help !

Posting Permissions

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