Micheal,
1. In the first query, you treat [Priority] as though it were a number, in
the second you compare it to a string, which is it (number or string). My
guess is that this is your problem.
2. Instead of using all of the NZ() functions, why not use ISNull()
function to determine whether a column has a value. It appears that the
first column is supposed to identify only those records where any one of
these fields has data in it, because if any of those fields has data, the
test against zero will fail (0), and because all the tests are AND'd
together, the result will also be zero(0), which is the criteria you are
testing against. If that is the case then why not make it easier to read by
writing it as:
NOT ISNULL([Priority])
OR NOT ISNULL([Date decided to treat (Surgery)])
OR NOT ISNULL([Date decided to treat (Spec palliative care)])
OR NOT ISNULL([Surgery admission date])
OR NOT ISNULL([Spec palliative care start date])
Criteria: True
3. The second column is easier to modify.
Second Query:
ISNULL([Priority]) OR
[Priority] = 1 OR
([Priority] = 2 AND NOT ISNULL([Date decided to treat (Surgery)])) OR
([Priority] = 2 AND NOT ISNULL([Date decided to treat (Spec palliative
care)]))
Criteria = -1
4. My guess is that these criteria could probably be rewritten into some
simpler form if I knew what the ultimate goal of the query is.
HTH
Dale
Michael said:
Ignore the previous posting - by deleting columns from the query and
rerunning it, I only get the problem if the last two columns are included,
both not shown (ie no tick). The SQL for these two columns are
(Nz([Priority],0)=0 And Nz([Date decided to treat (Surgery)],0)=0 And
Nz([Date decided to treat (Spec palliative care)],0)=0 And Nz([Surgery
admission date],0)=0 And Nz([Spec palliative care start date],0)=0).
Criteria 0
(([Priority]="01" Or ([Priority]="02" And (Nz([Date decided to treat
(Surgery)],0)>0 Or (Nz([Date decided to treat (Spec palliative
care)],0)>0)))) Or Nz([Priority],0)=0). Criteria -1
Does this help ?
As you may gather I'm taking over an existing Access application, with no
documentation or contact with the previous developer.
Michael said:
it looks better in design view
SELECT Demographics.[NHS Number], Referral.[Source of OP ref],
Referral.[Cancer ref decision date], DatePart("yyyy",[Referral]![Cancer ref
decision date]) & Format(DatePart("m",[Referral]![Cancer ref decision
date]),"00") & Format(DatePart("d",[Referral]![Cancer ref decision
date]),"00") AS [JCancer ref decision date], Referral.[Date referral
recieved], DatePart("yyyy",[Referral]![Date referral recieved]) &
Format(DatePart("m",[Referral]![Date referral recieved]),"00") &
Format(DatePart("d",[Referral]![Date referral recieved]),"00") AS [JDate
referral recieved], Referral.Priority, Referral.[Urgent suspected ca ref
type], Referral.[Hospital first seen at], Referral.[Waiting time adj],
Referral.[Waiting time adj reason (1st seen)], Referral.[Delay reason
comment], Referral.[Delay reason ref to 1st seen], Referral.[Cancer
specialist ref date], DatePart("yyyy",[Referral]![Cancer specialist ref
date]) & Format(DatePart("m",[Referral]![Cancer specialist ref date]),"00")
& Format(DatePart("d",[Referral]![Cancer specialist ref date]),"00") AS
[JCancer specialist ref date], Referral.[Hospital referring to specialist],
Referral.[First seen by specialist date], DatePart("yyyy",[Referral]![First
seen by specialist date]) & Format(DatePart("m",[Referral]![First seen by
specialist date]),"00") & Format(DatePart("d",[Referral]![First seen by
specialist date]),"00") AS [JFirst seen by specialist date], Referral.[First
cancer specialist hospital], AllInvestigations.[Date first diagnostic test],
DatePart("yyyy",[AllInvestigations]![Date first diagnostic test]) &
Format(DatePart("m",[AllInvestigations]![Date first diagnostic test]),"00")
& Format(DatePart("d",[AllInvestigations]![Date first diagnostic
test]),"00") AS [JDate first diagnostic test], AllInvestigations.[Hospital
of first test], Managementplan.[Case discussed at MDT], Managementplan.[Date
first discussed at MDT], DatePart("yyyy",[Managementplan]![Date first
discussed at MDT]) & Format(DatePart("m",[Managementplan]![Date first
discussed at MDT]),"00") & Format(DatePart("d",[Managementplan]![Date first
discussed at MDT]),"00") AS [JDate first discussed at MDT],
Demographics.[Cancer status], Demographics.[Primary diagnosis],
Referral.[Date first seen], DatePart("yyyy",[Referral]![Date first seen]) &
Format(DatePart("m",[Referral]![Date first seen]),"00") &
Format(DatePart("d",[Referral]![Date first seen]),"00") AS [JDate first
seen], Demographics.[Tumour laterality], AllSurgery.[Date decided to treat
(Surgery)], DatePart("yyyy",[AllSurgery]![Date decided to treat (Surgery)])
& Format(DatePart("m",[AllSurgery]![Date decided to treat (Surgery)]),"00")
& Format(DatePart("d",[AllSurgery]![Date decided to treat (Surgery)]),"00")
AS [JDate decided to treat (Surgery)], "" AS Expr1, "" AS Expr2, "" AS
Expr3, AllSurgery.[Date decided to treat (Spec palliative care)],
DatePart("yyyy",[AllSurgery]![Date decided to treat (Spec palliative care)])
& Format(DatePart("m",[AllSurgery]![Date decided to treat (Spec palliative
care)]),"00") & Format(DatePart("d",[AllSurgery]![Date decided to treat
(Spec palliative care)]),"00") AS [JDate decided to treat (Spec palliative
care)], "" AS Expr4, AllSurgery.[Dec to treat hospital], AllSurgery.[Wait
time adjustment dec to treat], AllSurgery.[Waiting time adj reason (dec to
trt)], Managementplan.[Planned first definitive treatment],
AllSurgery.[Surgery admission date], DatePart("yyyy",[AllSurgery]![Surgery
admission date]) & Format(DatePart("m",[AllSurgery]![Surgery admission
date]),"00") & Format(DatePart("d",[AllSurgery]![Surgery admission
date]),"00") AS [JSurgery admission date], "" AS Expr6, "" AS Expr7, "" AS
Expr8, AllSurgery.[Spec palliative care start date],
DatePart("yyyy",[AllSurgery]![Spec palliative care start date]) &
Format(DatePart("m",[AllSurgery]![Spec palliative care start date]),"00") &
Format(DatePart("d",[AllSurgery]![Spec palliative care start date]),"00") AS
[JSpec palliative care start date], "" AS Expr9, AllSurgery.[First treating
hospital], AllSurgery.[Wait time adjustment treatment], AllSurgery.[Waiting
time adj reason (treatment)], AllSurgery.[Delay comment decision to
treatment], AllSurgery.[Delay comment referral to treatment],
AllSurgery.[Delay reason decision to treatment], AllSurgery.[Delay reason
referral to treatment] INTO CWT(Demographics.Episode = AllInvestigations.Episode) AND
(Demographics.[Hospital Number] = AllInvestigations.[Hospital Number])) LEFT
JOIN AllSurgery ON (Demographics.Episode = AllSurgery.Episode) AND
(Demographics.[Hospital Number] = AllSurgery.[Hospital number])) LEFT JOIN
Managementplan ON (Demographics.Episode = Managementplan.Episode) AND
(Demographics.[Hospital Number] = Managementplan.[Hospital number])) LEFT
JOIN Referral ON (Demographics.Episode = Referral.Episode) AND
(Demographics.[Hospital Number] = Referral.[Hospital number])
WHERE (((Demographics.[NHS Number])<>"??????????" And (Demographics.[NHS
Number]) Is Not Null) AND ((Referral.[Date first seen]) Between [Start date
for CWT export:] And [End date for CWT export:]) AND (((Nz([Priority],0)=0
And Nz([Date decided to treat (Surgery)],0)=0 And Nz([Date decided to treat
(Spec palliative care)],0)=0 And Nz([Surgery admission date],0)=0 And
Nz([Spec palliative care start date],0)=0))=0) AND (((([Priority]="01" Or
([Priority]="02" And (Nz([Date decided to treat (Surgery)],0)>0 Or (Nz([Date
decided to treat (Spec palliative care)],0)>0)))) Or
Nz([Priority],0)=0))=-1)) OR (((AllSurgery.[Surgery admission date]) Between
[Start date for CWT export:] And [End date for CWT export:])) OR
(((AllSurgery.[Spec palliative care start date]) Between [Start date for CWT
export:] And [End date for CWT export:]));of linked tables. The query prompts me for a start date and end date ( used
to select appropriate records ). It returns a data mismatch error message.
I've checked the data types, and they all match up - the only difference is
in the format of the date/time fields - ie the 'source' field (field in
query) may be yyyymmdd, and the destination field (field in table) format
isn't set.
