part of multi search query non-functional

K

Krazy Darcy

In a database I have 2 name fields 1 for last name and 1 for first name.
FOr each field I have the following criteria:
Like [Forms]![searchscreen]![author] & "*"
Like "*" & [Forms]![searchscreen]![author] & "*"
Like "*" & [Forms]![searchscreen]![author]

And I have a column [Forms]![searchscreen]![author] that has
Is Null in the appropriate rows.
This is because I have a few other searchable fields as this is for a
searchform.

However I can't get the author part of the search to work. (title and
keyword (subject) work perfectly). All use exactly the same criteria's
(except for the [author] bit as appropriate)

The reason for splitting the author's name is so I can have it displayed
last,first or first,last as appropriate for a specific output ie book label,
list or whatever.
 
A

Allen Browne

That's right: Null doesn't match anything.

You will therefore need to craft the WHERE clause of your query so that it
returns True when the text box is Null:

1. Switch the query to SQL View.

2. Locate the WHERE clause.

3. Edit it like this:

WHERE (([Forms]![searchscreen]![author] Is Null)
OR ([LastName] Like "*" & [Forms]![searchscreen]![author] & "*")
OR (FirstName] Like "*" & [Forms]![searchscreen]![author] & "*"))
 
K

Krazy Darcy

Here is the sql for the search query:

SELECT [book details].group, [book details].[Record-number], [book
details].ISBN, [book details].[UPC/SKU/barcode], [book details].[Year
published], [book details].Publisher, authors.[Author last name],
authors.[Author First Name], subject.Subject, [book details].Title, [book
details].comments
FROM ([book details] INNER JOIN authors ON [book details].[Record-number] =
authors.[Record-number]) INNER JOIN subject ON [book details].[Record-number]
= subject.[Record-number]
WHERE (((subject.Subject) Like [Forms]![searchscreen]![keyword] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![title]) Is Null)) OR (((subject.Subject) Like "*" &
[Forms]![searchscreen]![keyword] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![title]) Is Null)) OR (((subject.Subject) Like "*" &
[Forms]![searchscreen]![keyword]) AND (([Forms]![searchscreen]![author]) Is
Null) AND (([Forms]![searchscreen]![title]) Is Null)) OR ((([book
details].Title) Like [Forms]![searchscreen]![title] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR ((([book details].Title)
Like "*" & [Forms]![searchscreen]![title] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR ((([book details].Title)
Like "*" & [Forms]![searchscreen]![title]) AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR (((authors.[Author last
name]) Like [Forms]![searchscreen]![author] & "*") AND
(([Forms]![searchscreen]![title]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR (((authors.[Author last
name]) Like "*" & [Forms]![searchscreen]![author] & "*") AND
(([Forms]![searchscreen]![title]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR (((authors.[Author last
name]) Like "*" & [Forms]![searchscreen]![author]) AND
(([Forms]![searchscreen]![title]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null));

[Forms]![searchscreen]![keyword] is used to search the subject field
[Forms]![searchscreen]![title] is used to search the title field
They work properly.
[Forms]![searchscreen]![author] is used to search both author first name and
Author last name fields. This is the part that doesn't work.
A user can enter a search value into any single or combination of these
three inputs on the search form.
Allen Browne said:
That's right: Null doesn't match anything.

You will therefore need to craft the WHERE clause of your query so that it
returns True when the text box is Null:

1. Switch the query to SQL View.

2. Locate the WHERE clause.

3. Edit it like this:

WHERE (([Forms]![searchscreen]![author] Is Null)
OR ([LastName] Like "*" & [Forms]![searchscreen]![author] & "*")
OR (FirstName] Like "*" & [Forms]![searchscreen]![author] & "*"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Krazy Darcy said:
In a database I have 2 name fields 1 for last name and 1 for first name.
FOr each field I have the following criteria:
Like [Forms]![searchscreen]![author] & "*"
Like "*" & [Forms]![searchscreen]![author] & "*"
Like "*" & [Forms]![searchscreen]![author]

And I have a column [Forms]![searchscreen]![author] that has
Is Null in the appropriate rows.
This is because I have a few other searchable fields as this is for a
searchform.

However I can't get the author part of the search to work. (title and
keyword (subject) work perfectly). All use exactly the same criteria's
(except for the [author] bit as appropriate)

The reason for splitting the author's name is so I can have it displayed
last,first or first,last as appropriate for a specific output ie book
label,
list or whatever.
Click to expand...
Click to expand...
 
A

Allen Browne

That's not really what was suggested.

If you can't take that approach (or even if you can), a more efficient
solution would be to leave the criteria out of the query, and build a filter
string from only the boxes where the user actually entered something.
Example in:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Krazy Darcy said:
Here is the sql for the search query:

SELECT [book details].group, [book details].[Record-number], [book
details].ISBN, [book details].[UPC/SKU/barcode], [book details].[Year
published], [book details].Publisher, authors.[Author last name],
authors.[Author First Name], subject.Subject, [book details].Title, [book
details].comments
FROM ([book details] INNER JOIN authors ON [book details].[Record-number]
=
authors.[Record-number]) INNER JOIN subject ON [book
details].[Record-number]
= subject.[Record-number]
WHERE (((subject.Subject) Like [Forms]![searchscreen]![keyword] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![title]) Is Null)) OR (((subject.Subject) Like
"*" &
[Forms]![searchscreen]![keyword] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![title]) Is Null)) OR (((subject.Subject) Like
"*" &
[Forms]![searchscreen]![keyword]) AND (([Forms]![searchscreen]![author])
Is
Null) AND (([Forms]![searchscreen]![title]) Is Null)) OR ((([book
details].Title) Like [Forms]![searchscreen]![title] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR ((([book details].Title)
Like "*" & [Forms]![searchscreen]![title] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR ((([book details].Title)
Like "*" & [Forms]![searchscreen]![title]) AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR (((authors.[Author last
name]) Like [Forms]![searchscreen]![author] & "*") AND
(([Forms]![searchscreen]![title]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR (((authors.[Author last
name]) Like "*" & [Forms]![searchscreen]![author] & "*") AND
(([Forms]![searchscreen]![title]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR (((authors.[Author last
name]) Like "*" & [Forms]![searchscreen]![author]) AND
(([Forms]![searchscreen]![title]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null));

[Forms]![searchscreen]![keyword] is used to search the subject field
[Forms]![searchscreen]![title] is used to search the title field
They work properly.
[Forms]![searchscreen]![author] is used to search both author first name
and
Author last name fields. This is the part that doesn't work.
A user can enter a search value into any single or combination of these
three inputs on the search form.
Allen Browne said:
That's right: Null doesn't match anything.

You will therefore need to craft the WHERE clause of your query so that
it
returns True when the text box is Null:

1. Switch the query to SQL View.

2. Locate the WHERE clause.

3. Edit it like this:

WHERE (([Forms]![searchscreen]![author] Is Null)
OR ([LastName] Like "*" & [Forms]![searchscreen]![author] & "*")
OR (FirstName] Like "*" & [Forms]![searchscreen]![author] & "*"))

Krazy Darcy said:
In a database I have 2 name fields 1 for last name and 1 for first
name.
FOr each field I have the following criteria:
Like [Forms]![searchscreen]![author] & "*"
Like "*" & [Forms]![searchscreen]![author] & "*"
Like "*" & [Forms]![searchscreen]![author]

And I have a column [Forms]![searchscreen]![author] that has
Is Null in the appropriate rows.
This is because I have a few other searchable fields as this is for a
searchform.

However I can't get the author part of the search to work. (title and
keyword (subject) work perfectly). All use exactly the same criteria's
(except for the [author] bit as appropriate)

The reason for splitting the author's name is so I can have it
displayed
last,first or first,last as appropriate for a specific output ie book
label,
list or whatever.
Click to expand...
Click to expand...
Click to expand...
 
K

Krazy Darcy

(I tried to send a post but got server too buisy eror message so didn't know
if it got sent so sending this one)

Thanks.

That was the sql code access generated in responce to what I had put in the
design screen.

Thanks for the link. I have bookmarked your site.
I have printed the sample code out which is well commented and wil try to do
it this way. If I don't get my lighting kit this weekend I should have it
basicly running by next week.

Thanks.

Allen Browne said:
That's not really what was suggested.

If you can't take that approach (or even if you can), a more efficient
solution would be to leave the criteria out of the query, and build a filter
string from only the boxes where the user actually entered something.
Example in:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Krazy Darcy said:
Here is the sql for the search query:

SELECT [book details].group, [book details].[Record-number], [book
details].ISBN, [book details].[UPC/SKU/barcode], [book details].[Year
published], [book details].Publisher, authors.[Author last name],
authors.[Author First Name], subject.Subject, [book details].Title, [book
details].comments
FROM ([book details] INNER JOIN authors ON [book details].[Record-number]
=
authors.[Record-number]) INNER JOIN subject ON [book
details].[Record-number]
= subject.[Record-number]
WHERE (((subject.Subject) Like [Forms]![searchscreen]![keyword] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![title]) Is Null)) OR (((subject.Subject) Like
"*" &
[Forms]![searchscreen]![keyword] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![title]) Is Null)) OR (((subject.Subject) Like
"*" &
[Forms]![searchscreen]![keyword]) AND (([Forms]![searchscreen]![author])
Is
Null) AND (([Forms]![searchscreen]![title]) Is Null)) OR ((([book
details].Title) Like [Forms]![searchscreen]![title] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR ((([book details].Title)
Like "*" & [Forms]![searchscreen]![title] & "*") AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR ((([book details].Title)
Like "*" & [Forms]![searchscreen]![title]) AND
(([Forms]![searchscreen]![author]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR (((authors.[Author last
name]) Like [Forms]![searchscreen]![author] & "*") AND
(([Forms]![searchscreen]![title]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR (((authors.[Author last
name]) Like "*" & [Forms]![searchscreen]![author] & "*") AND
(([Forms]![searchscreen]![title]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null)) OR (((authors.[Author last
name]) Like "*" & [Forms]![searchscreen]![author]) AND
(([Forms]![searchscreen]![title]) Is Null) AND
(([Forms]![searchscreen]![keyword]) Is Null));

[Forms]![searchscreen]![keyword] is used to search the subject field
[Forms]![searchscreen]![title] is used to search the title field
They work properly.
[Forms]![searchscreen]![author] is used to search both author first name
and
Author last name fields. This is the part that doesn't work.
A user can enter a search value into any single or combination of these
three inputs on the search form.
Allen Browne said:
That's right: Null doesn't match anything.

You will therefore need to craft the WHERE clause of your query so that
it
returns True when the text box is Null:

1. Switch the query to SQL View.

2. Locate the WHERE clause.

3. Edit it like this:

WHERE (([Forms]![searchscreen]![author] Is Null)
OR ([LastName] Like "*" & [Forms]![searchscreen]![author] & "*")
OR (FirstName] Like "*" & [Forms]![searchscreen]![author] & "*"))

In a database I have 2 name fields 1 for last name and 1 for first
name.
FOr each field I have the following criteria:
Like [Forms]![searchscreen]![author] & "*"
Like "*" & [Forms]![searchscreen]![author] & "*"
Like "*" & [Forms]![searchscreen]![author]

And I have a column [Forms]![searchscreen]![author] that has
Is Null in the appropriate rows.
This is because I have a few other searchable fields as this is for a
searchform.

However I can't get the author part of the search to work. (title and
keyword (subject) work perfectly). All use exactly the same criteria's
(except for the [author] bit as appropriate)

The reason for splitting the author's name is so I can have it
displayed
last,first or first,last as appropriate for a specific output ie book
label,
list or whatever.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple "Ors" in query 2
Search any field 10
query returns unwanted multiple occurances of records 5
Can't get subform to synch with Parent 3
Multi table query 1
Can't get sub to synch with Main 8
if statements in queries 9
Case Sensitive in a Criteria with Like 3

Top