Applying filter to same field

[sougata666]

Here is the result of a query

Name ItemBought

Tom Box
Tom Candle
Tom Pen
Bob Box
Bob Pen

A form is used to Select name (combo box) and items bought (check boxes). In this case, the form has a single combo box and three check boxes corresponding to the three items (box,candle and pen).

The user is at liberty to Select a Name (optional) and any combination of items (all these items are optional except that the OK button cannot be clicked if none of the options are selected. So a minimum of one item or a name has to be selected).

Once Ok is clicked, a string filter is passed to a pre-generated report which uses the above query.

Now , this works fine as long as i select only one checkbox OR one checkbox and a name OR just a name.

I want to see who has bought a candle AND a pen by selecting both check boxes but it generates a blank report. (It should have produced "TOM")

I suspect that this is because of the use of "AND" in my search string.

How do I solve this?

 

[CJ_London]

How do I solve this?

I don't know - but we might be able to help if you provided the sql you are using so we can see why it doesn't work as you require.

 

[sougata666]

Ok. I am attaching the small database. I have removed the user combo box. Individual check boxes work. But when I select two boxes, the report is blank.

 

[sougata666]

I must emphasize that the logic should be "AND" for checkboxes and not "OR"

 

[CJ_London]

sorry - can't open it - just copy and paste the sql

 

[sougata666]

query SQL:

SELECT tblNameItem.User, tblNameItem.Item
FROM tblNameItem;

Report SQL (recordsource):

SELECT tblNameItem.User, tblNameItem.Item
FROM tblNameItem;

VB code:

Private Sub cmdOK_Click()
Dim SearchString As String
Dim LengthOfString As Integer


If Me.chkBox = -1 Then
SearchString = SearchString & "([Item] = 1) AND "
End If

If Me.chkCandle = -1 Then
SearchString = SearchString & "([Item] = 2) AND "
End If

If Me.chkPen = -1 Then
SearchString = SearchString & "([Item] = 3) AND "
End If

LengthOfString = Len(SearchString) - 5
If LengthOfString <= 0 Then
MsgBox "You have not entered a criteria", vbInformation, "Invalid Search"
Else
SearchString = Left$(SearchString, LengthOfString) 'Remove Last five characters

Me.Visible = False

DoCmd.OpenReport "RptTest", acViewReport, , SearchString

End If

End Sub

 

[jdraw]

You do not have your tables set up to record Purchases (who bought what). As I understand your db, you have allowed multiple names for an Item?? which I don't think is your intent.

Customer--->Bought <---Items
where Bought records the CustomerID and the ItemID

 

[sougata666]

Why couldn't you open the file? it is small enough. Saved in Access 2013. I have attached them again in older formats.

test.mdb is in access 2000 format.

The other one is in 2003 format.

 

[sougata666]

You do not have your tables set up to record Purchases (who bought what). As I understand your db, you have allowed multiple names for an Item?? which I don't think is your intent.

Customer--->Bought <---Items
where Bought records the CustomerID and the ItemID

There is a junction table called tblNameItem which is recording what you have written. The query is based on that table.

 

[jdraw]

Hmmm...

This is what I see. I was looking for bought or purchase.
There wasn't anything on your form suggesting purchase.

I guess you know what you're looking for.

I did a debug.print on searchstring and got this
([Item] = 1) AND ([Item] = 2) AND ([Item] = 3)

Your recordsource is

SELECT tblNameItem.User, tblNameItem.Item
FROM tblNameItem;

and, in my view, you won't get any hits based on this (using your filter/searchstring in a where clause)

SELECT tblNameItem.User, tblNameItem.Item
FROM tblNameItem where
([Item] = 1) AND ([Item] = 2) AND ([Item] = 3)

 

[sougata666]

Hmmm...

This is what I see. I was looking for bought or purchase.
There wasn't anything on your form suggesting purchase.

I guess you know what you're looking for.

I did a debug.print on searchstring and got this
([Item] = 1) AND ([Item] = 2) AND ([Item] = 3)

Your recordsource is

SELECT tblNameItem.User, tblNameItem.Item
FROM tblNameItem;

and, in my view, you won't get any hits based on this (using your filter/searchstring in a where clause)

SELECT tblNameItem.User, tblNameItem.Item
FROM tblNameItem where
([Item] = 1) AND ([Item] = 2) AND ([Item] = 3)

Yes. That's the issue. Since the checkboxes refer to the same field, I am not able to use the AND logic properly. I had done the debug thing too.

 

[sougata666]

I am sure there must be a solution this. Is there an SQL statement I can use as my search string which will get me the result?

 

[sougata666]

Can INTERSECT help? How will I implement it in this example? I guess I will have to use INNER JOIN in access but how?

 

[CJ_London]

if you change your report recordsource to a crosstab

 TRANSFORM Count(tblNameItem.Item) AS CountOfItem
SELECT tblNameItem.User
FROM tblNameItem
GROUP BY tblNameItem.User
PIVOT Choose([item],"Box","Candle","Pen") In ("Box","Candle","Pen");

you can change your filter to be

 .....
 .....
 If Me.chkBox = -1 Then
SearchString = SearchString & "([Box] is not null) AND "
End If

If Me.chkCandle = -1 Then
SearchString = SearchString & "([Candle] is not null) AND "
End If

 If Me.chkPen = -1 Then
SearchString = SearchString & "([Pen] is not null) AND "
End If
 ....
 ....

However the whole thing is not normalised - if you add another item (e.g. string) then you need to redesign your form, query and report and change your coding to handle it.

 

[CJ_London]

a further thought if you leave the report recordsource alone is to use this criteria as a basis to build your search string - note that you have two parts to build, indicated in red

(((Exists (SELECT * FROM tblnameitem T where user=tblnameitem.user and item=1))=True) AND ((Exists (SELECT * FROM tblnameitem T where user=tblnameitem.user and item=2))=True) AND ((tblNameItem.Item) In (1,2)));