Frustrating Data Type Mismatch error

[Ivyleaf]

Hi all,

I am having great trouble with what I thought would be a simple query
and hopefully there is someone out there that may be able to shed a
little light on my problem.

I have (among many other things) a table in my database which lists
staff details (names, classifications, start dates etc). What I am
trying to do is extract a list of staff who are current (no Cease
Date) or who are ceased, but ceased in this financial year. My SQL is
as follows:

SELECT [Full CAS Listing].Ident, [Full CAS Listing].[Full Name], [Full
CAS Listing].DesGS, [Full CAS Listing].[EFT/Ratio]
FROM [Full CAS Listing]
WHERE DateValue(nz([Cease
Date],#1/1/1980#))>DateSerial(Year(DateAdd("m",-6,Now())),6,30);

If I can get this bit working, I can add the rest that will give me
what I want. I have also tried all incarnations of the criteria
expression that I can think of, but to no avail.

Many thanks to all!

 

[Allen Browne]

Try something like this:

SELECT [Full CAS Listing].Ident,
[Full CAS Listing].[Full Name],
[Full CAS Listing].DesGS,
[Full CAS Listing].[EFT/Ratio]
FROM [Full CAS Listing]
WHERE (([Cease Date] Is Null) OR ([Cease Date] >=
DateSerial(Year(DateAdd("m", -6 Date())),7,1)));

JET has problems understanding the output of Nz(). Even with DateSerial(),
your expression probably needed CVDate() wrapped around it.

But the suggestion above will be more efficient: JET will be able to use any
index you have on the [Cease Date] field, since the expression is crafted so
that the field is not wrapped in a function call.

This all assumes that if you open the table in design view, your [Cease
Date] field is a Date/Time field (not a Text field.)