part of multi search query non-functional

[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.

 

[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] & "*"))

 

[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.

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.

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.

 

[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.

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.

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.

 

[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.

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.

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.

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.