Data mismatch message returned by make table query

I have a make table query based on a number of fields from a variety 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.

Any help/suggestions gratefully received

Mike,

Post the SQL of your query. We cannot help without it.

-----Original Message-----
I have a make table query based on a number of fields
from a variety 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.

Any help/suggestions gratefully received
.

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
FROM (((Demographics LEFT JOIN AllInvestigations ON (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:]));


"Mike" wrote:

I have a make table query based on a number of fields from a variety 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.

Any help/suggestions gratefully received

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" wrote:

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
FROM (((Demographics LEFT JOIN AllInvestigations ON (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:]));


"Mike" wrote:

I have a make table query based on a number of fields from a variety 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.

Any help/suggestions gratefully received

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" wrote in message
...
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" wrote:

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
FROM (((Demographics LEFT JOIN AllInvestigations ON
(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:]));


"Mike" wrote:

I have a make table query based on a number of fields from a variety
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.

Any help/suggestions gratefully received

This is excellent, thanks for all your help

"Dale Fye" wrote:

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" wrote in message
...
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" wrote:

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
FROM (((Demographics LEFT JOIN AllInvestigations ON
(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:]));


"Mike" wrote:

I have a make table query based on a number of fields from a variety
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.

Any help/suggestions gratefully received