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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> 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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 11:42 AM