Query with criteria problem (1 Viewer)

[scuddersm]

Hello all,

This is what I am trying to do.
Compare two tables. Find associated records in table 1 that do not match records in table 2 and vice versa. Next be able to further filter those results based on a combo box selection. I am trying to do this all in one query. Data is being pulled from two separate sources, which is why I need to compare using two tables.

What is happening: Nonmatching records are returned by query, but it won't filter by combo box.

The results are being displayed on a multiple item form. Combo box selection is on the same form. After combo box selection I have tried both openquery and requery with no results.

Here is the SQL. I tried to make it a little easier to read. Thanks.

SELECT tblMasterList.Rank, tblMasterList.Last, tblMasterList.First, tblMasterList.Section, tblMasterList.DEPARTMENT, tblMasterList.Command, tblMasterList.[YB Date], tblCPIMS.[CPIMS YB], tblMasterList.[LCM Date], tblCPIMS.[CPIMS LCM], tblMasterList.[GB Date], tblCPIMS.[CPIMS GB], tblMasterList.[GB Cert Date], tblCPIMS.[CPIMS GB Cert], tblMasterList.[BB Date], tblCPIMS.[CPIMS BB], tblMasterList.[BB Cert Date], tblCPIMS.[CPIMS BB Cert]

FROM tblMasterList LEFT JOIN tblCPIMS ON (tblMasterList.First = tblCPIMS.First) AND (tblMasterList.Last = tblCPIMS.Last)

WHERE (((tblMasterList.Command)=Forms!frmDBCPIMSDataErr!cmbCommandSelect) And ((tblMasterList.[YB Date]) Is Null) And ((tblCPIMS.[CPIMS YB]) Is Not Null)) Or (((tblMasterList.[YB Date]) Is Not Null) And ((tblCPIMS.[CPIMS YB]) Is Null))
Or (((tblMasterList.[LCM Date]) Is Null) And ((tblCPIMS.[CPIMS LCM]) Is Not Null)) Or (((tblMasterList.[LCM Date]) Is Not Null) And ((tblCPIMS.[CPIMS LCM]) Is Null))
Or (((tblMasterList.[GB Date]) Is Null) And ((tblCPIMS.[CPIMS GB]) Is Not Null)) Or (((tblMasterList.[GB Date]) Is Not Null) And ((tblCPIMS.[CPIMS GB]) Is Null))
Or (((tblMasterList.[GB Cert Date]) Is Null) And ((tblCPIMS.[CPIMS GB Cert]) Is Not Null)) Or (((tblMasterList.[GB Cert Date]) Is Not Null) And ((tblCPIMS.[CPIMS GB Cert]) Is Null))
Or (((tblMasterList.[BB Date]) Is Null) And ((tblCPIMS.[CPIMS BB]) Is Not Null)) Or (((tblMasterList.[BB Date]) Is Not Null) And ((tblCPIMS.[CPIMS BB]) Is Null))
Or (((tblMasterList.[BB Cert Date]) Is Null) And ((tblCPIMS.[CPIMS BB Cert]) Is Not Null)) Or (((tblMasterList.[BB Cert Date]) Is Not Null) And ((tblCPIMS.[CPIMS BB Cert]) Is Null));

Thanks for any help.

 

[GinaWhipp]

The first issue is you are using quite a few Reserved Words in your query, to list a few...

First
Last
Section
Command

These are going to confuse Access. To avoid Access being you confused you are going to have to bracket all of those words, i.e.

[First]
[Last]
etc...

To see a complete list of Reserved Words, see...
http://allenbrowne.com/AppIssueBadWord.html

If after you fix all of that your Combo Box still does not work, please dvise what you are filtering on.

 

[vbaInet]

Find associated records in table 1 that do not match records in table 2 and vice versa.

By the way, your query isn't doing the vice versa in this instance.

 

[scuddersm]

You're absolutely correct! the "vice versa" part isn't working. Hopefully I can fix that part. I am pretty sure that I had it working before I started to rework this query.

I changed all of the reserved name into [brackets] and the combo box is still not working.

The combo box's source is another table (tblcommand) which lists the available [COMMAND] this same field is in both of the tables that are being used in the query.

This is a sample of what I am trying to do:
tblCommand: (source for combo box)
Command:
command 1
command 2
command 3

tblMasterlist (table 1)
rank last first command YB Date:
PVT smith john command 1 1/1/2014
pvt doe jane command 2 12/31/2013
pvt right all command 3 6/1/2014

tblCPIMS (table 2)
rank last first command CPIMS YB:
PVT smith john command 1 1/2/2014
pvt doe jane command 2 12/30/2013
pvt right all command 3 6/1/2014

This is what I want it to do:
First, query all records in tblmasterlist.[YB Date] that do not match tblCPIMS.[CPIMS YB], to include those that have data in one table and not in the other, then do the reverse.
Next, let me filter my results on the form using the combo box selection.

Using the example jane's and john's record would be displayed on the form and then I would be able to filter the results down to just a specific command selection using the combo box.

Thanks again.

 

[GinaWhipp]

What's the code on the Combo Box.

 

[scuddersm]

Query Criteria
[Forms]![frmDBCPIMSDataErr]![cmbCommandSelect]

Combo Box Code
Private Sub cmbCommandSelect_AfterUpdate()
DoCmd.OpenQuery ("qryDBCPIMSDateMismatch")
End Sub


Form is based on query ("qryDBCPIMSDateMismatch")
Combo is unbound and rowsource is tblCommand, which list available command selections.

I hope that this is what you asked for.

 

[GinaWhipp]

Hmm, let me see if I got this... Not sure this will work but try putting a...

Me.Requery

...in the After_Update event of the Combo Box.

 

[scuddersm]

Error message:
Microsoft access cannot find the object 'me'

 

[scuddersm]

Sorry, I think I had it in the wrong place. I justed typed it straight into the event_afterupdate box in properties. I think that you meant it to go into the VBA code on the control [Event_procedure]. I moved it to the VBA. The query values on the form do not change regardless of combo box selection.

 

[GinaWhipp]

Yep, that's what I meant...

Hmm, does the query run? If you open the Form is there anything displayed?

 

[scuddersm]

The query does run both out of the form and in the form. It displays all mismatched records between the two tables. However, when I select an item (a specific command) from the combo box the query does not update to only show those mismatch records with the corresponding command. Because the form is based on the query it loads automatically with all mismatched records when opened.

Still scratching my head.

 

[GinaWhipp]

Try (in the After_Update event procedure of your Combo Box)...

Me.RecordSource = "qryDBCPIMSDateMismatch"

 

[scuddersm]

Unfortunatley no change.

 

[scuddersm]

OK, so I figured out a way around the issue. Its not pretty and it doesn't allow me to just type in the updates on the form, but it does allow me to select mismatched records by [command] from the combo box. I did this through the use of two queries. The first query finds all of the mismatched records in all commands and the second query uses the first as its record source and has the combo box and its SQL WHERE statement criteria. So at least I can print the information out and then manually update the records this way.

Do you have any suggestions on how I could modify the returned values from the query on the form itself, so that I don't have to print out each selection to make updates?

 

[GinaWhipp]

Without the database itself, so I could tinker... I am out of ideas.

 

[vbaInet]

For testing purposes get rid of all the other criteria and just have:

WHERE tblMasterList.Command = Forms!frmDBCPIMSDataErr!cmbCommandSelect

Now re-test the after update event to make sure it's pulling the right records.

We can look into your "vice versa part" later.