I have the below code in my form, which basically, looks to see if a supplier is on a targeted list, creates an new table and then populates my form.
If the supplier performance improves, then i remove the supplier from the list by adding a date removed into a Date_Removed field.
Problem is that the code below does not refer to that field, so i want to change the code to check that the date_removed field is null.
My Date_Removed field is in the tblqcsupplierwatch table. I have tried using various "AND" commands, but to no avail.
I Figure it needs to go in the IIF(NZ(Dcount, but cannot work out where to put the additional code.
Any help is appreciated.
If the supplier performance improves, then i remove the supplier from the list by adding a date removed into a Date_Removed field.
Problem is that the code below does not refer to that field, so i want to change the code to check that the date_removed field is null.
My Date_Removed field is in the tblqcsupplierwatch table. I have tried using various "AND" commands, but to no avail.
I Figure it needs to go in the IIF(NZ(Dcount, but cannot work out where to put the additional code.
Code:
IIf(Nz(DCount('*','tblqcsupplierwatch','Sup_Code=''' & [Forms]![frmSupplierWatch]![CboxSupplier] & ''''),0)=0,0,-1)
Code:
DoCmd.RunSQL "SELECT DISTINCT tblProductSupplier.Sup_Code, tblCatalog.Department, tblCatalog.Category, IIf(Nz(DCount('*','tblqcsupplierwatch','Sup_Code=''' & [Forms]![frmSupplierWatch]![CboxSupplier] & ''''),0)=0,0,-1) AS InGroup INTO TEMP_SupplierWatch " & vbCrLf & _
"FROM tblProductSupplier LEFT JOIN tblCatalog ON tblProductSupplier.Cat_No = tblCatalog.Cat_No " & vbCrLf & _
"WHERE (((tblProductSupplier.Sup_Code)=[Forms]![frmSupplierWatch]![CboxSupplier]));"
Any help is appreciated.