Problems USIng Various Selection Criteria

[simeybt]

All,
I have been pulling my hair out with this query all day. What I want to do is select various records for an absence table. The criteria I am using are as follows. IF the end_date="" or the end > report_date AND AbsenceCode="SL". The first selection criteria work ok and it selects all the record that either has a null end_date or the end_date is greater than the report date. But when I add in the criteria for the absence type the query just seems to ignore the Code. Here is the SQL for my Query

SELECT tblStaff_Absence.EIN, tblStaff_Absence.Absence_Code, tblStaff_Details.OUC, tblStaff_Details.Employer, tblStaff_Absence.Absence_Code, tblStaff_Absence.Absence_End, tblStaff_Absence.Absence_Start
FROM tblStaff_Details INNER JOIN tblStaff_Absence ON tblStaff_Details.EIN = tblStaff_Absence.EIN
WHERE (((tblStaff_Absence.Absence_Code)="al") AND ((IsNull([absence_end]))=True)) OR (((tblStaff_Absence.Absence_End)>[end_date]));

all suggestions welcome

Simon

 

[SiJP]

Ignores it? Do you mean it returns all records or no records? I assume by Asbsence type you mean the Absence_Code field?



------------------------
Hit any User to continue

 

[SiJP]

never mind... try this:

SELECT tblStaff_Absence.EIN, tblStaff_Absence.Absence_Code, tblStaff_Details.OUC AS Expr1, tblStaff_Details.Employer, tblStaff_Absence.Absence_Code, tblStaff_Absence.Absence_End, tblStaff_Absence.Absence_Start
FROM tblStaff_Details INNER JOIN tblStaff_Absence ON tblStaff_Details.EIN = tblStaff_Absence.EIN
WHERE (((tblStaff_Absence.Absence_Code)="al") AND ((tblStaff_Absence.Absence_End)>[end_date]) AND ((IsNull([absence_end]))=True));

------------------------
Hit any User to continue

 

[simeybt]

yip sorry about that i did mean the absence code. THe query returns all the records that match IsNull[absence_end]=True OR tblStaff_Absence.Absence_End)>[end_date]. it does not use the Absence_Code crteria.

 

[SiJP]

try the revised sql above

------------------------
Hit any User to continue

 

[simeybt]

nah,
that pulled back an empty recordset

 

[SiJP]

pants..

ok, I'm off home just now, but I'll look at in a hour or so (after trawling through rush hour..)

Can you give me a sample of the data (just one row) for each table?

Cheers

------------------------
Hit any User to continue

 

[SiJP]

Couple of things

1) Absence_Code is selected twice..

2) If you are trying to select the Absence_end if its either null, or against an inputted end date, try using the following..

SELECT tblStaff_Absence.EIN, tblStaff_Details.OUC, tblStaff_Details.Employer, tblStaff_Absence.Absence_Code, tblStaff_Absence.Absence_End, tblStaff_Absence.Absence_Start
FROM tblStaff_Details INNER JOIN tblStaff_Absence ON tblStaff_Details.EIN = tblStaff_Absence.EIN
WHERE (((tblStaff_Absence.Absence_Code)="al") AND ((tblStaff_Absence.Absence_End) Is Null Or (tblStaff_Absence.Absence_End)>[End Date]));






------------------------
Hit any User to continue

 

[simeybt]

I think I cracked it. The problem seems to be the way in which access inserts the brackets. I moved the placement of the brackets so the date criteria selection was grouped inside a set of brackets. Like this

WHERE ((tblStaff_Absence.Absence_Code)="SL") AND (((IsNull([absence_end]))=True) OR (((tblStaff_Absence.Absence_End)>[end_date])));

This has a weird effect when you go into design view that it adds the Absence_Code criteria i.e “SL” into the criteria and into the "or" row as well!!.

 

[SiJP]

Yep.. what you've done above is another way of doing what I had in the bit above !!

It just uses the criteria rows as the 'or' bits.. I've kept everythin gon one criteria line by using:

WHERE (((tblStaff_Absence.Absence_Code)="al") AND ((tblStaff_Absence.Absence_End) Is Null Or (tblStaff_Absence.Absence_End)>[End Date]));

- rather than -
WHERE ((tblStaff_Absence.Absence_Code)="SL") AND (((IsNull([absence_end]))=True) OR (((tblStaff_Absence.Absence_End)>[end_date])));

..which as you've said, gives it a 'wierd' effect! Its not really wierd, just another way to do things

------------------------
Hit any User to continue

 

[simeybt]

I see know, I put you code into the query builder to see how it ran, Perfect same as mine. I think it a bit neater the way in which you done it. But this was really the beginning of my problem I was using this query to understand why my report was now pulling back incorrect information. I know this is changing the direction of this post but I want to be able to pass this criteria as part of the WHERE statement to a report from code. Is it just a matter of making sure the WHERE string is formatted in the same way as it was in the query. I currently don’t have any brackets in the string.

Simon