UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Use Both "and"/"or" In Mycriteria, Access 2010    
 
   
bardi
post Jan 3 2018, 06:21 AM
Post#1



Posts: 24
Joined: 25-March 07



Hello learned ones.

I have come close to finding an answer in the forums for my predicament but before my head explodes I am asking for help. I have a successful form that has search criteria that delivers good results if I am careful about "and" or "or". In the following AddtoWhere I can change MyCriteria = MyCriteria & " and " to MyCriteria = MyCriteria & " or " and can generate partial searches. In the bottom piece of code I need to use "OR" to be able to search for text from [LookforSubjectID] and see if it is found in [BookSubjects1ID] or [BookSubjects2ID] or [BookSubjects3ID]. (I am using text here not an integer. Bound Col is the ID). I need to also do the same and see if the text in [LookforWord] is in [BookTitle] or [BookDescription] etc. The "and" is still required for the first four lines and to join the next two blocks of "or". I would have six "AND" statements in all.

When it all comes together the search should be [LookforResourceID] AND [LookforPublicationID] AND [LookforCountryID] AND [LookforAuthor] AND the "OR" from the three [LookforSubjectID] AND the "OR" from the [LookforWord]. I am sorry for the clunky way of expressing this. Thankyou in anticipation, it's been quite journey of discovery to get this far.

============================================


Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "

End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & Chr(42) & FieldValue & Chr(42) & Chr(39))


' Increase argument count.
ArgCount = ArgCount + 1
End If

End Sub
===================================================================
MySQL = "SELECT * FROM LibraryAllQ WHERE"
MyCriteria = " "

' Use values entered in text boxes in form header to create criteria for WHERE clause.

AddToWhere [LookforResourceID], "[ResourceID]", MyCriteria, ArgCount
AddToWhere [LookforPublicationID], "[PublicationID]", MyCriteria, ArgCount
AddToWhere [LookforCountryID], "[CountryID]", MyCriteria, ArgCount
AddToWhere [LookforAuthor], "[Author]", MyCriteria, ArgCount

AddToWhere [LookforSubjectID], "[BookSubjects1ID]", MyCriteria, ArgCount
AddToWhere [LookforSubjectID], "[BookSubjects2ID]", MyCriteria, ArgCount
AddToWhere [LookforSubjectID], "[BookSubjects3ID]", MyCriteria, ArgCount

AddToWhere [LookforWord], "[BookTitle]", MyCriteria, ArgCount
AddToWhere [LookforWord], "[BookDescription]", MyCriteria, ArgCount
AddToWhere [LookforWord], "[BookNotes]", MyCriteria, ArgCount
AddToWhere [LookforWord], "[Contents]", MyCriteria, ArgCount
AddToWhere [LookforWord], "[ContentsNotes]", MyCriteria, ArgCount


' If no criterion specifed, return all records.
If MyCriteria = " " Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria
Go to the top of the page
 
Jeff B.
post Jan 3 2018, 07:31 AM
Post#2


UtterAccess VIP
Posts: 10,140
Joined: 30-April 10
From: Pacific NorthWet


Perhaps Access is interpreting the logic of your statement containing AND and OR differently than you expect. Check on the precedence Access uses when "AND" and "OR" are incorporated into the same statement.
Go to the top of the page
 
GroverParkGeorge
post Jan 3 2018, 07:49 AM
Post#3


UA Admin
Posts: 33,766
Joined: 20-June 02
From: Newcastle, WA


Careful use of Parentheses is the key to this, for example.

WHERE (A = 1 AND B = 2 AND C= 3) AND (D = 5 OR E = 6 OR F = 7)

Go to the top of the page
 
bardi
post Jan 3 2018, 08:26 PM
Post#4



Posts: 24
Joined: 25-March 07



Thankyou for your reply. Is this relating to a Case situation.?. I am stumped as to how to invoke this. I see that what you show makes sense, could you please show me by way of code how this would be actioned. Your assistance is appreciated.
Go to the top of the page
 
GroverParkGeorge
post Jan 3 2018, 09:30 PM
Post#5


UA Admin
Posts: 33,766
Joined: 20-June 02
From: Newcastle, WA


THis is not VBA. Not code.

This is in the WHERE clause of your query.

The way to get the syntax right, so you can break it down into your VBA, is to create the SQL in a query so that it returns the values you want, with the parentheses set the way you need them. Then you can take that and break it back down to put into the VBA in the proper places.
This post has been edited by GroverParkGeorge: Jan 3 2018, 09:32 PM
Go to the top of the page
 
bardi
post Jan 8 2018, 03:17 AM
Post#6



Posts: 24
Joined: 25-March 07



I have found that if I insert MyCriteria = MyCriteria & " or " in certain lines in below I get something toward the following statement.
I have also tried doing away with the add to where and tried constructing a plain Select/From/Where statement but always end up with and end of statement error.

Can you please give me an example of how the following can be constructed. I have trawled so many pages looking for help but nothing as yet. I was also placing
MyCriteria = " or " between lines but also not successful.
The following is my need. Thankyou

AddToWhere [LookforResourceID], "[ResourceID]", MyCriteria, ArgCount
AND
AddToWhere [LookforPublicationID], "[PublicationID]", MyCriteria, ArgCount
AND
AddToWhere [LookforCountryID], "[CountryID]", MyCriteria, ArgCount
AND
AddToWhere [LookforAuthor], "[Author]", MyCriteria, ArgCount
AND
AddToWhere [LookforSubjectID], "[BookSubjects1ID]", MyCriteria, ArgCount OR AddToWhere [LookforSubjectID], "[BookSubjects2ID]", MyCriteria, ArgCount OR AddToWhere [LookforSubjectID], "[BookSubjects3ID]", MyCriteria, ArgCount OR AddToWhere [LookforSubjectID], "[BookSubjects3ID]", MyCriteria, ArgCount
AND
AddToWhere [LookforWord], "[BookTitle]", MyCriteria, ArgCount OR AddToWhere [LookforWord], "[BookDescription]", MyCriteria, ArgCount OR AddToWhere [LookforWord], "[BookNotes]", MyCriteria, ArgCount OR AddToWhere [LookforWord], "[Contents]", MyCriteria, ArgCount OR AddToWhere [LookforWord], "[ContentsNotes]", MyCriteria, ArgCount
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th October 2018 - 11:44 AM