Adding an additional WHERE clause

[Pells]

I am using the following code which works fine in a query:

SELECT PEPERSON.PERSONNEL_NO, PEPERSON.SURNAME, PEPERSON.CENTRE, [Input Week Number] AS WEEK_NO, PEPERSON.DATE_LEFT
FROM PEPERSON


WHERE (((PEPERSON.CENTRE)>"0") AND ((PEPERSON.DATE_LEFT) Is Null) AND ((Exists (SELECT JCBATCH.PERSONNEL_NO FROM JCBATCH
WHERE JCBATCH.COMPANY = PEPERSON.COMPANY AND JCBATCH.WEEK_NO = [Input Week Number] AND JCBATCH.PERSONNEL_NO = PEPERSON.PERSONNEL_NO AND JCBATCH.ORIG_ADJUST = "Original" AND JCBATCH.UPDATED IN ("Held", "Ready", "Updated")))=False) AND ((PEPERSON.USE_IN_TB)=1) AND ((PEPERSON.COMPANY)="PFC1"))
ORDER BY PEPERSON.PERSONNEL_NO;

I need to be able to insert the following additional WHERE statement into my code (note that I am already using PEPERSON.DATE_LEFT to filter on all values that are blank):

OR PEPERSON.DATE_LEFT > MAX(JCBATCH.WEEK_END_DATE)

I have tried to put the above into my code, but so far failing miserably. Does anyone know where I can successfully insert the above statement t get it to work successfully?

Any suggestion would be greatly received - many thanks.

Many thanks

[ajetrumpet]

try changing this:

MAX(JCBATCH.WEEK_END_DATE)

to this:

DMAX(JCBATCH.WEEK_END_DATE)

[Pells]

try changing this:

MAX(JCBATCH.WEEK_END_DATE)

to this:

DMAX(JCBATCH.WEEK_END_DATE)

Many thanks for this.

Do you know where I put this into my code?

[ajetrumpet]

as I said, SUBSTITUTE it in for the MAX() function you have now. make sense?

[Pells]

as I said, SUBSTITUTE it in for the MAX() function you have now. make sense?

Apologies, but I dont currently have this additional WHERE statement inserted into my code yet and my original question was where do I insert (apologies, but I am a novice and learning all the time with this).

Many thanks.

[ajetrumpet]

SELECT PEPERSON.PERSONNEL_NO, PEPERSON.SURNAME, PEPERSON.CENTRE, [Input Week Number] AS WEEK_NO, PEPERSON.DATE_LEFT
FROM PEPERSON
WHERE (((PEPERSON.CENTRE)>"0") AND ((PEPERSON.DATE_LEFT) Is Null) AND ((Exists (SELECT JCBATCH.PERSONNEL_NO FROM JCBATCH
WHERE JCBATCH.COMPANY = PEPERSON.COMPANY AND JCBATCH.WEEK_NO = [Input Week Number] AND JCBATCH.PERSONNEL_NO = PEPERSON.PERSONNEL_NO AND JCBATCH.ORIG_ADJUST = "Original" AND JCBATCH.UPDATED IN ("Held", "Ready", "Updated")))=False) AND ((PEPERSON.USE_IN_TB)=1) AND ((PEPERSON.COMPANY)="PFC1"))

OR PEPERSON.DATE_LEFT > DMAX(JCBATCH.WEEK_END_DATE)

ORDER BY PEPERSON.PERSONNEL_NO;

[Pells]

SELECT PEPERSON.PERSONNEL_NO, PEPERSON.SURNAME, PEPERSON.CENTRE, [Input Week Number] AS WEEK_NO, PEPERSON.DATE_LEFT
FROM PEPERSON
WHERE (((PEPERSON.CENTRE)>"0") AND ((PEPERSON.DATE_LEFT) Is Null) AND ((Exists (SELECT JCBATCH.PERSONNEL_NO FROM JCBATCH
WHERE JCBATCH.COMPANY = PEPERSON.COMPANY AND JCBATCH.WEEK_NO = [Input Week Number] AND JCBATCH.PERSONNEL_NO = PEPERSON.PERSONNEL_NO AND JCBATCH.ORIG_ADJUST = "Original" AND JCBATCH.UPDATED IN ("Held", "Ready", "Updated")))=False) AND ((PEPERSON.USE_IN_TB)=1) AND ((PEPERSON.COMPANY)="PFC1"))

OR PEPERSON.DATE_LEFT > DMAX(JCBATCH.WEEK_END_DATE)

ORDER BY PEPERSON.PERSONNEL_NO;

Thanks for you reply but doing this results in the following error:

Cannot have aggregate function in WHERE clause.

Any other ideas?

[ajetrumpet]

try changing the word 'WHERE' to 'HAVING'. Aside from that, I can't help much more because your data is specific and I'd have to see the database with instructions on what the resulting dataset would have to look like.

good luck sir!