Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Angry Unanswered: Date field between two date fields

    Hi
    Not sure where I'm, going wrong here so hope you can help

    I have 2 tables
    Table 1 contains patient results
    Table 2 contains all inpatient records

    The common fields are ClientID and CaseID and there can be multiple results for each inpatient stay.
    A patient must have a health check when they are admitted to hospital and what I am trying to do is find whether a patient has a smoking result recorded during their inpatient stay to report this as a % i.e. No of results/No of inpatients during month

    I have created a qry to limit the result table to smoking status. I have created another qry to list all inpatients during the month by creating another field to determine the end reporting period i.e. if not discharged month end date or date of discharge if in month with the reporting start date of any patients who were inpatients at the beginning of the month.

    My next qry left joins the inpatient records to the smoking status, so I have all inpatients during the month with a smoking status/s recorded.

    What I then want to do is limit the smoking status date field so that it is between the admission date and the reporting period which I have put in the criteria line. When I know which results were added between the two dates I will join back to the full list of inpatients and create a new field to mark each record as 1 or 0. 1 having a status recorded.

    When I add the between on the criteria line I get an error message. I think it was 'too complex to evaluate'

    Hope I make sense and can anyone help, its driving me mad
    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    show us the SQL

    but if you prefer self-help, the 'too complex' error usually comes from A's query design grid when you join more than 2 tables and have forgotten to specify the join type between one or more pairs. right-click the join 'line' and see how it goes.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2006
    Posts
    4
    OK thanks I thought that it may have been something to do with the dates even though they all looked as though they were the same format

    I'll post the SQL later as I'm not in work at the moment, thanks for your response

  4. #4
    Join Date
    Dec 2006
    Posts
    4
    Here is the SQL to the query

    SELECT qryInpatOnDate_a.Directorate, qryInpatOnDate_a.SST, qryInpatOnDate_a.SST1, qryInpatOnDate_a.Description, qryInpatOnDate_a.ClientID, qryInpatOnDate_a.CaseID, qryInpatOnDate_a.AdmissionDate, qryInpatOnDate_a.DischargeDate, qryInpatOnDate_a.ReportingPeriod, qryInpatOnDate_a.WardOnDate, qryResults_AllSmokingStatus.ResultDate
    FROM qryInpatOnDate_a LEFT JOIN qryResults_AllSmokingStatus ON (qryInpatOnDate_a.CaseID = qryResults_AllSmokingStatus.CaseID) AND (qryInpatOnDate_a.ClientID = qryResults_AllSmokingStatus.ClientID)
    WHERE (((qryResults_AllSmokingStatus.ResultDate) Between [admissiondate] And [reportingperiod]))
    ORDER BY qryInpatOnDate_a.Directorate, qryInpatOnDate_a.SST1;

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    first look, i guess you were right: it is the WHERE kicking up a fuss.
    WHERE (((qryResults_AllSmokingStatus.ResultDate) Between [admissiondate] And [reportingperiod]))
    is 'too complex' because there is an admissiondate and a reportingperiod in your SELECTed fields - A's brain gets addled at this stage.

    where do admissiondate and reportingperiod (for WHERE purposes) come from
    ...a form?
    ...so try (assuming we are still in A's query-design thingie, SQL-view)

    WHERE (((qryResults_AllSmokingStatus.ResultDate) Between Forms!nameOfYourForm![admissiondate] And Forms!nameOfYourForm![reportingperiod]))

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2006
    Posts
    4

    Wink

    Thanks again

    Although on the face the of it all dates appear in short date format, I changed all the formats to short date, then mktbl from the qry and set the criteria from the table rather than the qry.

    It has worked, although I was hoping that I wouldn't have to go to that trouble

    Cheers Izy, I am a bit rubbish with Access but like to learn

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    dont get lost in A-magic.
    dates can be dsplayed in a bewildering array of formats, but unless you have gone completely mad and have actually stored them as text-fields, they are stored as access datetimes.

    these access datetime guys are completely agnostic as far as formatting goes. they are (Access-speak) doubles with the number left of the decimal representing days before/since day-zero and the number after the decimal representing some fraction of 24 hours (e.g. 1.25 is 6AM on day-one).

    izy
    currently using SS 2008R2

Posting Permissions

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