Conflicting WHERE clause

Hello, I Have a SQL statement which returns no records...
I'm not sure what the problem is, but this is what I'm trying to do:

I added a field to this SQL statment called IB_GROUP and the where clause for this statement is:
b.IB_GROUP = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'IB_GROUP'

But it's conflicting with the other field called STATUS
for which the where clause is:
b.ACTIVE = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'BAN_STATUS'

I think it's looking for records that fit all these 4 criterias together instead of looking for records that fit the first two to put into the IB_GROUP field and the second two to put into the STATUS field

So basically how do you do a SQL statment like this which requires so many different where clauses INTO just one WHERE Clause
Like if I could do this with two separate where clauses it would work, but I need both fields to show up into this one TABLE
Can you help?

THIS IS MY CURRENT CODE BUT IT DOESN'T WORK:

CMD.CommandText = "Select BANKER_ID, PREFIX, FIRST_NM, MIDDLE_INIT, LAST_NM " & _
                        ", SUFFIX, lv.LOOKUP_DESC AS STATUS, lv.LOOKUP_DESC AS IB_GROUP, TITLE From BANKER b, LOOKUP_VALUES lv " & _
                        "Where (b.ACTIVE = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'BAN_STATUS')" & _
                        "AND (b.IB_GROUP = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'IB_GROUP')" & _
                        "AND LAST_NM Like '" & Replace(Me.txtLastName.Text, "'", "''") & "%' ORDER BY LAST_NM;"



THE FOLLOWING CODE IS WHAT I HAD BEFORE I ADDED THE IB_GROUP FIELD, AND IT WORKS, BUT I REALLY NEED THIS IB_GROUP FIELD INTO THE SAME TABLE:

CMD.CommandText = "Select BANKER_ID, PREFIX, FIRST_NM, MIDDLE_INIT, LAST_NM " & _
                        ", SUFFIX, lv.LOOKUP_DESC AS STATUS, TITLE From BANKER b, LOOKUP_VALUES lv " & _
                        "Where (b.ACTIVE = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'BAN_STATUS')" & _
                         "AND LAST_NM Like '" & Replace(Me.txtLastName.Text, "'", "''") & "%' ORDER BY LAST_NM;"
angelahontauAsked:
Who is Participating?
 
JesterTooCommented:
Try it like this... (BTW, which DBMS are you running this against?  I'm curious about the ";" at the end of the query.

CMD.CommandText = "Select BANKER_ID, PREFIX, FIRST_NM, MIDDLE_INIT, LAST_NM " & _
                        ", SUFFIX, lv1.LOOKUP_DESC AS STATUS, lv2.LOOKUP_DESC AS IB_GROUP, TITLE From BANKER b, LOOKUP_VALUES lv1 " & _
                        ", LOOKUP_VALUES lv2 " & _
                        "Where b.ACTIVE = lv1.LOOKUP_CODE AND lv1.LOOKUP_NAME = 'BAN_STATUS'" & _
                        "AND b.IB_GROUP = lv2.LOOKUP_CODE AND lv2.LOOKUP_NAME = 'IB_GROUP'" & _
                        "AND LAST_NM Like '" & Replace(Me.txtLastName.Text, "'", "''") & "%' ORDER BY LAST_NM;"    
0
 
angelahontauAuthor Commented:
The database is a Microsoft Access database called "Pipeline.mdb," is that what you mean by DBMS?
0
 
angelahontauAuthor Commented:
THANK YOU!
0
 
JesterTooCommented:
You're welcome and thank you for the grade!

Lynn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.