Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007

    Unanswered: Reports - adding a [start] and [end] date

    I have created a query as follows

    SELECT Attendance.UnitNo, dbo_Consultant.CONSULTANT_ID, IIf(IsNull(dbo_Consultant!CONSULTANT_ID),"Unknown Cons",dbo_Consultant!TITLE & ". " & (dbo_Consultant!INITIALS) & " " & dbo_Consultant!SURNAME) AS ConsName, Attendance.ReferringAgent, Attendance.AppointmentType, Attendance.DateofSession, Attendance.ReferralDate, Attendance.AttendanceID, Attendance.ReferralSource, Attendance.AppointmentComment, Attendance.PatientCategory, Attendance.AttendanceCode, Attendance.SpecialtyCode, Attendance.ClinicNumber, Attendance.TreatmentCode1, Attendance.TreatmentCode2, Attendance.TreatmentCode3, Attendance.TreatmentCode4, IIf([PatientCategory]="10",1,0) AS NHS, IIf([PatientCategory]="20",1,0) AS Priv, IIf(IsNull([PatientCategory]) Or ([PatientCategory] Not In ("10","20")),1,0) AS Other, IIf([AttendanceCode]="A",1,0) AS Attended, IIf([AttendanceCode]="D",1,0) AS DNA, IIf([AttendanceCode]="C" Or IsNull([AttendanceCode]),1,0) AS Canc, IIf([AppointmentType]="N",1,0) AS New, IIf([AppointmentType]="O",1,0) AS Follup
    FROM Attendance LEFT JOIN dbo_Consultant ON Attendance.ReferringAgent = dbo_Consultant.CONSULTANT_ID
    WHERE (((Attendance.DateofSession) Between [start date] And [end date]) AND ((Attendance.SpecialtyCode)="11006") AND ((Attendance.ClinicNumber)="X72" Or (Attendance.ClinicNumber)="X73" Or (Attendance.ClinicNumber)="X75" Or (Attendance.ClinicNumber)="X87" Or (Attendance.ClinicNumber)="X79" Or (Attendance.ClinicNumber)="X80" Or (Attendance.ClinicNumber)="X83" Or (Attendance.ClinicNumber)="ER6" Or (Attendance.ClinicNumber)="X84" Or (Attendance.ClinicNumber)="X85" Or (Attendance.ClinicNumber)="X86" Or (Attendance.ClinicNumber)="HJ0"));

    each time i run it, i need to enter to date values, the [start date] and [end date].

    i have created a report to using the query to show the results.

    in the report hearder i need to show the [start date] and [end date].

    i'm not sure why the following is not compiling correctly, it comes up with name error???

    ="Therapy Services - ROH Podiatry Contacts for Period : " & ([Queries]![AR_20080116_SLA_Podiatry_02]![start date],"dd/mm/yyyy") & " to " & ([Queries]![AR_20080116_SLA_Podiatry_02]![end date],"dd/mm/yyyy")

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    do yourself a big favour: completely forget that Access queries can prompt for a parameter. forever.

    prompted parameters are so ugly, so error-prone, and when the poor user gets an error thrown back (or worse, data that doesn't make sense) there is no trace of the dates entered at the prompt.

    make a form, get the user to enter the dates on the form, use the form dates in the query and the report and in debugging confused users.

    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2007
    Provided Answers: 10
    On a side note; are you aware of the IN keyword in SQL?

    WHERE  id = 1
    OR     id = 2
    OR     id = 3
    Could be simplified to
    WHERE  id IN (1, 2, 3)
    Home | Blog

  4. #4
    Join Date
    Oct 2007
    thanks... sorted now...

    thanks all

Posting Permissions

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