Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

[ChicagoPete]

Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB, Document Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the end-user may
only have 1 or more pieces of data to perform a search, they may also only
have a partial Last Name or worse an inproperly spelled last name - so I need
a few different scenarios to perform my query and my OR statements don't cut
it any more.

The user may type SMITH in the last name field, which returns a lot of data
and currently if they enter SMITH and JOE they get all the Smith's and all
the Joe's, due to my many OR statements. What is needed, and I can't seem to
get it right is the ability to search on any one and/or all fields. The user
may also type the first few characters of a last name. I guess I need a mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to search for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[Danny Lesandrini]

Pete:

I hate what Access QBE grid does to WHERE clause statements. Yours could be
rewritten like this, with the table name omitted, though that's only done
for clarity. It works either way, but of course purists will tell you to
include the table nam.

WHERE LastName =[forms]![frmSearch]![LastName]
OR FirstName =[forms]![frmSearch]![FirstName]
OR DOB =[forms]![frmSearch]![DateOfBirth]
OR DocumentType =[forms]![frmSearch]![DocumentType]
OR Location =[forms]![frmSearch]![Location]

To make this more flexible, you'll need the LIKE operator.

WHERE LastName Like "*" & [forms]![frmSearch]![LastName] & "*"
OR FirstName = [forms]![frmSearch]![FirstName]
OR DOB = [forms]![frmSearch]![DateOfBirth]
OR DocumentType = [forms]![frmSearch]![DocumentType]
OR Location = [forms]![frmSearch]![Location]

If you want to make the result set more restrictive, change all the OR
keywords to AND. In the QBE Grid this means putting them all on the same
line.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB, Document Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the end-user may
only have 1 or more pieces of data to perform a search, they may also only
have a partial Last Name or worse an inproperly spelled last name - so I
need
a few different scenarios to perform my query and my OR statements don't
cut
it any more.

The user may type SMITH in the last name field, which returns a lot of
data
and currently if they enter SMITH and JOE they get all the Smith's and all
the Joe's, due to my many OR statements. What is needed, and I can't seem
to
get it right is the ability to search on any one and/or all fields. The
user
may also type the first few characters of a last name. I guess I need a
mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to search
for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[Dale Fye]

Pete,

In this type of circumstance, I generally build my SQL strings dynamically.
I start out with a search form, which it looks like you already have, which
also includes a "filter","search", or "find next" button. Then, In the Click
event of that button, I use a series of statements to build my SQL statement.
Something like:

Private Sub Search_Click

Dim strSQL as string
Dim varCriteria as variant

strSQL = "SELECT LastName, FirstName, DOB, " _
& "DocumentType, Location " _
& "FROM tblMain"

varCriteria = NULL
if LEN(me.txtLastName & "") > 0 then
varCriteria = " [LastName] Like '*" & me.txtLastName & "*'"
endif

if LEN(me.txtFirstName & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "[FirstName] Like '*" & me.txtFirstName & "*'"
endif

if Len(me.txtDOB & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "[DateOfBirth] = #" & me.txtDOB & "#"
endif

strSQL = strSQL & (" WHERE " + varCriteria)

'do something with the string here

End Sub

I use a variant for VarCriteria because of the ease of use when
concatenating a NULL value to another ITEM. It eliminates the need to check
to see whether the criteria already contains a value.

I'm not sure how you are using this, but you can leave out the strSQL
portion, and just set the forms filter property to varCriteria, or you could
set the value of a querydefs sql property, something like:

currentdb.querydefs("yourQuery").sql = strSQL & (" WHERE " + varCriteria)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB, Document Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the end-user may
only have 1 or more pieces of data to perform a search, they may also only
have a partial Last Name or worse an inproperly spelled last name - so I need
a few different scenarios to perform my query and my OR statements don't cut
it any more.

The user may type SMITH in the last name field, which returns a lot of data
and currently if they enter SMITH and JOE they get all the Smith's and all
the Joe's, due to my many OR statements. What is needed, and I can't seem to
get it right is the ability to search on any one and/or all fields. The user
may also type the first few characters of a last name. I guess I need a mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to search for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[ChicagoPete]

Thanks for the quick response Dale.
I failed to mention that I was asked by mgmt NOT to use added code, and to
do this db with the wizards and such as this will be passed to one super-user
to maintain thru its life.
I just never worked with the need for multiple operators before.
i know I need a "LIKE" statement in the First/Last name fields and some IS
NULL /IF IS NULL in all the fields because any one of them may be blank.

They may be stuck with my OR statements...

thanks again...

Pete,

In this type of circumstance, I generally build my SQL strings dynamically.
I start out with a search form, which it looks like you already have, which
also includes a "filter","search", or "find next" button. Then, In the Click
event of that button, I use a series of statements to build my SQL statement.
Something like:

Private Sub Search_Click

Dim strSQL as string
Dim varCriteria as variant

strSQL = "SELECT LastName, FirstName, DOB, " _
& "DocumentType, Location " _
& "FROM tblMain"

varCriteria = NULL
if LEN(me.txtLastName & "") > 0 then
varCriteria = " [LastName] Like '*" & me.txtLastName & "*'"
endif

if LEN(me.txtFirstName & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "[FirstName] Like '*" & me.txtFirstName & "*'"
endif

if Len(me.txtDOB & "") > 0 then
varCriteria = (varCriteria + " AND ") _
& "[DateOfBirth] = #" & me.txtDOB & "#"
endif

strSQL = strSQL & (" WHERE " + varCriteria)

'do something with the string here

End Sub

I use a variant for VarCriteria because of the ease of use when
concatenating a NULL value to another ITEM. It eliminates the need to check
to see whether the criteria already contains a value.

I'm not sure how you are using this, but you can leave out the strSQL
portion, and just set the forms filter property to varCriteria, or you could
set the value of a querydefs sql property, something like:

currentdb.querydefs("yourQuery").sql = strSQL & (" WHERE " + varCriteria)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB, Document Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the end-user may
only have 1 or more pieces of data to perform a search, they may also only
have a partial Last Name or worse an inproperly spelled last name - so I need
a few different scenarios to perform my query and my OR statements don't cut
it any more.

The user may type SMITH in the last name field, which returns a lot of data
and currently if they enter SMITH and JOE they get all the Smith's and all
the Joe's, due to my many OR statements. What is needed, and I can't seem to
get it right is the ability to search on any one and/or all fields. The user
may also type the first few characters of a last name. I guess I need a mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to search for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[ChicagoPete]

Danny,
Thanks, that solves one of my issues, the LIKE for the Last Name.
My concern is if I change the OR to AND and they leave any of the other
fields blank it will return everything - don't I need a ISNULL or ISNOTNULL
in there somewhere on ALL of the fields??

Last Name = "SMITH"
First Name = "JOE"
Document Type = "Invoice"

Should return ONLY Joe Smith invoices, but with the OR in there it returns
ALL invoices. If I put a AND in there it still returns everything because (I
think) there is no ISNULL or ISNOTNULL in the query...

-Pete

Pete:

I hate what Access QBE grid does to WHERE clause statements. Yours could be
rewritten like this, with the table name omitted, though that's only done
for clarity. It works either way, but of course purists will tell you to
include the table nam.

WHERE LastName =[forms]![frmSearch]![LastName]
OR FirstName =[forms]![frmSearch]![FirstName]
OR DOB =[forms]![frmSearch]![DateOfBirth]
OR DocumentType =[forms]![frmSearch]![DocumentType]
OR Location =[forms]![frmSearch]![Location]

To make this more flexible, you'll need the LIKE operator.

WHERE LastName Like "*" & [forms]![frmSearch]![LastName] & "*"
OR FirstName = [forms]![frmSearch]![FirstName]
OR DOB = [forms]![frmSearch]![DateOfBirth]
OR DocumentType = [forms]![frmSearch]![DocumentType]
OR Location = [forms]![frmSearch]![Location]

If you want to make the result set more restrictive, change all the OR
keywords to AND. In the QBE Grid this means putting them all on the same
line.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB, Document Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the end-user may
only have 1 or more pieces of data to perform a search, they may also only
have a partial Last Name or worse an inproperly spelled last name - so I
need
a few different scenarios to perform my query and my OR statements don't
cut
it any more.

The user may type SMITH in the last name field, which returns a lot of
data
and currently if they enter SMITH and JOE they get all the Smith's and all
the Joe's, due to my many OR statements. What is needed, and I can't seem
to
get it right is the ability to search on any one and/or all fields. The
user
may also type the first few characters of a last name. I guess I need a
mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to search
for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[Danny Lesandrini]

Pete:

I now see what you're saying. I sometimes have a hard time visualizing
things I'm not actually working on, so I mocked it up with a contacts table
of mine. The criteria below works for me. I put this in the Criteria row
of the FirstName column of my query. It uses the IIF() function to decide
whether or not to use the filter.

Like IIf([Forms]![frmMain]![txtFirst] Is Null,Null,"*" &
[Forms]![frmMain]![txtFirst] & "*")

Next, I tried the NULL test in an AND clause, and this works too:

Like "*" & [Forms]![frmMain]![txtFirst] & "*" And
[Forms]![frmMain]![txtFirst] Is Not Null


Now you'll need to combine these with other filters to get the results you'd
like, but I've convinced myself that it can be done in either of these two
ways.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Danny,
Thanks, that solves one of my issues, the LIKE for the Last Name.
My concern is if I change the OR to AND and they leave any of the other
fields blank it will return everything - don't I need a ISNULL or
ISNOTNULL
in there somewhere on ALL of the fields??

Last Name = "SMITH"
First Name = "JOE"
Document Type = "Invoice"

Should return ONLY Joe Smith invoices, but with the OR in there it returns
ALL invoices. If I put a AND in there it still returns everything because
(I
think) there is no ISNULL or ISNOTNULL in the query...

-Pete

Pete:

I hate what Access QBE grid does to WHERE clause statements. Yours could
be
rewritten like this, with the table name omitted, though that's only done
for clarity. It works either way, but of course purists will tell you to
include the table nam.

WHERE LastName =[forms]![frmSearch]![LastName]
OR FirstName =[forms]![frmSearch]![FirstName]
OR DOB =[forms]![frmSearch]![DateOfBirth]
OR DocumentType =[forms]![frmSearch]![DocumentType]
OR Location =[forms]![frmSearch]![Location]

To make this more flexible, you'll need the LIKE operator.

WHERE LastName Like "*" & [forms]![frmSearch]![LastName] & "*"
OR FirstName = [forms]![frmSearch]![FirstName]
OR DOB = [forms]![frmSearch]![DateOfBirth]
OR DocumentType = [forms]![frmSearch]![DocumentType]
OR Location = [forms]![frmSearch]![Location]

If you want to make the result set more restrictive, change all the OR
keywords to AND. In the QBE Grid this means putting them all on the same
line.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB, Document
Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the end-user
may
only have 1 or more pieces of data to perform a search, they may also
only
have a partial Last Name or worse an inproperly spelled last name - so
I
need
a few different scenarios to perform my query and my OR statements
don't
cut
it any more.

The user may type SMITH in the last name field, which returns a lot of
data
and currently if they enter SMITH and JOE they get all the Smith's and
all
the Joe's, due to my many OR statements. What is needed, and I can't
seem
to
get it right is the ability to search on any one and/or all fields. The
user
may also type the first few characters of a last name. I guess I need a
mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to
search
for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[John W. Vinson]

I failed to mention that I was asked by mgmt NOT to use added code, and to
do this db with the wizards and such as this will be passed to one super-user
to maintain thru its life.

"Please provide the user with a utility truck. Oh, by the way, it is not
permitted to provide an engine in the truck."

That's an unreasonable request. You'll need either some very complex and
unmaintainable SQL ("code") in your query, or (better) some VBA code as
suggested. The wizards simply aren't smart enough to accomplish this
particular task given the constraints.

 

[ChicagoPete]

Danny,
I'm currently working with your second solution and hitting a "NAME#"
reference error - I'll keep trying...

-Pete

Pete:

I now see what you're saying. I sometimes have a hard time visualizing
things I'm not actually working on, so I mocked it up with a contacts table
of mine. The criteria below works for me. I put this in the Criteria row
of the FirstName column of my query. It uses the IIF() function to decide
whether or not to use the filter.

Like IIf([Forms]![frmMain]![txtFirst] Is Null,Null,"*" &
[Forms]![frmMain]![txtFirst] & "*")

Next, I tried the NULL test in an AND clause, and this works too:

Like "*" & [Forms]![frmMain]![txtFirst] & "*" And
[Forms]![frmMain]![txtFirst] Is Not Null


Now you'll need to combine these with other filters to get the results you'd
like, but I've convinced myself that it can be done in either of these two
ways.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Danny,
Thanks, that solves one of my issues, the LIKE for the Last Name.
My concern is if I change the OR to AND and they leave any of the other
fields blank it will return everything - don't I need a ISNULL or
ISNOTNULL
in there somewhere on ALL of the fields??

Last Name = "SMITH"
First Name = "JOE"
Document Type = "Invoice"

Should return ONLY Joe Smith invoices, but with the OR in there it returns
ALL invoices. If I put a AND in there it still returns everything because
(I
think) there is no ISNULL or ISNOTNULL in the query...

-Pete

Pete:

I hate what Access QBE grid does to WHERE clause statements. Yours could
be
rewritten like this, with the table name omitted, though that's only done
for clarity. It works either way, but of course purists will tell you to
include the table nam.

WHERE LastName =[forms]![frmSearch]![LastName]
OR FirstName =[forms]![frmSearch]![FirstName]
OR DOB =[forms]![frmSearch]![DateOfBirth]
OR DocumentType =[forms]![frmSearch]![DocumentType]
OR Location =[forms]![frmSearch]![Location]

To make this more flexible, you'll need the LIKE operator.

WHERE LastName Like "*" & [forms]![frmSearch]![LastName] & "*"
OR FirstName = [forms]![frmSearch]![FirstName]
OR DOB = [forms]![frmSearch]![DateOfBirth]
OR DocumentType = [forms]![frmSearch]![DocumentType]
OR Location = [forms]![frmSearch]![Location]

If you want to make the result set more restrictive, change all the OR
keywords to AND. In the QBE Grid this means putting them all on the same
line.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB, Document
Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the end-user
may
only have 1 or more pieces of data to perform a search, they may also
only
have a partial Last Name or worse an inproperly spelled last name - so
I
need
a few different scenarios to perform my query and my OR statements
don't
cut
it any more.

The user may type SMITH in the last name field, which returns a lot of
data
and currently if they enter SMITH and JOE they get all the Smith's and
all
the Joe's, due to my many OR statements. What is needed, and I can't
seem
to
get it right is the ability to search on any one and/or all fields. The
user
may also type the first few characters of a last name. I guess I need a
mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to
search
for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[Danny Lesandrini]

Yes, that's probably because my code pointed to my tables and fields. Did
you modify it for your table and field names?

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Danny,
I'm currently working with your second solution and hitting a "NAME#"
reference error - I'll keep trying...

-Pete

Pete:

I now see what you're saying. I sometimes have a hard time visualizing
things I'm not actually working on, so I mocked it up with a contacts
table
of mine. The criteria below works for me. I put this in the Criteria
row
of the FirstName column of my query. It uses the IIF() function to
decide
whether or not to use the filter.

Like IIf([Forms]![frmMain]![txtFirst] Is Null,Null,"*" &
[Forms]![frmMain]![txtFirst] & "*")

Next, I tried the NULL test in an AND clause, and this works too:

Like "*" & [Forms]![frmMain]![txtFirst] & "*" And
[Forms]![frmMain]![txtFirst] Is Not Null


Now you'll need to combine these with other filters to get the results
you'd
like, but I've convinced myself that it can be done in either of these
two
ways.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Danny,
Thanks, that solves one of my issues, the LIKE for the Last Name.
My concern is if I change the OR to AND and they leave any of the
other
fields blank it will return everything - don't I need a ISNULL or
ISNOTNULL
in there somewhere on ALL of the fields??

Last Name = "SMITH"
First Name = "JOE"
Document Type = "Invoice"

Should return ONLY Joe Smith invoices, but with the OR in there it
returns
ALL invoices. If I put a AND in there it still returns everything
because
(I
think) there is no ISNULL or ISNOTNULL in the query...

-Pete

:

Pete:

I hate what Access QBE grid does to WHERE clause statements. Yours
could
be
rewritten like this, with the table name omitted, though that's only
done
for clarity. It works either way, but of course purists will tell you
to
include the table nam.

WHERE LastName =[forms]![frmSearch]![LastName]
OR FirstName =[forms]![frmSearch]![FirstName]
OR DOB =[forms]![frmSearch]![DateOfBirth]
OR DocumentType =[forms]![frmSearch]![DocumentType]
OR Location =[forms]![frmSearch]![Location]

To make this more flexible, you'll need the LIKE operator.

WHERE LastName Like "*" & [forms]![frmSearch]![LastName] &
"*"
OR FirstName = [forms]![frmSearch]![FirstName]
OR DOB = [forms]![frmSearch]![DateOfBirth]
OR DocumentType = [forms]![frmSearch]![DocumentType]
OR Location = [forms]![frmSearch]![Location]

If you want to make the result set more restrictive, change all the OR
keywords to AND. In the QBE Grid this means putting them all on the
same
line.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB,
Document
Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the
end-user
may
only have 1 or more pieces of data to perform a search, they may
also
only
have a partial Last Name or worse an inproperly spelled last name -
so
I
need
a few different scenarios to perform my query and my OR statements
don't
cut
it any more.

The user may type SMITH in the last name field, which returns a lot
of
data
and currently if they enter SMITH and JOE they get all the Smith's
and
all
the Joe's, due to my many OR statements. What is needed, and I can't
seem
to
get it right is the ability to search on any one and/or all fields.
The
user
may also type the first few characters of a last name. I guess I
need a
mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to
search
for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[ChicagoPete]

Yes, I changed the names to my fields and yes it is plugged in ;-)

I still have problems if the first name field is left blank, then I get no
results.

still working it...

Yes, that's probably because my code pointed to my tables and fields. Did
you modify it for your table and field names?

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Danny,
I'm currently working with your second solution and hitting a "NAME#"
reference error - I'll keep trying...

-Pete

Pete:

I now see what you're saying. I sometimes have a hard time visualizing
things I'm not actually working on, so I mocked it up with a contacts
table
of mine. The criteria below works for me. I put this in the Criteria
row
of the FirstName column of my query. It uses the IIF() function to
decide
whether or not to use the filter.

Like IIf([Forms]![frmMain]![txtFirst] Is Null,Null,"*" &
[Forms]![frmMain]![txtFirst] & "*")

Next, I tried the NULL test in an AND clause, and this works too:

Like "*" & [Forms]![frmMain]![txtFirst] & "*" And
[Forms]![frmMain]![txtFirst] Is Not Null


Now you'll need to combine these with other filters to get the results
you'd
like, but I've convinced myself that it can be done in either of these
two
ways.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Danny,
Thanks, that solves one of my issues, the LIKE for the Last Name.
My concern is if I change the OR to AND and they leave any of the
other
fields blank it will return everything - don't I need a ISNULL or
ISNOTNULL
in there somewhere on ALL of the fields??

Last Name = "SMITH"
First Name = "JOE"
Document Type = "Invoice"

Should return ONLY Joe Smith invoices, but with the OR in there it
returns
ALL invoices. If I put a AND in there it still returns everything
because
(I
think) there is no ISNULL or ISNOTNULL in the query...

-Pete

:

Pete:

I hate what Access QBE grid does to WHERE clause statements. Yours
could
be
rewritten like this, with the table name omitted, though that's only
done
for clarity. It works either way, but of course purists will tell you
to
include the table nam.

WHERE LastName =[forms]![frmSearch]![LastName]
OR FirstName =[forms]![frmSearch]![FirstName]
OR DOB =[forms]![frmSearch]![DateOfBirth]
OR DocumentType =[forms]![frmSearch]![DocumentType]
OR Location =[forms]![frmSearch]![Location]

To make this more flexible, you'll need the LIKE operator.

WHERE LastName Like "*" & [forms]![frmSearch]![LastName] &
"*"
OR FirstName = [forms]![frmSearch]![FirstName]
OR DOB = [forms]![frmSearch]![DateOfBirth]
OR DocumentType = [forms]![frmSearch]![DocumentType]
OR Location = [forms]![frmSearch]![Location]

If you want to make the result set more restrictive, change all the OR
keywords to AND. In the QBE Grid this means putting them all on the
same
line.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB,
Document
Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the
end-user
may
only have 1 or more pieces of data to perform a search, they may
also
only
have a partial Last Name or worse an inproperly spelled last name -
so
I
need
a few different scenarios to perform my query and my OR statements
don't
cut
it any more.

The user may type SMITH in the last name field, which returns a lot
of
data
and currently if they enter SMITH and JOE they get all the Smith's
and
all
the Joe's, due to my many OR statements. What is needed, and I can't
seem
to
get it right is the ability to search on any one and/or all fields.
The
user
may also type the first few characters of a last name. I guess I
need a
mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to
search
for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[ChicagoPete]

If I just try this in the criterea field for Last Name:

Like IIf([Forms]![frmSearch]![LastName] Is Null,Null,"*" &
[Forms]![frmSearch]![LastName] & "*")

and this in the criterea field of the FirstName

Like IIf([Forms]![frmSearch]![FirstName] Is Null,Null,"*" &
[Forms]![frmSearch]![FirstName] & "*")

When I run my query for only last name "SMITH" I get zero records when i
know I should see a bunch and if I try only first name "Charles" I get zero
records when I know I should get one record.

If I run the query with last name "Smith" and first name "Charles" it works
and returns one record.

Yes, I changed the names to my fields and yes it is plugged in ;-)

I still have problems if the first name field is left blank, then I get no
results.

still working it...

Yes, that's probably because my code pointed to my tables and fields. Did
you modify it for your table and field names?

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Danny,
I'm currently working with your second solution and hitting a "NAME#"
reference error - I'll keep trying...

-Pete

:

Pete:

I now see what you're saying. I sometimes have a hard time visualizing
things I'm not actually working on, so I mocked it up with a contacts
table
of mine. The criteria below works for me. I put this in the Criteria
row
of the FirstName column of my query. It uses the IIF() function to
decide
whether or not to use the filter.

Like IIf([Forms]![frmMain]![txtFirst] Is Null,Null,"*" &
[Forms]![frmMain]![txtFirst] & "*")

Next, I tried the NULL test in an AND clause, and this works too:

Like "*" & [Forms]![frmMain]![txtFirst] & "*" And
[Forms]![frmMain]![txtFirst] Is Not Null


Now you'll need to combine these with other filters to get the results
you'd
like, but I've convinced myself that it can be done in either of these
two
ways.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Danny,
Thanks, that solves one of my issues, the LIKE for the Last Name.
My concern is if I change the OR to AND and they leave any of the
other
fields blank it will return everything - don't I need a ISNULL or
ISNOTNULL
in there somewhere on ALL of the fields??

Last Name = "SMITH"
First Name = "JOE"
Document Type = "Invoice"

Should return ONLY Joe Smith invoices, but with the OR in there it
returns
ALL invoices. If I put a AND in there it still returns everything
because
(I
think) there is no ISNULL or ISNOTNULL in the query...

-Pete

:

Pete:

I hate what Access QBE grid does to WHERE clause statements. Yours
could
be
rewritten like this, with the table name omitted, though that's only
done
for clarity. It works either way, but of course purists will tell you
to
include the table nam.

WHERE LastName =[forms]![frmSearch]![LastName]
OR FirstName =[forms]![frmSearch]![FirstName]
OR DOB =[forms]![frmSearch]![DateOfBirth]
OR DocumentType =[forms]![frmSearch]![DocumentType]
OR Location =[forms]![frmSearch]![Location]

To make this more flexible, you'll need the LIKE operator.

WHERE LastName Like "*" & [forms]![frmSearch]![LastName] &
"*"
OR FirstName = [forms]![frmSearch]![FirstName]
OR DOB = [forms]![frmSearch]![DateOfBirth]
OR DocumentType = [forms]![frmSearch]![DocumentType]
OR Location = [forms]![frmSearch]![Location]

If you want to make the result set more restrictive, change all the OR
keywords to AND. In the QBE Grid this means putting them all on the
same
line.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Help with AND/OR, IIF, ISNULL, ISNOTNULL, LIKE??

In my simplified example, I have Last Name, First Name, DOB,
Document
Type
(receipt, check, invoice) and location.
Here is my SQL code from the Access2007 Query, problem is the
end-user
may
only have 1 or more pieces of data to perform a search, they may
also
only
have a partial Last Name or worse an inproperly spelled last name -
so
I
need
a few different scenarios to perform my query and my OR statements
don't
cut
it any more.

The user may type SMITH in the last name field, which returns a lot
of
data
and currently if they enter SMITH and JOE they get all the Smith's
and
all
the Joe's, due to my many OR statements. What is needed, and I can't
seem
to
get it right is the ability to search on any one and/or all fields.
The
user
may also type the first few characters of a last name. I guess I
need a
mix
of operators such as LIKE, IS NOT NULL etc... Maybe the ability to
search
for
all invoices from Joe Smith?

Any help/ideas as I know someone has dealt with this before.

Thanks in advance

****snip****

SELECT tblMain.LastName, tblMain.FirstName, tblMain.DOB,
tblMain.DocumentType, tblMain.Location,
FROM tblMain
WHERE (((tblMain.LastName)=[forms]![frmSearch]![LastName])) OR
(((tblMain.FirstName)=[forms]![frmSearch]![FirstName])) OR
(((tblMain.DOB)=[forms]![frmSearch]![DateOfBirth])) OR
(((tblMain.DocumentType)=[forms]![frmSearch]![DocumentType])) OR
(((tblMain.Location)=[forms]![frmSearch]![Location]));

****end snip ****

Pete

 

[Danny Lesandrini]

Pete:

Ok, we are getting somewhere. It runs, but doesn't return the recordset you
expect, right?

First, copy and save off to a text file the two criteria expressions so you
don't lose them, and then run them in the query one at a time to verify
that, individually they are behaving correctly. Then return them both, once
on the same criteria line and once on different lines. (This is the
difference between running them as an AND operation or an OR operation.)

If the statements are correct, one of these configurations should give you
your expected results.

 

[ChicagoPete]

Here are the results:

If I use only the Last Name on the criteria line:
Last Name = "Smith"
First Name left blank
returned all Smiths

If i use only First Name on the criteria line:
Last Name = left blank
First Name = "Charles"
returned all Charles

If I use both criteria on same line:
Last Name = "Smith"
First Name = "Charles"
returns Charles Smith
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned nothing (expecting all Smiths)
or
Last Name = left blank
First Name = "Charles"
returned nothing (expecting all Charles)

If I use both criteria on different lines:
Last Name = "Smith"
First Name = "Charles"
returns all Smiths and all Charles
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned all Smiths
or
Last Name = left blank
First Name = "Charles"
returned all Charles


So if a user enters only a last name I need to return all 'last name'
if a user enters only a first name then return all 'first name'
if a user enters both a first name and last name return only a match of
first name AND last name - not all Smiths and all Charles.

clear as mud?

Pete:

Ok, we are getting somewhere. It runs, but doesn't return the recordset you
expect, right?

First, copy and save off to a text file the two criteria expressions so you
don't lose them, and then run them in the query one at a time to verify
that, individually they are behaving correctly. Then return them both, once
on the same criteria line and once on different lines. (This is the
difference between running them as an AND operation or an OR operation.)

If the statements are correct, one of these configurations should give you
your expected results.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

If I just try this in the criterea field for Last Name:

Like IIf([Forms]![frmSearch]![LastName] Is Null,Null,"*" &
[Forms]![frmSearch]![LastName] & "*")

and this in the criterea field of the FirstName

Like IIf([Forms]![frmSearch]![FirstName] Is Null,Null,"*" &
[Forms]![frmSearch]![FirstName] & "*")

When I run my query for only last name "SMITH" I get zero records when i
know I should see a bunch and if I try only first name "Charles" I get
zero
records when I know I should get one record.

If I run the query with last name "Smith" and first name "Charles" it
works
and returns one record.

 

[Danny Lesandrini]

Pete:

Actually, the way you described it made it very understandable. I don't
have the answer, but I clearly understand the results.

The problem is with both criteria in an AND relationship, on the same
criteria line, where one of them is NULL.

Could be an issue with evaluating NULLs. Try changing the first return of
the IIF() function to a star "*" instead of NULL, so the NULL criteria
becomes LIKE "*" instead of LIKE NULL.

Like IIf([Forms]![frmSearch]![LastName] Is Null, "*","*" &
[Forms]![frmSearch]![LastName] & "*")

Like IIf([Forms]![frmSearch]![FirstName] Is Null, "*","*" &
[Forms]![frmSearch]![FirstName] & "*")


--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Here are the results:

If I use only the Last Name on the criteria line:
Last Name = "Smith"
First Name left blank
returned all Smiths

If i use only First Name on the criteria line:
Last Name = left blank
First Name = "Charles"
returned all Charles

If I use both criteria on same line:
Last Name = "Smith"
First Name = "Charles"
returns Charles Smith
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned nothing (expecting all Smiths)
or
Last Name = left blank
First Name = "Charles"
returned nothing (expecting all Charles)

If I use both criteria on different lines:
Last Name = "Smith"
First Name = "Charles"
returns all Smiths and all Charles
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned all Smiths
or
Last Name = left blank
First Name = "Charles"
returned all Charles


So if a user enters only a last name I need to return all 'last name'
if a user enters only a first name then return all 'first name'
if a user enters both a first name and last name return only a match of
first name AND last name - not all Smiths and all Charles.

clear as mud?

Pete:

Ok, we are getting somewhere. It runs, but doesn't return the recordset
you
expect, right?

First, copy and save off to a text file the two criteria expressions so
you
don't lose them, and then run them in the query one at a time to verify
that, individually they are behaving correctly. Then return them both,
once
on the same criteria line and once on different lines. (This is the
difference between running them as an AND operation or an OR operation.)

If the statements are correct, one of these configurations should give
you
your expected results.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

If I just try this in the criterea field for Last Name:

Like IIf([Forms]![frmSearch]![LastName] Is Null,Null,"*" &
[Forms]![frmSearch]![LastName] & "*")

and this in the criterea field of the FirstName

Like IIf([Forms]![frmSearch]![FirstName] Is Null,Null,"*" &
[Forms]![frmSearch]![FirstName] & "*")

When I run my query for only last name "SMITH" I get zero records when
i
know I should see a bunch and if I try only first name "Charles" I get
zero
records when I know I should get one record.

If I run the query with last name "Smith" and first name "Charles" it
works
and returns one record.

 

[ChicagoPete]

YOU ARE THE MAN!
that did it - I just need to add the same logic to the other fields

Thank you very much for your help and patience...
-Pete

Pete:

Actually, the way you described it made it very understandable. I don't
have the answer, but I clearly understand the results.

The problem is with both criteria in an AND relationship, on the same
criteria line, where one of them is NULL.

Could be an issue with evaluating NULLs. Try changing the first return of
the IIF() function to a star "*" instead of NULL, so the NULL criteria
becomes LIKE "*" instead of LIKE NULL.

Like IIf([Forms]![frmSearch]![LastName] Is Null, "*","*" &
[Forms]![frmSearch]![LastName] & "*")

Like IIf([Forms]![frmSearch]![FirstName] Is Null, "*","*" &
[Forms]![frmSearch]![FirstName] & "*")


--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Here are the results:

If I use only the Last Name on the criteria line:
Last Name = "Smith"
First Name left blank
returned all Smiths

If i use only First Name on the criteria line:
Last Name = left blank
First Name = "Charles"
returned all Charles

If I use both criteria on same line:
Last Name = "Smith"
First Name = "Charles"
returns Charles Smith
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned nothing (expecting all Smiths)
or
Last Name = left blank
First Name = "Charles"
returned nothing (expecting all Charles)

If I use both criteria on different lines:
Last Name = "Smith"
First Name = "Charles"
returns all Smiths and all Charles
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned all Smiths
or
Last Name = left blank
First Name = "Charles"
returned all Charles


So if a user enters only a last name I need to return all 'last name'
if a user enters only a first name then return all 'first name'
if a user enters both a first name and last name return only a match of
first name AND last name - not all Smiths and all Charles.

clear as mud?

Pete:

Ok, we are getting somewhere. It runs, but doesn't return the recordset
you
expect, right?

First, copy and save off to a text file the two criteria expressions so
you
don't lose them, and then run them in the query one at a time to verify
that, individually they are behaving correctly. Then return them both,
once
on the same criteria line and once on different lines. (This is the
difference between running them as an AND operation or an OR operation.)

If the statements are correct, one of these configurations should give
you
your expected results.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com


If I just try this in the criterea field for Last Name:

Like IIf([Forms]![frmSearch]![LastName] Is Null,Null,"*" &
[Forms]![frmSearch]![LastName] & "*")

and this in the criterea field of the FirstName

Like IIf([Forms]![frmSearch]![FirstName] Is Null,Null,"*" &
[Forms]![frmSearch]![FirstName] & "*")

When I run my query for only last name "SMITH" I get zero records when
i
know I should see a bunch and if I try only first name "Charles" I get
zero
records when I know I should get one record.

If I run the query with last name "Smith" and first name "Charles" it
works
and returns one record.

 

[Dale Fye]

Talk about hard to maintain. I totally agree with John that your leadership
obviously doesn't have a clue if they are asking you not to use the full
toolset at hand.

The guy that has to maintain this query is going to pull his hair out the
first time he tries to debug it, and every time thereafter. On the other
hand, a little bit of VBA, with a couple of documentation lines goes a long
way to maintainability.

Good luck.
--
Dale

email address is invalid
Please reply to newsgroup only.

YOU ARE THE MAN!
that did it - I just need to add the same logic to the other fields

Thank you very much for your help and patience...
-Pete

Pete:

Actually, the way you described it made it very understandable. I don't
have the answer, but I clearly understand the results.

The problem is with both criteria in an AND relationship, on the same
criteria line, where one of them is NULL.

Could be an issue with evaluating NULLs. Try changing the first return of
the IIF() function to a star "*" instead of NULL, so the NULL criteria
becomes LIKE "*" instead of LIKE NULL.

Like IIf([Forms]![frmSearch]![LastName] Is Null, "*","*" &
[Forms]![frmSearch]![LastName] & "*")

Like IIf([Forms]![frmSearch]![FirstName] Is Null, "*","*" &
[Forms]![frmSearch]![FirstName] & "*")


--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Here are the results:

If I use only the Last Name on the criteria line:
Last Name = "Smith"
First Name left blank
returned all Smiths

If i use only First Name on the criteria line:
Last Name = left blank
First Name = "Charles"
returned all Charles

If I use both criteria on same line:
Last Name = "Smith"
First Name = "Charles"
returns Charles Smith
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned nothing (expecting all Smiths)
or
Last Name = left blank
First Name = "Charles"
returned nothing (expecting all Charles)

If I use both criteria on different lines:
Last Name = "Smith"
First Name = "Charles"
returns all Smiths and all Charles
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned all Smiths
or
Last Name = left blank
First Name = "Charles"
returned all Charles


So if a user enters only a last name I need to return all 'last name'
if a user enters only a first name then return all 'first name'
if a user enters both a first name and last name return only a match of
first name AND last name - not all Smiths and all Charles.

clear as mud?


:


Pete:

Ok, we are getting somewhere. It runs, but doesn't return the recordset
you
expect, right?

First, copy and save off to a text file the two criteria expressions so
you
don't lose them, and then run them in the query one at a time to verify
that, individually they are behaving correctly. Then return them both,
once
on the same criteria line and once on different lines. (This is the
difference between running them as an AND operation or an OR operation.)

If the statements are correct, one of these configurations should give
you
your expected results.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com


If I just try this in the criterea field for Last Name:

Like IIf([Forms]![frmSearch]![LastName] Is Null,Null,"*" &
[Forms]![frmSearch]![LastName] & "*")

and this in the criterea field of the FirstName

Like IIf([Forms]![frmSearch]![FirstName] Is Null,Null,"*" &
[Forms]![frmSearch]![FirstName] & "*")

When I run my query for only last name "SMITH" I get zero records when
i
know I should see a bunch and if I try only first name "Charles" I get
zero
records when I know I should get one record.

If I run the query with last name "Smith" and first name "Charles" it
works
and returns one record.

 

[Dale Fye]

It is likely that in the latter cases, the value in the blank field is no
longer NULL but an empty string "". That is why testing for NULL did not
work.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Here are the results:

If I use only the Last Name on the criteria line:
Last Name = "Smith"
First Name left blank
returned all Smiths

If i use only First Name on the criteria line:
Last Name = left blank
First Name = "Charles"
returned all Charles

If I use both criteria on same line:
Last Name = "Smith"
First Name = "Charles"
returns Charles Smith
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned nothing (expecting all Smiths)
or
Last Name = left blank
First Name = "Charles"
returned nothing (expecting all Charles)

If I use both criteria on different lines:
Last Name = "Smith"
First Name = "Charles"
returns all Smiths and all Charles
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned all Smiths
or
Last Name = left blank
First Name = "Charles"
returned all Charles


So if a user enters only a last name I need to return all 'last name'
if a user enters only a first name then return all 'first name'
if a user enters both a first name and last name return only a match of
first name AND last name - not all Smiths and all Charles.

clear as mud?

Pete:

Ok, we are getting somewhere. It runs, but doesn't return the recordset you
expect, right?

First, copy and save off to a text file the two criteria expressions so you
don't lose them, and then run them in the query one at a time to verify
that, individually they are behaving correctly. Then return them both, once
on the same criteria line and once on different lines. (This is the
difference between running them as an AND operation or an OR operation.)

If the statements are correct, one of these configurations should give you
your expected results.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

If I just try this in the criterea field for Last Name:

Like IIf([Forms]![frmSearch]![LastName] Is Null,Null,"*" &
[Forms]![frmSearch]![LastName] & "*")

and this in the criterea field of the FirstName

Like IIf([Forms]![frmSearch]![FirstName] Is Null,Null,"*" &
[Forms]![frmSearch]![FirstName] & "*")

When I run my query for only last name "SMITH" I get zero records when i
know I should see a bunch and if I try only first name "Charles" I get
zero
records when I know I should get one record.

If I run the query with last name "Smith" and first name "Charles" it
works
and returns one record.

 

[Danny Lesandrini]

Actually, it's probably because the concept of LIKE NULL is meaningless.

I mean, what IS null? The unknown. So what does it mean to be LIKE the
UNKNOWN?
Nobody knows!

That was my bad ... I wrote the initial WHERE script. Just wasn't thinking
clearly at the time.

As for being difficult to maintain, that could be true, but since VBA is
difficult for some, it might be considered 'six of one and half dozen of
another'.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

It is likely that in the latter cases, the value in the blank field is no
longer NULL but an empty string "". That is why testing for NULL did not
work.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Here are the results:

If I use only the Last Name on the criteria line:
Last Name = "Smith"
First Name left blank
returned all Smiths

If i use only First Name on the criteria line:
Last Name = left blank
First Name = "Charles"
returned all Charles

If I use both criteria on same line:
Last Name = "Smith"
First Name = "Charles"
returns Charles Smith
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned nothing (expecting all Smiths)
or
Last Name = left blank
First Name = "Charles"
returned nothing (expecting all Charles)

If I use both criteria on different lines:
Last Name = "Smith"
First Name = "Charles"
returns all Smiths and all Charles
BUT if I try
Last Name = "Smith"
First Name = left blank
retuned all Smiths
or
Last Name = left blank
First Name = "Charles"
returned all Charles


So if a user enters only a last name I need to return all 'last name'
if a user enters only a first name then return all 'first name'
if a user enters both a first name and last name return only a match of
first name AND last name - not all Smiths and all Charles.

clear as mud?

Pete:

Ok, we are getting somewhere. It runs, but doesn't return the
recordset you
expect, right?

First, copy and save off to a text file the two criteria expressions so
you
don't lose them, and then run them in the query one at a time to verify
that, individually they are behaving correctly. Then return them both,
once
on the same criteria line and once on different lines. (This is the
difference between running them as an AND operation or an OR
operation.)

If the statements are correct, one of these configurations should give
you
your expected results.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com


If I just try this in the criterea field for Last Name:

Like IIf([Forms]![frmSearch]![LastName] Is Null,Null,"*" &
[Forms]![frmSearch]![LastName] & "*")

and this in the criterea field of the FirstName

Like IIf([Forms]![frmSearch]![FirstName] Is Null,Null,"*" &
[Forms]![frmSearch]![FirstName] & "*")

When I run my query for only last name "SMITH" I get zero records
when i
know I should see a bunch and if I try only first name "Charles" I
get
zero
records when I know I should get one record.

If I run the query with last name "Smith" and first name "Charles" it
works
and returns one record.