More query questions re: keywords

[Guest]

I posted on 8/9 & again, want to thank Rick B. The information helped, but I
am still struggling to get teh syntax right. Through reading other posts I
have figured out a little SQL, but I'm obviously still lacking...this is the
query I have in place:

SELECT Data.Product, Data.Category, Data.Question, Data.[Q Effective Date],
Data.Answer, Data.[A Effective Date], Data.Owner, Data.Status
FROM Data
WHERE (((Data.Question) Like "*" & [Enter Keyword] & "*") AND
((Data.Status)="active")) OR (((Data.Question) Like "*" & [Enter 2nd Keyword]
& "*")) Is Null OR (((Data.Question) Like "*" & [Enter 3rd Keyword] & "*"))
Is Null OR (((Data.Question) Like "*" & [Enter 4th Keyword] & "*")) Is Null
OR (((Data.Question) Like "*" & [Enter 5th Keyword] & "*")) Is Null;

The problem I am having is that it is only searching on the first keyword
entered. If a user tries to search on more than one keyword, it pulls all
records in the Data table. I tried taking all but the last "Is Null" out,
but that didn't work either. What am I doing wrong???

Any help is greatly appreciated!

(As a side question, our network had server issues & now some of my queries
that were working are not now. I have checked the syntax & some of them did
appear to be different, however, after fixing it back to what I had they
still aren't working properly. Has anyone encountered such a problem...it
was giving me error messages stating Access encountered problems & had to
shut down)?

Thank you!

Lori
(e-mail address removed)

 

[Duane Hookom]

Are the results from this query displaying in a form or report (as they
should) or is the query opened in datasheet view?

I detest parameter prompt queries since they have near zero flexibility or
quality user interface.

 

[John Spencer (MVP)]

Since I don't know what the logic is that you are trying to do, it is hard to
decide how your code is wrong.

I am going to guess that you want all records where Data.Status is equal to
"Active" and Data.Question contains one of up to five words. One method would
be to force an impossible value into the keyword parameters if nothing is
entered for the specific parameter.

You can do that using the NZ function to force string that will never exist.
You can also use an IIF statement to do something similar.

Using NZ:

SELECT Data.Product, Data.Category, Data.Question, Data.[Q Effective Date],
Data.Answer, Data.[A Effective Date], Data.Owner, Data.Status
FROM Data
Where (Data.Status = "Active) AND
( Data.Question Like "*" & NZ([Enter Keyword],"wysiwigxx") & "*" OR
Data.Question Like "*" & NZ([Enter 2nd Keyword],"wysiwigxx") & "*" OR
Data.Question Like "*" & NZ([Enter 3rd Keyword],"wysiwigxx") & "*" OR
Data.Question Like "*" & NZ([Enter 4th Keyword],"wysiwigxx") & "*" OR
Data.Question Like "*" & NZ([Enter 5th Keyword],"wysiwigxx") & "*" )

Using an IIF clause:

....
AND (
Data.Question Like IIF([Enter Keyword] is Null, "","*" & [Enter Keyword] & "*") OR
Date.Question Like IIF([Enter Keyword2] is Null, "","*" & [Enter Keyword2] &
"*") OR ...

Side Question:
You may have a corrupted database. Have you tried a compact and repair - AFTER
making a BACKUP copy of the data?

I posted on 8/9 & again, want to thank Rick B. The information helped, but I
am still struggling to get teh syntax right. Through reading other posts I
have figured out a little SQL, but I'm obviously still lacking...this is the
query I have in place:

SELECT Data.Product, Data.Category, Data.Question, Data.[Q Effective Date],
Data.Answer, Data.[A Effective Date], Data.Owner, Data.Status
FROM Data
WHERE (((Data.Question) Like "*" & [Enter Keyword] & "*") AND
((Data.Status)="active")) OR (((Data.Question) Like "*" & [Enter 2nd Keyword]
& "*")) Is Null OR (((Data.Question) Like "*" & [Enter 3rd Keyword] & "*"))
Is Null OR (((Data.Question) Like "*" & [Enter 4th Keyword] & "*")) Is Null
OR (((Data.Question) Like "*" & [Enter 5th Keyword] & "*")) Is Null;

The problem I am having is that it is only searching on the first keyword
entered. If a user tries to search on more than one keyword, it pulls all
records in the Data table. I tried taking all but the last "Is Null" out,
but that didn't work either. What am I doing wrong???

Any help is greatly appreciated!

(As a side question, our network had server issues & now some of my queries
that were working are not now. I have checked the syntax & some of them did
appear to be different, however, after fixing it back to what I had they
still aren't working properly. Has anyone encountered such a problem...it
was giving me error messages stating Access encountered problems & had to
shut down)?

Thank you!

Lori
(e-mail address removed)

 

[Guest]

Try this --

WHERE (((Data.Question) Like "*" & [Enter Keyword] & "*") AND
((Data.Status)="active")) OR (((Data.Question) Like "*" & [Enter 2nd Keyword]
& "*" Or (Data.Question) Is Null)) OR (((Data.Question) Like "*" & [Enter 3rd
Keyword] & "*" Or (Data.Question) Is Null)) OR (((Data.Question) Like "*" &
[Enter 4th Keyword] & "*" Or (Data.Question) Is Null)) OR (((Data.Question)
Like "*" & [Enter 5th Keyword] & "*" Or (Data.Question) Is Null));

 

[Guest]

Duane,

For now I have it opening in datasheet view. Ultimately I want to run a
report off the query & attach the report to a switchboard to make it easy on
the ultimate end-user (and to prevent them from "playing" with anything
behind the scenes. I agree that the paramenter queries are not as
user-friendly as I would like, but I feel that it's probably the best way to
set it up for those that have never worked in access...

Thank you!

Lori

 

[Guest]

Karl,

Thank you for your respone. I tried that & it still pulls all records if
any of the 5 prompts are left blank. I KNOW there is a way to do this, I
just can't seem to find the magic syntax!

Have a great day!

Lori

Try this --

WHERE (((Data.Question) Like "*" & [Enter Keyword] & "*") AND
((Data.Status)="active")) OR (((Data.Question) Like "*" & [Enter 2nd Keyword]
& "*" Or (Data.Question) Is Null)) OR (((Data.Question) Like "*" & [Enter 3rd
Keyword] & "*" Or (Data.Question) Is Null)) OR (((Data.Question) Like "*" &
[Enter 4th Keyword] & "*" Or (Data.Question) Is Null)) OR (((Data.Question)
Like "*" & [Enter 5th Keyword] & "*" Or (Data.Question) Is Null));

I posted on 8/9 & again, want to thank Rick B. The information helped, but I
am still struggling to get teh syntax right. Through reading other posts I
have figured out a little SQL, but I'm obviously still lacking...this is the
query I have in place:

SELECT Data.Product, Data.Category, Data.Question, Data.[Q Effective Date],
Data.Answer, Data.[A Effective Date], Data.Owner, Data.Status
FROM Data
WHERE (((Data.Question) Like "*" & [Enter Keyword] & "*") AND
((Data.Status)="active")) OR (((Data.Question) Like "*" & [Enter 2nd Keyword]
& "*")) Is Null OR (((Data.Question) Like "*" & [Enter 3rd Keyword] & "*"))
Is Null OR (((Data.Question) Like "*" & [Enter 4th Keyword] & "*")) Is Null
OR (((Data.Question) Like "*" & [Enter 5th Keyword] & "*")) Is Null;

The problem I am having is that it is only searching on the first keyword
entered. If a user tries to search on more than one keyword, it pulls all
records in the Data table. I tried taking all but the last "Is Null" out,
but that didn't work either. What am I doing wrong???

Any help is greatly appreciated!

(As a side question, our network had server issues & now some of my queries
that were working are not now. I have checked the syntax & some of them did
appear to be different, however, after fixing it back to what I had they
still aren't working properly. Has anyone encountered such a problem...it
was giving me error messages stating Access encountered problems & had to
shut down)?

Thank you!

Lori
(e-mail address removed)

 

[Duane Hookom]

Parameter queries are nearly the worst interface for "those that have never
worked in Access".

A form with several text boxes to enter key words is much better. Since you
are opening a report, you can use code like:

Dim strWhere as String
' keywords are entered into text boxes with names like "txtKW1",
"txtKW2",...

If Not IsNull(Me.txtKW1) Then
strWhere = strWhere & "[Question] Like ""*" & _
Me.txtKW1 & "*"" or "
End If

If Not IsNull(Me.txtKW2) Then
strWhere = strWhere & "[Question] Like ""*" & _
Me.txtKW2 & "*"" or "
End If

If Not IsNull(Me.txtKW3) Then
strWhere = strWhere & "[Question] Like ""*" & _
Me.txtKW3 & "*"" or "
End If

'add code for more text boxes
' looping code could be created to make this more maintainable

If Len(strWhere)>5 Then
'get rid of last " or "
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
DoCmd.OpenReport "rptYourReport",acPreview, , strWhere

 

[Guest]

John,

You are correct. I want it to only search active records. From that point,
I want the end-user to have the ability to enter up to 5 keywords (yet have
the option to enter only 1, 2, 3, or 4 as well). With my original syntax it
appears it was only searching on the first keyword. If more than one keyword
was entered, the query returned all of the active records rather than those
containing only the keyword(s) entered. I tried using your IIF syntax below
& it gave me an error message that I needed (). So I modified to enclose
each phrase (so I thought), as follows:

WHERE (((Data.Question) Like "*" & [Enter Keyword] & "*") AND
((Data.Status)="active")) OR (((Data.Question) Like IIF ( "*" & [Enter 2nd
Keyword] & "*" Or (Data.Question) Is Null))) OR (((Data.Question) Like IIF
("*" & [Enter 3rd Keyword] & "*" Or (Data.Question) Is Null))) OR
(((Data.Question) Like IIF ("*" & [Enter 4th Keyword] & "*" Or
(Data.Question) Is Null))) OR (((Data.Question) Like IIF ("*" & [Enter 5th
Keyword] & "*" Or (Data.Question) Is Null)));

This returned the following error message:

"Wrong number of arguments used with function in query expression '[my
syntax]'"

I then tried the NZ method as you suggested below & got an error message. I
tried a few variations and am still getting syntax error messages. This has
to be getting close!

In answer to your side question, I have saved multiple versions, compacted,
repaired, and can work for a while but it sometimes shuts down anyway. As of
yesterday, I started a brand new database from scratch. I re-created my
table in Excel (to keep it free from any possible corruption) and imported it
into the clean database. I was able to recreate my queries (though I still
can't seem to get the more complicated ones working correctly again, so I
went to plan B workarounds). When I started a new report (using the wizard)
I got to the last step & it shut me down. I am familiar with a database
being corrupted, but this was a brand new one with no copy/paste from
previously corrupted (I'm assuming) versions. Is it possible for Access
itself to be corrupted? The program does not seem to want to work with any
reports...I'm at a loss here. I was nearly done & since Friday's server
issues I can't seem to make any progress!

 

[Guest]

Duane,

I'm willing to try this, but I'm still confused. I was planning to run a
report from my parameter query. To do as you suggest, would I be putting
this code in SQL view (from design view) in the query like I had been doing
(or is that visual basic)? I'm thinking I would still need to create the
query first, then the form, then the report. I would link the form to the
query, then the report to the form? Where does the code you provided go...in
the form or the report? I'm still a pretty basic user. I can create a
database from scratch, but when it comes to advanced programming I get
lost...thanks for your patience with a non-programmer!

Parameter queries are nearly the worst interface for "those that have never
worked in Access".

A form with several text boxes to enter key words is much better. Since you
are opening a report, you can use code like:

Dim strWhere as String
' keywords are entered into text boxes with names like "txtKW1",
"txtKW2",...

If Not IsNull(Me.txtKW1) Then
strWhere = strWhere & "[Question] Like ""*" & _
Me.txtKW1 & "*"" or "
End If

If Not IsNull(Me.txtKW2) Then
strWhere = strWhere & "[Question] Like ""*" & _
Me.txtKW2 & "*"" or "
End If

If Not IsNull(Me.txtKW3) Then
strWhere = strWhere & "[Question] Like ""*" & _
Me.txtKW3 & "*"" or "
End If

'add code for more text boxes
' looping code could be created to make this more maintainable

If Len(strWhere)>5 Then
'get rid of last " or "
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
DoCmd.OpenReport "rptYourReport",acPreview, , strWhere

--
Duane Hookom
MS Access MVP
--

Duane,

For now I have it opening in datasheet view. Ultimately I want to run a
report off the query & attach the report to a switchboard to make it easy
on
the ultimate end-user (and to prevent them from "playing" with anything
behind the scenes. I agree that the paramenter queries are not as
user-friendly as I would like, but I feel that it's probably the best way
to
set it up for those that have never worked in access...

Thank you!

Lori

 

[John Spencer (MVP)]

I left out a closing quote mark in the query after the word "Active". Did you
fix that?

SELECT Data.Product, Data.Category, Data.Question, Data.[Q Effective Date],
Data.Answer, Data.[A Effective Date], Data.Owner, Data.Status
FROM Data
Where (Data.Status = "Active") AND
( Data.Question Like "*" & NZ([Enter Keyword],"wysiwigxx") & "*" OR
Data.Question Like "*" & NZ([Enter 2nd Keyword],"wysiwigxx") & "*" OR
Data.Question Like "*" & NZ([Enter 3rd Keyword],"wysiwigxx") & "*" OR
Data.Question Like "*" & NZ([Enter 4th Keyword],"wysiwigxx") & "*" OR
Data.Question Like "*" & NZ([Enter 5th Keyword],"wysiwigxx") & "*" )


As far as your database problem, I am shooting in the dark. Have you tried
assigning a new printer as the default printer? You can switch back to the
original printer after you have done the assignment and saved it.

Otherwise, I suggest you start a new thread on that problem.

John,

You are correct. I want it to only search active records. From that point,
I want the end-user to have the ability to enter up to 5 keywords (yet have
the option to enter only 1, 2, 3, or 4 as well). With my original syntax it
appears it was only searching on the first keyword. If more than one keyword
was entered, the query returned all of the active records rather than those
containing only the keyword(s) entered. I tried using your IIF syntax below
& it gave me an error message that I needed (). So I modified to enclose
each phrase (so I thought), as follows:

WHERE (((Data.Question) Like "*" & [Enter Keyword] & "*") AND
((Data.Status)="active")) OR (((Data.Question) Like IIF ( "*" & [Enter 2nd
Keyword] & "*" Or (Data.Question) Is Null))) OR (((Data.Question) Like IIF
("*" & [Enter 3rd Keyword] & "*" Or (Data.Question) Is Null))) OR
(((Data.Question) Like IIF ("*" & [Enter 4th Keyword] & "*" Or
(Data.Question) Is Null))) OR (((Data.Question) Like IIF ("*" & [Enter 5th
Keyword] & "*" Or (Data.Question) Is Null)));

This returned the following error message:

"Wrong number of arguments used with function in query expression '[my
syntax]'"

I then tried the NZ method as you suggested below & got an error message. I
tried a few variations and am still getting syntax error messages. This has
to be getting close!

In answer to your side question, I have saved multiple versions, compacted,
repaired, and can work for a while but it sometimes shuts down anyway. As of
yesterday, I started a brand new database from scratch. I re-created my
table in Excel (to keep it free from any possible corruption) and imported it
into the clean database. I was able to recreate my queries (though I still
can't seem to get the more complicated ones working correctly again, so I
went to plan B workarounds). When I started a new report (using the wizard)
I got to the last step & it shut me down. I am familiar with a database
being corrupted, but this was a brand new one with no copy/paste from
previously corrupted (I'm assuming) versions. Is it possible for Access
itself to be corrupted? The program does not seem to want to work with any
reports...I'm at a loss here. I was nearly done & since Friday's server
issues I can't seem to make any progress!

Since I don't know what the logic is that you are trying to do, it is hard to
decide how your code is wrong.

I am going to guess that you want all records where Data.Status is equal to
"Active" and Data.Question contains one of up to five words. One method would
be to force an impossible value into the keyword parameters if nothing is
entered for the specific parameter.

You can do that using the NZ function to force string that will never exist.
You can also use an IIF statement to do something similar...

...Side Question:
You may have a corrupted database. Have you tried a compact and repair - AFTER
making a BACKUP copy of the data?

 

[Duane Hookom]

The code that I provided would be in some event code such as the On Click
event of a command button that would open the report. You can use the wizard
to create the command button and its code. Then modify the code as I
suggested.

This would mean the report's recordsource query would not have much for
criteria since the filtering would be provided by the where clause of the
DoCmd.OpenReport method.

--
Duane Hookom
MS Access MVP
--

Duane,

I'm willing to try this, but I'm still confused. I was planning to run a
report from my parameter query. To do as you suggest, would I be putting
this code in SQL view (from design view) in the query like I had been
doing
(or is that visual basic)? I'm thinking I would still need to create the
query first, then the form, then the report. I would link the form to the
query, then the report to the form? Where does the code you provided
go...in
the form or the report? I'm still a pretty basic user. I can create a
database from scratch, but when it comes to advanced programming I get
lost...thanks for your patience with a non-programmer!

Parameter queries are nearly the worst interface for "those that have
never
worked in Access".

A form with several text boxes to enter key words is much better. Since
you
are opening a report, you can use code like:

Dim strWhere as String
' keywords are entered into text boxes with names like "txtKW1",
"txtKW2",...

If Not IsNull(Me.txtKW1) Then
strWhere = strWhere & "[Question] Like ""*" & _
Me.txtKW1 & "*"" or "
End If

If Not IsNull(Me.txtKW2) Then
strWhere = strWhere & "[Question] Like ""*" & _
Me.txtKW2 & "*"" or "
End If

If Not IsNull(Me.txtKW3) Then
strWhere = strWhere & "[Question] Like ""*" & _
Me.txtKW3 & "*"" or "
End If

'add code for more text boxes
' looping code could be created to make this more maintainable

If Len(strWhere)>5 Then
'get rid of last " or "
strWhere = Left(strWhere, Len(strWhere) - 4)
End If
DoCmd.OpenReport "rptYourReport",acPreview, , strWhere

--
Duane Hookom
MS Access MVP
--

Duane,

For now I have it opening in datasheet view. Ultimately I want to run
a
report off the query & attach the report to a switchboard to make it
easy
on
the ultimate end-user (and to prevent them from "playing" with anything
behind the scenes. I agree that the paramenter queries are not as
user-friendly as I would like, but I feel that it's probably the best
way
to
set it up for those that have never worked in access...

Thank you!

Lori