Checking both criteria

[Noel2]

Hi All,

I have a problem with a query that I have created. I have two criteria that I need to be satisfied for the records to reflect what I want. Let me explain. The records that I do not want to see are the records where the field tech is equal to a value entered on my form and where the field year is equal to a value entered on my form. The problem I am having is that when I am inserting the HAVING clause into my query as down below:

HAVING ((tch_tbl.maint_tch)<>[Forms]![frm_mntc]![tch]) AND (([qry_lst_maint]![yr])<>[Forms]![frm_mntc]![yr])

I am losing all of the records that have the year equal to the year on the form. I need to keep some of those. I just need to not have the ones where the tech is equal to the form value and the year is equal to the form value. This is probably a simple fix, but I can't see that right now. Please let me know what you think.

Thanks,
Noel

 

[scriverb]

I know this sounds wrong but try changing your AND to an OR in the having statement. It is somewhat like a double negative but it seems to work in a test database that have here.

Let me know if this works for you.

Bob Scriver

 

[Noel2]

Thanks for the response. This is good except I didn't tell you the whole story. These are not the only two criteria in my expression. Below I have included what is actually going on with the query. The two fields that I am talking about are the qry_lst_maint.last_yr and the value that would be equal to the value for [Forms]![frm_mntnc]![mont]. Would that still work? And if so, it doesn't make sense and it does seem very wrong. Why would that work? Please let me know.

HAVING (((tbl_tch_asst.maint_mon) Like &quot;*&quot; & [Forms]![frm_mntnc]![mont] & &quot;*&quot AND ((qry_lst_maint.last_yr)<>2003) AND ((IIf(([qry_lst_maint]![num_mntnc]=1),Left([tbl_tch_asst]![maint_mon],3),Mid([tbl_tch_asst]![maint_mon],[qry_lst_maint]![num_mntnc]*3+[qry_lst_maint]![num_mntnc]-3,3)))<>[Forms]![frm_mntnc]![mont]));

Thanks again,
Noel

 

[scriverb]

Sorry Noel2 I don't see anything wrong with your HAVING statement. Maybe posting the entire query so that the Group By and everything else would help.

Bob Scriver

 

[Noel2]

Hey all,

I got it rockin' with just the addition of a couple of parenthesis. Here is the SQL for those who would like to see, kinda hairy but it works:

SELECT tbl_tch_asst.tch, tbl_tch_asst.maint_mon, qry_lst_maint.LastOftyp AS last_typ, tbl_tch_asst.addr, tbl_tch_asst.annual_hrs, Int((([tbl_tch_asst]![annual_hrs]/[tbl_tch_asst]![num_mntncs])+0.005)*100)/100 AS qrtly_hrs, IIf(IsNull([qry_2k3_pms]![hours]),0,[qry_2k3_pms]![hours]) AS hrs_cmplt, qry_lst_maint.last_yr, qry_lst_maint.tech, qry_lst_maint.last_dt, IIf(([qry_lst_maint]![num_mntnc]=1),Left([tbl_tch_asst]![maint_mon],3),Mid([tbl_tch_asst]![maint_mon],[qry_lst_maint]![num_mntnc]*3+[qry_lst_maint]![num_mntnc]-3,3)) AS lst_mnth, [tbl_tch_asst]![annual_hrs]-IIf(IsNull([qry_2k3_pms]![hours]),0,[qry_2k3_pms]![hours]) AS rem_2k3, IIf([qry_2k2_pms].[bnkd]<0,0,[qry_2k2_pms].[bnkd]) AS 2k2hrs, Int(((([tbl_tch_asst]![annual_hrs]/[tbl_tch_asst]![num_mntncs]+IIf([qry_2k2_pms].[bnkd]<0,0,[qry_2k2_pms].[bnkd])+Nz([qry_2k3_pms].[xpctd_hrs],0))-IIf(IsNull([qry_2k3_pms]![hours]),0,[qry_2k3_pms]![hours]))+0.005)*100)/100 AS ttl_qrtr, Nz([qry_2k3_pms].[xpctd_hrs],0) AS xpct
FROM ((tbl_tch_asst LEFT JOIN qry_2k2_pms ON tbl_tch_asst.addr = qry_2k2_pms.addr) LEFT JOIN qry_2k3_pms ON tbl_tch_asst.addr = qry_2k3_pms.addr) LEFT JOIN qry_lst_maint ON tbl_tch_asst.addr = qry_lst_maint.addr
GROUP BY tbl_tch_asst.tch, tbl_tch_asst.maint_mon, qry_lst_maint.LastOftyp, tbl_tch_asst.addr, tbl_tch_asst.annual_hrs, Int((([tbl_tch_asst]![annual_hrs]/[tbl_tch_asst]![num_mntncs])+0.005)*100)/100, IIf(IsNull([qry_2k3_pms]![hours]),0,[qry_2k3_pms]![hours]), qry_lst_maint.last_yr, qry_lst_maint.tech, qry_lst_maint.last_dt, IIf(([qry_lst_maint]![num_mntnc]=1),Left([tbl_tch_asst]![maint_mon],3),Mid([tbl_tch_asst]![maint_mon],[qry_lst_maint]![num_mntnc]*3+[qry_lst_maint]![num_mntnc]-3,3)), [tbl_tch_asst]![annual_hrs]-IIf(IsNull([qry_2k3_pms]![hours]),0,[qry_2k3_pms]![hours]), IIf([qry_2k2_pms].[bnkd]<0,0,[qry_2k2_pms].[bnkd]), Int(((([tbl_tch_asst]![annual_hrs]/[tbl_tch_asst]![num_mntncs]+IIf([qry_2k2_pms].[bnkd]<0,0,[qry_2k2_pms].[bnkd])+Nz([qry_2k3_pms].[xpctd_hrs],0))-IIf(IsNull([qry_2k3_pms]![hours]),0,[qry_2k3_pms]![hours]))+0.005)*100)/100, Nz([qry_2k3_pms].[xpctd_hrs],0)
HAVING (((tbl_tch_asst.maint_mon) Like &quot;*&quot; & [Forms]![frm_mntnc]![mont] & &quot;*&quot AND ((tbl_tch_asst.tch) Like &quot;*&quot; & GetQName() & &quot;*&quot AND (((qry_lst_maint.last_yr)<>2003) OR ((IIf(([qry_lst_maint]![num_mntnc]=1),Left([tbl_tch_asst]![maint_mon],3),Mid([tbl_tch_asst]![maint_mon],[qry_lst_maint]![num_mntnc]*3+[qry_lst_maint]![num_mntnc]-3,3)))<>[Forms]![frm_mntnc]![mont])));