Help with a QBF

[MichaelPReid]

I am developing a query by form to search through multiple fields of a datasheet, but am running into a snag. I would like to have one parameter, [Forms]![QBF_Form]![RepName], from the form be used to search through two similar fields, ByLine1 and ByLine2, in the query.

When I set "Like "*" & [Forms]![QBF_Form]![RepName] & "*" OR [Forms]![QBF_Form]![RepName] is NULL" as the criteria field for ByLine1, the query runs perfectly. But when I add the same command into the criteria field for ByLine2, the result is a query with no records. ByLine1 and ByLine2 are text fields drawn from the same list of names and the form field is a combo box based on that list.

What am I missing? Thank you

 

[djwatts]

Are you using 'OR' or 'AND' between the two field criterias? Have a look in your SQL view and check this, sounds like its searching for:-

ByLine1 = Like "*" & [Forms]![QBF_Form]![RepName] & "*" OR [Forms]![QBF_Form]![RepName] is NULL

AND [rather than OR]

ByLine2 = Like "*" & [Forms]![QBF_Form]![RepName] & "*" OR [Forms]![QBF_Form]![RepName] is NULL

 

[AlexCuse]

Are you entering the second criteria in the OR line? I suspect you haven't, so it is looking fo ByLine1 like so and so AND ByLine2 like so and so.

Can you post your SQL code, that is usually the best way to get query help.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[MichaelPReid]

Thank you. That seems to have done the trick. Though, I did need to move the OR expression to to the end of the string. DOES an OR statement like that affect the syntax of the statement?

SELECT Clippings.Date, Clippings.Headline, Clippings.Source, Clippings.Byline1, Clippings.Byline2, Clippings.ArticleText

FROM Clippings

WHERE (((Clippings.Date) Between [Forms]![QBF_Form]![BDate] And [Forms]![QBF_Form]![EDate] Or [Forms]![QBF_Form]![BDate] Is Null)

AND ((Clippings.Headline) Like "*" & [Forms]![QBF_Form]![HeadWord] & "*" Or [Forms]![QBF_Form]![HeadWord] Is Null)

AND ((Clippings.Source) Like "*" & [Forms]![QBF_Form]![PubName] & "*" Or [Forms]![QBF_Form]![PubName] Is Null)

AND ((Clippings.Byline1) Like "*" & [Forms]![QBF_Form]![RepName] & "*" Or [Forms]![QBF_Form]![RepName] Is Null)

AND ((Clippings.ArticleText) Like "*" & [Forms]![QBF_Form]![TextWord] & "*" OR [Forms]![QBF_Form]![TextWord] is Null)

OR ((Clippings.Byline2) Like "*" & [Forms]![QBF_Form]![RepName] & "*" Or [Forms]![QBF_Form]![RepName] Is Null))

ORDER BY Clippings.Date, Clippings.Headline, Clippings.Source, Clippings.Byline1, Clippings.Byline2, Clippings.ArticleText

 

[AlexCuse]

When you post code, try using the [ignore]

 typing code here

[/ignore] tags. This will yield the result

 typing code here

And make your code much more readable. I think you need to make one minor change (not knowing really what you're looking for).

Add an extra pair of parentheses

WHERE [b]([/b](((Clippings.Date) Between [Forms]![QBF_Form]![BDate] And [Forms]![QBF_Form]![EDate] Or [Forms]![QBF_Form]![BDate] Is Null) 

AND ((Clippings.Headline) Like "*" & [Forms]![QBF_Form]![HeadWord] & "*" Or [Forms]![QBF_Form]![HeadWord] Is Null) 

AND ((Clippings.Source) Like "*" & [Forms]![QBF_Form]![PubName] & "*" Or [Forms]![QBF_Form]![PubName] Is Null) 

AND ((Clippings.Byline1) Like "*" & [Forms]![QBF_Form]![RepName] & "*" Or [Forms]![QBF_Form]![RepName] Is Null) 

AND ((Clippings.ArticleText) Like "*" & [Forms]![QBF_Form]![TextWord] & "*" OR [Forms]![QBF_Form]![TextWord] is Null)[b])[/b] 

OR ((Clippings.Byline2) Like "*" & [Forms]![QBF_Form]![RepName] & "*" Or [Forms]![QBF_Form]![RepName] Is Null))

Because I am thinking you want records where (ALL the conditions included inside the bolded parentheses are true) OR (Last condition is true).

I could be reading this wrong though?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[MichaelPReid]

Thanks for the tip and the help.

Actually, the desired result is not how you have described it. I do not want the (ALL the conditions included inside the bolded parentheses are true) OR (Last condition is true).

I would like to have all of the conditions be a part of the query, but I need some way to have [Forms]![QBF_Form]![RepName] be the value for both ByLine1 and ByLine2 simultaneously without interfering with the rest of the query. The end result is a query that searches through several designated fields, including a search for a name that can appear in either the ByLine1 or the ByLine 2 fields.

Would putting the parentheses around the Clippings.Byline1 and Clippings.ByLine2 expressions achieve this? Something like this:

SELECT Clippings.Date, Clippings.Headline, Clippings.Source, Clippings.Byline1, Clippings.Byline2, Clippings.ArticleText

FROM Clippings

WHERE (((Clippings.Date) Between [Forms]![QBF_Form]![BDate] And [Forms]![QBF_Form]![EDate] Or [Forms]![QBF_Form]![BDate] Is Null) 

AND ((Clippings.Headline) Like "*" & [Forms]![QBF_Form]![HeadWord] & "*" Or [Forms]![QBF_Form]![HeadWord] Is Null) 

AND ((Clippings.Source) Like "*" & [Forms]![QBF_Form]![PubName] & "*" Or [Forms]![QBF_Form]![PubName] Is Null) 

AND [b]([/b]((Clippings.Byline1) Like "*" & [Forms]![QBF_Form]![RepName] & "*" Or [Forms]![QBF_Form]![RepName] Is Null) 

OR ((Clippings.Byline2) Like "*" & [Forms]![QBF_Form]![RepName] & "*" Or [Forms]![QBF_Form]![RepName] Is Null))[b])[/b]

AND ((Clippings.ArticleText) Like "*" & [Forms]![QBF_Form]![TextWord] & "*" OR [Forms]![QBF_Form]![TextWord] is Null)?

Thanks again.

 

[AlexCuse]

Yes, that seems like it would do what you describe. I thought something was funny with your query before. Have you tested this? Let me know if it gets you teh desired result.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[MichaelPReid]

Yes. It does seem to be giving me the desired result. Thank you for your help.

Now I just need to figure our how to have the results output into a form instead of a datasheet. Any thoughts?

 

[PHV]

Create a query with the following SQL Code:

SELECT [Date], Headline, Source, Byline1, Byline2, ArticleText
FROM Clippings
WHERE ([Date] Between [Forms]![QBF_Form]![BDate] And [Forms]![QBF_Form]![EDate] Or [Forms]![QBF_Form]![BDate] Is Null)
AND (Headline Like '*' & [Forms]![QBF_Form]![HeadWord] & '*' Or [Forms]![QBF_Form]![HeadWord] Is Null) 
AND (Source Like '*' & [Forms]![QBF_Form]![PubName] & '*' Or [Forms]![QBF_Form]![PubName] Is Null) 
AND (Byline1 & Byline2 Like '*' & [Forms]![QBF_Form]![RepName] & '*' Or [Forms]![QBF_Form]![RepName] Is Null)
AND (ArticleText Like '*' & [Forms]![QBF_Form]![TextWord] & '*' OR [Forms]![QBF_Form]![TextWord] is Null)

Now follow the form wizard to create the form you want based on this query.
Then in your QBF_Form you may have a button opening the newly created form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886