exclude records based on records from another table

[kpal29]

I have a table where I import data. This table has several fields. I need to exclude certain records from my report based on a value in a field or a combination field values.

Users need to add/edit these values so I have created a form for them to input records where if they need to exclude field Company = ABC, the add ABC to the company field and leave the rest blank for that record.

What is the best way to have my query look at all each record in the 'filters' table and exclude those records that match each row?

 

[PHV]

How is your 'filters' table defined ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kpal29]

I have a field in the filters table that matches the field in the data table. For example the fields are:
Facility
Point1
Point2
Dispatch Office

I need to exclude records where facility = CHI and point1 equals 'BNSF'.

So there is a record in the filters table where Facility = CHI and point1 = BNSF and the rest of the fields are null.

I also need to exclude records where Point2 begins with 'HUDD' regardless of letters at the end. So there is a record in the Filters table where point2 = HUDD and the rest of the fields are blank.

Thanks

 

[PHV]

If you launch your report with the DoCmd.OpenReport method then you may build a dynamic criteria you can use as 4th argument, eg:
strCriteria = "(Facility<>'CHI' OR point1<>'BNSF') AND Not (point2 Like 'HUDD*')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry for the typo:
strCriteria = "(Facility<>'CHI' OR point1<>'BNSF') AND Not (point2 Like 'HUDD*')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kpal29]

I do not want to 'hard code' any values as users will be adding/editing and deleting 'situations' in which we need to exclude records. This is why I wanted to use VB and have it look at a table?

 

[PHV]

I do not want to 'hard code' any values
I talked about dynamic criteria built in VBA code ...

 

[kpal29]

How do I make it so the user does not have to change code if I am typing strings? Please explain how to set this up?
Thanks

 

[kpal29]

I got it to work with this code but seems a little clumsy. Pls advise if you have a better way.

Option Compare Database

Sub Filter_CustomerDrayage()
Dim db As Database
Dim rstFilterCustomerDrayage As Recordset
Dim rstFilterListCustomerDrayage As Recordset
Dim intI As Integer
Dim IntB As Integer

Set db = CurrentDb

'open the FilterCustomer Drayage Table
Set rstFilterListCustomerDrayage = db.OpenRecordset("FilterList_CustomerDrayage")
'move to the first record
rstFilterListCustomerDrayage.MoveFirst
'Start the counter for the filter table
IntB = 1

'keep looping until you get to the last record in the filter table
Do Until rstFilterListCustomerDrayage.EOF

'Now open the CustomerDrayage import table
Set rstCustomerDrayage = db.OpenRecordset("Import_CustomerDrayage")
'Move to the first record
rstCustomerDrayage.MoveFirst
'Start the counter for the import table
intI = 1
'keep looping until you get to the last record in the import table
Do Until rstCustomerDrayage.EOF
'Allow code to edit the 'filter' field in the import table
rstCustomerDrayage.Edit

Dim FilterCustomer As String
Dim FilterFacility As String
Dim FilterDispatch As String
Dim FilterHarbor As String
Dim FilterPoint1 As String
Dim FilterPoint2 As String
Dim FilterCarrier As String

'In the filter table, if the facility field is null, make FilterFacility equal to the import table facility field or else leave it as is
If IsNull(rstFilterListCustomerDrayage![Facility]) = True Then FilterFacility = rstCustomerDrayage![Facility] Else FilterFacility = rstFilterListCustomerDrayage![Facility]
'In the filter table, if the customer name field is null, make FilterCustomer equal to the import table customer name field or else leave it as is
If IsNull(rstFilterListCustomerDrayage![Customer Name]) = True Then FilterCustomer = rstCustomerDrayage![Cust_name] Else FilterCustomer = rstFilterListCustomerDrayage![Customer Name]
'In the filter table, if the dispatch field is null, make FilterDispatch equal to the import table dispatch field or else leave it as is
If IsNull(rstFilterListCustomerDrayage![Dispatch Office]) = True Then FilterDispatch = rstCustomerDrayage![dispOffice_name] Else FilterDispatch = rstFilterListCustomerDrayage![Dispatch Office]
'In the filter table, if the harbor field is null, make FilterHarbor equal to the import table harbor field or else leave it as is
If IsNull(rstFilterListCustomerDrayage![harbor]) = True Then FilterHarbor = rstCustomerDrayage![Harbor_name] Else FilterHarbor = rstFilterListCustomerDrayage![harbor]
'In the filter table, if the point1 (FDest) field is null, make FilterPoint1 equal to the import table FDest field or else leave it as is
If IsNull(rstFilterListCustomerDrayage![Point1]) = True Then FilterPoint1 = rstCustomerDrayage![FDest_Name] Else FilterPoint1 = rstFilterListCustomerDrayage![Point1]
'In the filter table, if the point2 (TDest) field is null, make FilterPoint2 equal to the import table TDest field or else leave it as is
If IsNull(rstFilterListCustomerDrayage![Point2]) = True Then FilterPoint2 = rstCustomerDrayage![TDest_Name] Else FilterPoint2 = rstFilterListCustomerDrayage![Point2]
'In the filter table, if the Carrier field is null, make FilterCarrier equal to the import table Carrier field or else leave it as is
If IsNull(rstFilterListCustomerDrayage![Carrier Name]) = True Then FilterCarrier = rstCustomerDrayage![Carrier_Name] Else FilterCarrier = rstFilterListCustomerDrayage![Carrier Name]

'Compare the variables set by the Filter Table to the values in the Import table
If rstCustomerDrayage![Facility] = FilterFacility And _
rstCustomerDrayage![Cust_name] = FilterCustomer And _
rstCustomerDrayage![dispOffice_name] = FilterDispatch And _
rstCustomerDrayage![Harbor_name] = FilterHarbor And _
rstCustomerDrayage![FDest_Name] = FilterPoint1 And _
rstCustomerDrayage![TDest_Name] = FilterPoint2 And _
rstCustomerDrayage![Carrier_Name] = FilterCarrier Then


'If the records match, update the Filter field in the Import table to 'True' meaning this record s/b excluded
rstCustomerDrayage![Filter] = True
rstCustomerDrayage.Update
Else
'If it doesn't match, update the Filter field in the Import table to 'False' meaning this is a valid record
rstCustomerDrayage![Filter] = False
End If
'Move to the next record in the import table and compare to the same record in the filter table
rstCustomerDrayage.MoveNext
intI = intI + 1
Loop
'Once every record in the import table has been compared to the first record in the Filter table, move to the next & _
'record in the filter table and compare it to all Import table records until all filter records have been compared & _
'against all import records.
rstFilterListCustomerDrayage.MoveNext
IntB = IntB + 1
Loop

rstCustomerDrayage.Close
rstFilterListCustomerDrayage.Close

End Sub