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

    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
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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.

    izy
    currently using SS 2008R2

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

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

  4. #4
    Join Date
    Oct 2007
    Posts
    127
    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
  •