Query brings back record when it shouldn't

KevCB226 · #1 (**permalink**) 09-13-06, 04:51

Hi,

I'm getting an unusual issue with a query I have made.
The query is meant to show records of schools that haven't had any visits from our liaison officers.

To do this I linked both the schools details table and the visits table. I then checked to see if the date of the visit is not between the previous academic year, and display the schools address details.

The query works on all but one record. When I view the visit table the record is in there and between the date ranges. But the query seems to bring back the record as having no date at all.

This is the query I am using:

Code:

SELECT dbo_tblSchoolDetails.SchoolName, dbo_tblSchoolDetails.SchoolED, dbo_tblSchoolDetails.Addr1, dbo_tblSchoolDetails.Addr2, dbo_tblSchoolDetails.Town, dbo_tblSchoolDetails.County, dbo_tblSchoolDetails.PostCode, dbo_tblSchoolDetails.PostCode2, dbo_tblVisits.EventDate, IIf([EventDate] Not Between "01/09/" & CStr(IIf(Month(Date())<9,Year(Date())-1,Year(Date()))) And "31/08/" & CStr(Year(Date())),"N","Y") AS Expr1
FROM dbo_tblSchoolDetails LEFT JOIN dbo_tblVisits ON dbo_tblSchoolDetails.ID = dbo_tblVisits.ID
WHERE (((IIf([EventDate] Not Between "01/09/" & CStr(IIf(Month(Date())<9,Year(Date())-1,Year(Date()))) And "31/08/" & CStr(Year(Date())),"N","Y"))="Y"));

I'm not sure why it is doing this, but the first thing I could think of, is that it maybe something to do with the linked tables from SQL Server 2000, but the date is also in the table on the server, unless it is something else.

Is the criteria on my query correct?

Thanks

EDIT: Sorted now, I was making the stupid mistake of linking the wrong IDs.

Internet Services

Web Development

Digital Marketing

Consumer Tech