Hi everybody,
I need to create a query that will search based on keywords and major categories. My tables are set-up as follows:
tblLiteratureArticles: Abbreviation(FK)
ArticleID(PK)
Author
Title
etc... tblArticleKeyword (Join Table btn tblLiteratureArticles & tblKeywords) ArticleID
Keyword
(these two create a composite key) tblKeywords Keyword(PK) tblLitCategories (one-to-many relationship with tblLiteratureArticles) MajorCategory
Abbreviation(PK)
Selected(Yes/No field) I hope I made the relationships clear. Okay, so I have a form (Form1) with a text box for users to type in keyword(s) separated by commas. I also have a subform based on an update query (subfrmLitCategories), which has the [MajorCategory] field and the [Selected] Yes/No field to allow users to choose any combination of major categories.
I need to have a subform based on a query that will filter records based on the keywords typed in the text box and the major categories chosen in the subform. Is this possible? Are my table relationships correct to create a query to do this job? If so, how do I do it?
Thanks for your help/suggestions!
23 2405
Hi everybody,
I need to create a query that will search based on keywords and major categories. My tables are set-up as follows:
tblLiteratureArticles:Abbreviation(FK)
ArticleID(PK)
Author
Title
etc... tblArticleKeyword (Join Table btn tblLiteratureArticles & tblKeywords)ArticleID
Keyword
(these two create a composite key) tblKeywordsKeyword(PK) tblLitCategories (one-to-many relationship with tblLiteratureArticles)MajorCategory
Abbreviation(PK)
Selected(Yes/No field) I hope I made the relationships clear. Okay, so I have a form (Form1) with a text box for users to type in keyword(s) separated by commas. I also have a subform based on an update query (subfrmLitCategories), which has the [MajorCategory] field and the [Selected] Yes/No field to allow users to choose any combination of major categories.
I need to have a subform based on a query that will filter records based on the keywords typed in the text box and the major categories chosen in the subform. Is this possible? Are my table relationships correct to create a query to do this job? If so, how do I do it?
Thanks for your help/suggestions!
I was able to create a query to filter records based on major categories and ONE keyword. However, I need to allow users to type in more than one keyword, separated by commas. Does anyone know what changes I need to make to the SQL?
SELECT
tblLiteratureArticles.Abbreviation,
tblLiteratureArticles.ArticleID,
tblLiteratureArticles.PrincipalAuthor,
tblLiteratureArticles.TITLE,
tblLiteratureArticles.Journal,
tblLiteratureArticles.Volume,
tblLiteratureArticles.Number,
tblLiteratureArticles.PAGES,
tblLiteratureArticles.DATE,
tblLiteratureArticles.OtherAuthors,
tblLiteratureArticles.Link,
tblArticleKeyword.Keyword
FROM
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE
(((tblArticleKeyword.Keyword)=[Forms]![Form1]![txtKeywords]) AND ((tblLitCategories.Selected)=True));
I was able to create a query to filter records based on major categories and ONE keyword. However, I need to allow users to type in more than one keyword, separated by commas. Does anyone know what changes I need to make to the SQL?
SELECT
tblLiteratureArticles.Abbreviation,
tblLiteratureArticles.ArticleID,
tblLiteratureArticles.PrincipalAuthor,
tblLiteratureArticles.TITLE,
tblLiteratureArticles.Journal,
tblLiteratureArticles.Volume,
tblLiteratureArticles.Number,
tblLiteratureArticles.PAGES,
tblLiteratureArticles.DATE,
tblLiteratureArticles.OtherAuthors,
tblLiteratureArticles.Link,
tblArticleKeyword.Keyword
FROM
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE
(((tblArticleKeyword.Keyword)=[Forms]![Form1]![txtKeywords]) AND ((tblLitCategories.Selected)=True));
Hi.
I'd like to bring your back to the post - http://www.thescripts.com/forum/thread671666.html msg#4
Add this function to a public module. SQL will look like this -
SELECT
-
tblLiteratureArticles.Abbreviation,
-
tblLiteratureArticles.ArticleID,
-
tblLiteratureArticles.PrincipalAuthor,
-
tblLiteratureArticles.TITLE,
-
tblLiteratureArticles.Journal,
-
tblLiteratureArticles.Volume,
-
tblLiteratureArticles.Number,
-
tblLiteratureArticles.PAGES,
-
tblLiteratureArticles.DATE,
-
tblLiteratureArticles.OtherAuthors,
-
tblLiteratureArticles.Link,
-
tblArticleKeyword.Keyword
-
FROM
-
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
-
WHERE
-
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True AND tblLitCategories.Selected=True;
Hi.
I'd like to bring your back to the post - http://www.thescripts.com/forum/thread671666.html msg#4
Add this function to a public module. SQL will look like this -
SELECT
-
tblLiteratureArticles.Abbreviation,
-
tblLiteratureArticles.ArticleID,
-
tblLiteratureArticles.PrincipalAuthor,
-
tblLiteratureArticles.TITLE,
-
tblLiteratureArticles.Journal,
-
tblLiteratureArticles.Volume,
-
tblLiteratureArticles.Number,
-
tblLiteratureArticles.PAGES,
-
tblLiteratureArticles.DATE,
-
tblLiteratureArticles.OtherAuthors,
-
tblLiteratureArticles.Link,
-
tblArticleKeyword.Keyword
-
FROM
-
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
-
WHERE
-
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True AND tblLitCategories.Selected=True;
Thanks! I pasted the SQL exactly, and I also pasted the function into a module. But it's not working. I tried to type in two keywords into the textbox(txtKeywords), but I got no results in the query. Was I supposed to change something in the function?
Thanks for your help!
Thanks! I pasted the SQL exactly, and I also pasted the function into a module. But it's not working. I tried to type in two keywords into the textbox(txtKeywords), but I got no results in the query. Was I supposed to change something in the function?
Thanks for your help!
The function works with one keyword, but it does not work with 2 or more. I believe the code for the function should be debugged, but I can't see what's wrong with it. I know that the Else statement within the Do Loop should be storing the keywords, right? I can't see why the function isn't returning more than one keyword. Of course, I am a newbie code writer; the logic is screwed up somewhere, but I can't see it. - Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal varField As Variant) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
End Function
The function works with one keyword, but it does not work with 2 or more. I believe the code for the function should be debugged, but I can't see what's wrong with it. I know that the Else statement within the Do Loop should be storing the keywords, right? I can't see why the function isn't returning more than one keyword. Of course, I am a newbie code writer; the logic is screwed up somewhere, but I can't see it. - Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal varField As Variant) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
End Function
I have been doing something similar.
The code (quoted here) worked for me.
Are you separating your search keywords with commas?
Have you tried to debug the code yourself, using the 'step through' facility within VBA?
I have been doing something similar.
The code (quoted here) worked for me.
Are you separating your search keywords with commas?
Have you tried to debug the code yourself, using the 'step through' facility within VBA?
Yes, I separate the keywords by commas.
I have tried to debug the code myself, but I have not used the 'step through' facility; I've never heard of it. If the code worked for you, I am even more confused. I copy-and-pasted the SQL and the Function code, so I shouldn't have any syntax errors.
Do you think the problem could be in the way my tables are set-up? I have the keywords in a join table, so that each article can be assigned mutliple keywords using a composite key. So, article 1 could be paired with more than one keyword. Is this code designed to take that into account, or does it even matter?
Thanks for your help!
The function works with one keyword, but it does not work with 2 or more. I believe the code for the function should be debugged, but I can't see what's wrong with it. I know that the Else statement within the Do Loop should be storing the keywords, right? I can't see why the function isn't returning more than one keyword. Of course, I am a newbie code writer; the logic is screwed up somewhere, but I can't see it. - Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal varField As Variant) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
End Function
Hi and apologies. Certainly this code will not work anyway. Here is working one. -
Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal varField As Variant) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
-
Loop Until intPos = 0
-
-
End Function
-
Hi and apologies. Certainly this code will not work anyway. Here is working one. -
Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal varField As Variant) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
-
Loop Until intPos = 0
-
-
End Function
-
Awesome! It worked! Well, kinda...there are two problems:
1. It didn't narrow the search. If I type in two keywords like this, "distillation, column", the query will return articles that have either one keyword or the other. I need it to only show the articles that have both keywords.
2. When the results show up in datasheet view, articles show up multiple times. For instance, article 4 has distillation and column as keywords. If I type in 'distillation, column' into the search box, article 4 is displayed twice - one time for 'distillation' and one time for 'column.'
Thanks for your help! I will keep trying!
Awesome! It worked! Well, kinda...there are two problems:
1. It didn't narrow the search. If I type in two keywords like this, "distillation, column", the query will return articles that have either one keyword or the other. I need it to only show the articles that have both keywords.
2. When the results show up in datasheet view, articles show up multiple times. For instance, article 4 has distillation and column as keywords. If I type in 'distillation, column' into the search box, article 4 is displayed twice - one time for 'distillation' and one time for 'column.'
Thanks for your help! I will keep trying!
Okay, I think I got it fixed. - Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal varField As Variant) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
Loop Until intPos = 0
-
If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
-
-
End Function
I only need to show the article if the last keyword typed into the search box is a match. For instance, if I again use "distillation, column" in the search box, each article will be searched for the first keyword and then the second keyword. Well, if the second keyword is a match then the article will show, BUT if the second keyword is not a match then the article will not show. I did this by putting the If Then statement outside the Do Loop.
I will keep testing it, but I think it's right!
Thanks again for your help!
Okay, I think I got it fixed. - Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal varField As Variant) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
Loop Until intPos = 0
-
If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
-
-
End Function
I only need to show the article if the last keyword typed into the search box is a match. For instance, if I again use "distillation, column" in the search box, each article will be searched for the first keyword and then the second keyword. Well, if the second keyword is a match then the article will show, BUT if the second keyword is not a match then the article will not show. I did this by putting the If Then statement outside the Do Loop.
I will keep testing it, but I think it's right!
Thanks again for your help!
NOOOO! It doesn't work! I tried switching the keywords' order from "distillation, column" to "column, distillation." Well, some articles only have "distillation" as a keyword. So, since the code only chooses based on the last keyword, the query shows only the articles for the last keyword. I don't know why I thought it would work. I guess I jumped the gun :(
But, I will keep trying!
NOOOO! It doesn't work! I tried switching the keywords' order from "distillation, column" to "column, distillation." Well, some articles only have "distillation" as a keyword. So, since the code only chooses based on the last keyword, the query shows only the articles for the last keyword. I don't know why I thought it would work. I guess I jumped the gun :(
But, I will keep trying!
To step through your code you need to set a breakpoint - press F9 on a line of code (or Menu bar, Debug, Toggle Breakpoint. Or click in the vertical grey bar to the left of the code line), a brown spot will appear in the vertical grey bar and the line of code will be highlighted brown.
When the code is executed, it will run until the breakpoint is encountered.
From that point you can step through line by line, using F8 (other options available from Debug menu). You can see the values of your variables by hovering over their name with your mouse pointer or by right-clicking and selecting Add Watch.
This way you can see exactly what is happening and why.
Often you will want to stop the code - Menu bar, Run, Reset (or Reset button - looks like a VCR stop button, i.e. solid square)
Other times you will want the code to run without prompting - Menu bar, Run, Continue (F5), (or Continue button - VCR play)
Back to your code:
By placing the test for keywords outside of the loop, you will only be testing the current, i.e. last keyword, effectively ignoring all the others.
Testing inside the loop compares every keyword in order. - If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
-
Loop Until intPos = 0
Will set the comparison to True for ANY keyword match - If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
-
Loop Until intPos = 0
-
KeyWordsInStr = True
Will set the comparison to False for the first keyword that IS NOT found.
If you are using the function in it's intended manor, then it will be called for every record in your table.
Note that the field containing your keywords must contain all of the keywords, and not a separate record for each keyword.
E.g.
Keywords = "Distillation Column Retort Fraction"
Article = 4
Not:
Keyword = "Distillation", Article = 4
Keyword = "Column", Article = 4
etc
Hopefully that clarifies a few points.
To step through your code you need to set a breakpoint - press F9 on a line of code (or Menu bar, Debug, Toggle Breakpoint. Or click in the vertical grey bar to the left of the code line), a brown spot will appear in the vertical grey bar and the line of code will be highlighted brown.
When the code is executed, it will run until the breakpoint is encountered.
From that point you can step through line by line, using F8 (other options available from Debug menu). You can see the values of your variables by hovering over their name with your mouse pointer or by right-clicking and selecting Add Watch.
This way you can see exactly what is happening and why.
Often you will want to stop the code - Menu bar, Run, Reset (or Reset button - looks like a VCR stop button, i.e. solid square)
Other times you will want the code to run without prompting - Menu bar, Run, Continue (F5), (or Continue button - VCR play)
Back to your code:
By placing the test for keywords outside of the loop, you will only be testing the current, i.e. last keyword, effectively ignoring all the others.
Testing inside the loop compares every keyword in order. - If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
-
Loop Until intPos = 0
Will set the comparison to True for ANY keyword match - If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
-
Loop Until intPos = 0
-
KeyWordsInStr = True
Will set the comparison to False for the first keyword that IS NOT found.
If you are using the function in it's intended manor, then it will be called for every record in your table.
Note that the field containing your keywords must contain all of the keywords, and not a separate record for each keyword.
E.g.
Keywords = "Distillation Column Retort Fraction"
Article = 4
Not:
Keyword = "Distillation", Article = 4
Keyword = "Column", Article = 4
etc
Hopefully that clarifies a few points.
So, I shouldn't have a join table with a composite key as described in my first post of this discussion? I should have all keywords in the same field for each article/record? I tried that at first, but I was posting on another discussion, and someone told me that my tables weren't normalized. He/She said that I should use a composite key in a join table. I understand the code now, but I am confused about my tables.
Thanks for your help!
So, I shouldn't have a join table with a composite key as described in my first post of this discussion? I should have all keywords in the same field for each article/record? I tried that at first, but I was posting on another discussion, and someone told me that my tables weren't normalized. He/She said that I should use a composite key in a join table. I understand the code now, but I am confused about my tables.
Thanks for your help!
Can you post a link to the other discussion for me please?
http://www.thescripts.com/forum/thre...arch+form.html - post #7
OK.
The code worked on my database because I am searching the equivalent of the text of the Article rather than a table of keywords. My text is only a few lines long for each record. Your situation is obviously much larger.
Anyway, I have worked out a method that should help you.
It requires a second Public Function to count the number of keywords to search for and combines that with the number of keywords found (using the ANY search, not the ALL search). So, if there are 2 keywords to find, but the article only contains 1 of them, that article is discarded. - Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal varField As Variant) As Boolean
-
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
-
Loop Until intPos = 0
-
-
End Function
- Public Function CountKeywords(ByVal strKeyWords As String) As Integer
-
Dim intPos As Integer
-
-
CountKeywords = 0
-
If IsNull(strKeyWords) Then Exit Function
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
CountKeywords = CountKeywords + 1
-
Else
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
CountKeywords = CountKeywords + 1
-
End If
-
Loop Until intPos = 0
-
-
End Function
Your SQL then becomes something like - SELECT [your_fields]
-
FROM
-
(tblLitCategories INNER JOIN tblLiteratureArticles ON
-
tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation)
-
INNER JOIN tblArticleKeyword ON
-
tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
-
WHERE
-
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True
-
AND tblLitCategories.Selected=True
-
GROUP BY [your_fields]
-
HAVING (((Count(tblArticleKeyword.[Article ID])) = CountKeywords([Forms]![Form1]![txtKeywords])));
Substitute [your_fields] with a list of the fields you want to see, make sure you duplicate that list in the GROUP BY clause, otherwise you will see error messages about fields not being included as part of an aggregate function.
Hopefully you should be really close now.
OK.
The code worked on my database because I am searching the equivalent of the text of the Article rather than a table of keywords. My text is only a few lines long for each record. Your situation is obviously much larger.
Anyway, I have worked out a method that should help you.
It requires a second Public Function to count the number of keywords to search for and combines that with the number of keywords found (using the ANY search, not the ALL search). So, if there are 2 keywords to find, but the article only contains 1 of them, that article is discarded. - Public Function CountKeywords(ByVal strKeyWords As String) As Integer
-
Dim intPos As Integer
-
-
CountKeywords = 0
-
If IsNull(strKeyWords) Then Exit Function
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
CountKeywords = CountKeywords + 1
-
Else
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
CountKeywords = CountKeywords + 1
-
End If
-
Loop Until intPos = 0
-
-
End Function
Your SQL then becomes something like - SELECT [your_fields]
-
FROM
-
(tblLitCategories INNER JOIN tblLiteratureArticles ON
-
tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation)
-
INNER JOIN tblArticleKeyword ON
-
tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
-
WHERE
-
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True
-
AND tblLitCategories.Selected=True
-
GROUP BY [your_fields]
-
HAVING (((Count(tblArticleKeyword.[Article ID])) = CountKeywords([Forms]![Form1]![txtKeywords])));
Substitute [your_fields] with a list of the fields you want to see, make sure you duplicate that list in the GROUP BY clause, otherwise you will see error messages about fields not being included as part of an aggregate function.
Hopefully you should be really close now.
Thanks! It works fine when searching for just one keyword, but nothing appears when searching for more than one. When I view the query in datasheet view, nothing appears at all, not even an empty row. However, I do feel like I'm much closer to solving this problem. I will keep working on it!
Thanks again for your help!
Hi, mforema.
Try the following code/SQL. -
Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal lngArticleID As Long) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
-
"ArticleID=" & lngArticleID & " AND KeyWord='" & strKeyWord & "'")) Then
-
Exit Function
-
End If
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
-
End Function
-
-
SELECT tblLiteratureArticles.*
-
FROM tblLiteratureArticles
-
WHERE KeyWordsInStr([Forms]![Form1]![txtKeywords],[tblLiteratureArticles]![ArticleID]);
-
Actually it is not a very good solution bkz it invokes multiple times DLookUp function which is rather slow. On large number of records performance supposed to be low, but at least it works.
Hi, mforema.
Try the following code/SQL. -
Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal lngArticleID As Long) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
-
"ArticleID=" & lngArticleID & " AND KeyWord='" & strKeyWord & "'")) Then
-
Exit Function
-
End If
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
-
End Function
-
-
SELECT tblLiteratureArticles.*
-
FROM tblLiteratureArticles
-
WHERE KeyWordsInStr([Forms]![Form1]![txtKeywords],[tblLiteratureArticles]![ArticleID]);
-
Actually it is not a very good solution bkz it invokes multiple times DLookUp function which is rather slow. On large number of records performance supposed to be low, but at least it works.
It's not working. It's running into the same problems as before. It only works with one keyword. When it does show results, it shows multiple results, because tblArticleKeyword consists of the composite key where each ArticleID is joined with multiple keywords, like
ArticleID Keyword
Article 4 - distillation
Article 4 - column
Article 5 - reflux
Article 5 - distillation
Thanks for your help, though!
It's not working. It's running into the same problems as before. It only works with one keyword. When it does show results, it shows multiple results, because tblArticleKeyword consists of the composite key where each ArticleID is joined with multiple keywords, like
ArticleID Keyword
Article 4 - distillation
Article 4 - column
Article 5 - reflux
Article 5 - distillation
Thanks for your help, though!
I've tested this code on a sample db with the table structure you've posted in msg#1. The names are slightly different but certainly this doesn't make difference in the solution logic. The query does retrieve records from tblLiteratureArticles having records in tblArticleKeywords for all keywords in search string. tblArticles
keyArticleID(Long,Autonumber,PK); txtTitle(Text)
1 Article1
2 Article2
3 Article3
4 Article4 tblKeyWords
keyKeyWord(Text,PK)
Column
Distillation
Plate
Reflux tblArticleKeywords (both fields are members of composite PK)
keyArticleID(FK(tblArticles); keyKeyWord(FK(tblKeyWords))
Article1 Column
Article1 Distillation
Article2 Distillation
Article2 Reflux
Article3 Column
Article3 Distillation
Article3 Reflux
Article4 Column
Article4 Distillation VBA -
Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal lngArticleID As Long) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If IsNull(DLookup("keyArticleID", "tblArticleKeyWords", _
-
"keyArticleID=" & lngArticleID & " AND keyKeyWord='" & strKeyWord & "'")) Then
-
Exit Function
-
End If
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
-
End Function
-
-
Query -
SELECT tblArticles.*
-
FROM tblArticles
-
WHERE KeyWordsInStr("Column,Distillation",[tblArticles]![keyArticleID]);
-
returns
keyArticleID txtTitle
1 Article1
3 Article3
4 Article4
Is this what do you want?
I've tested this code on a sample db with the table structure you've posted in msg#1. The names are slightly different but certainly this doesn't make difference in the solution logic. The query does retrieve records from tblLiteratureArticles having records in tblArticleKeywords for all keywords in search string. tblArticles
keyArticleID(Long,Autonumber,PK); txtTitle(Text)
1 Article1
2 Article2
3 Article3
4 Article4 tblKeyWords
keyKeyWord(Text,PK)
Column
Distillation
Plate
Reflux tblArticleKeywords (both fields are members of composite PK)
keyArticleID(FK(tblArticles); keyKeyWord(FK(tblKeyWords))
Article1 Column
Article1 Distillation
Article2 Distillation
Article2 Reflux
Article3 Column
Article3 Distillation
Article3 Reflux
Article4 Column
Article4 Distillation VBA -
Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal lngArticleID As Long) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If IsNull(DLookup("keyArticleID", "tblArticleKeyWords", _
-
"keyArticleID=" & lngArticleID & " AND keyKeyWord='" & strKeyWord & "'")) Then
-
Exit Function
-
End If
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
-
End Function
-
-
Query -
SELECT tblArticles.*
-
FROM tblArticles
-
WHERE KeyWordsInStr("Column,Distillation",[tblArticles]![keyArticleID]);
-
returns
keyArticleID txtTitle
1 Article1
3 Article3
4 Article4
Is this what do you want?
I feel like such an idiot...Yes, the code and the SQL work fine. I didn't paste the SQL exactly. Also, I've been trying to test the code with "distillation, column", when "column" is not even a keyword - "columns" with an 's' is a keyword. Some of my other search forms use a code that searches for partial matches - like "dist" will bring up "distillation" and "distillate." I'm used to typing in partial words to test my code. Thanks so much! Yes, the code works! I'd like users to be able to type in partial words, though. Is there a way to use 'Like' and wildcards to do that with the code that you wrote?
Again, thanks for your help, and I am sorry for the confusion.
I feel like such an idiot...Yes, the code and the SQL work fine. I didn't paste the SQL exactly. Also, I've been trying to test the code with "distillation, column", when "column" is not even a keyword - "columns" with an 's' is a keyword. Some of my other search forms use a code that searches for partial matches - like "dist" will bring up "distillation" and "distillate." I'm used to typing in partial words to test my code. Thanks so much! Yes, the code works! I'd like users to be able to type in partial words, though. Is there a way to use 'Like' and wildcards to do that with the code that you wrote?
Again, thanks for your help, and I am sorry for the confusion.
Ok. You can easily modify code to be able to use partial keywords.
Here is modified function code. -
Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal lngArticleID As Long) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If IsNull(DLookup("ArticleID", "tblArticleKeyWord", _
-
"ArticleID=" & lngArticleID & " AND KeyWord Like '*" & _
-
strKeyWord & "*'")) Then
-
Exit Function
-
End If
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
-
End Function
-
Ok. You can easily modify code to be able to use partial keywords.
Here is modified function code. -
Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal lngArticleID As Long) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = Trim(strKeyWords)
-
Else
-
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
-
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
-
End If
-
If IsNull(DLookup("ArticleID", "tblArticleKeyWord", _
-
"ArticleID=" & lngArticleID & " AND KeyWord Like '*" & _
-
strKeyWord & "*'")) Then
-
Exit Function
-
End If
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
-
End Function
-
Awesome! It works great!
Thanks again for your help!
Awesome! It works great!
Thanks again for your help!
Glad it was helpful.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Kunal |
last post by:
Hi,
I need some help on writing an update query to update "UnitsSold" field in
Products Table whenever I save a transaction. The transaction may contain
several "Subtransactions", one for each...
|
by: John |
last post by:
Hi - I am trying to perform a simple append query, with no luck. I
have a table (MktPrices) that has the following fields: BondID,
PriceDate, Price. The objective is to allow the user to input a...
|
by: radioman |
last post by:
Hi all, I would appreciate some help please.
I just need pointing in the right direction as I am at a loss.
Basically I have a form (frmAddMaster) which displays two subforms
"Master Stock...
|
by: Josh Felker |
last post by:
Hey everyone.
I have a daily log form with my investing info. In that form I have 2
subforms, the first shows my profits on all the stocks I traded that day,
the second shows my profits on...
|
by: Laura |
last post by:
Here's the situation:
I'm trying to use an update query to copy data from one row to another.
Here is the situation:
I have 5 companies that are linked to each other. I need to show all 5...
|
by: alwayshouston |
last post by:
Hi All!
I am working on this very small database and I am confused in the
designing a simple form. I only have three tables in the database.
First Table: tblExpense
Columns: ExpenseID ;...
|
by: Ron |
last post by:
Hi All,
I've got a form called frmCust that has a subform on it called sfrmBalance.
sfrmBalance's control source is a query called qryTrans4CustBal.
sfrmBalance has one field on it called...
|
by: mcmahonb |
last post by:
Hey people...
I've been searching this forum for a few hours and even though this topic has been went over from many different angles; I cannot seem to figure out how to make things work on my...
|
by: samdev |
last post by:
I have created two combo boxes in a subform....
For example
1. Combo Box State
2. Combo Box City
3. When a state from the Combo Box State is selected, the City combo
box updates to reflect...
|
by: adigga1 |
last post by:
Good Day Experts,
I have an issue dealing with a subform operation whereby, each time I select the value field of the subform, that same value is then placed into the source table as a new...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |