If/then queries & wildcards

  • Thread starter Thread starter Tom Jones
  • Start date Start date
T

Tom Jones

I've found great code by John Spencer in this NG for if/then query
solutions, which has turned out to be excellent for what I want to do.
However, I'd like to add one more functionality to his code, which I'm
having trouble doing--the ability to handle wildcards.

This question is part of a larger question on forms and queries passing
data between each other, in microsoft.public.access.forms in the thread
"Form data passed to querry".

The code I'm using is as follows. I've modified his "LIKE" to be an "=",
since neither "LIKE" nor "=" allow me to use "*" or "?" wildcards.

<CODE>

SELECT
DISTINCT ROW
Person.LAST,
Person.FIRST,
Address.ADDRESS_TYPE,
Address.STREET,
## -- etc ##

FROM Person INNER JOIN Address ON Person.UNIQUE_PERSON_KEY =
Address.UNIQUE_PERSON_KEY

Where (
((Person.LAST) = [forms]![person]![last] or [forms]![person]![last] is
null)

AND

((Person.FIRST) = [forms]![person]![first] or [forms]![person]![first]
is null)
);

</CODE>

Many thanks.
 
I'm sorry I don't have a solution. However, I am in need
of Mr. Spencer's original code. I've been searching
article by article with no luck.

Could you provide me with the original code, subject line
of the post you found they code at, or the appx date of
the post?

Thanks in advance...

-----Original Message-----
I've found great code by John Spencer in this NG for if/then query
solutions, which has turned out to be excellent for what I want to do.
However, I'd like to add one more functionality to his code, which I'm
having trouble doing--the ability to handle wildcards.

This question is part of a larger question on forms and queries passing
data between each other, in
Click to expand...
microsoft.public.access.forms in the thread
"Form data passed to querry".

The code I'm using is as follows. I've modified his "LIKE" to be an "=",
since neither "LIKE" nor "=" allow me to use "*" or "?" wildcards.

<CODE>

SELECT
DISTINCT ROW
Person.LAST,
Person.FIRST,
Address.ADDRESS_TYPE,
Address.STREET,
## -- etc ##

FROM Person INNER JOIN Address ON Person.UNIQUE_PERSON_KEY =
Address.UNIQUE_PERSON_KEY

Where (
((Person.LAST) = [forms]![person]![last] or [forms]! [person]![last] is
null)

AND

((Person.FIRST) = [forms]![person]![first] or [forms]! [person]![first]
is null)
);

</CODE>

Many thanks.
.
Click to expand...
 
"tag" said:
Could you provide me with the original code, subject line
of the post you found they code at, or the appx date of
the post?
Click to expand...

Original subject: "Re: sql query with if else statement"
Date: 2003-03-03 10:17:10PST

Code:
SELECT *
FROM Contacts
WHERE
([Contacts].[Firstname] like [forms]![n1contactsmain]![firstname2] or
[forms]![n1contactsmain]![firstname2] is null)

OR

([Contacts].[Lastname] like [forms]![n1contactsmain]![Lastname2] or
[forms]![n1contactsmain]![Lastname2] is null)
);

As an aside, I found it through Google newsgroup search with "SQL
if/then statement"
 
The SQL String of the Query should be:

....
WHERE
((Person.LAST Like "*" & [forms]![person]![last] & "*")
OR ([forms]![person]![last] is null))
AND
((Person.FIRST Like "*" & [forms]![person]![first] & "*")
OR ([forms]![person]![first] is null));

You need to use Like AND wildcards together.

HTH
Van T. Dinh
MVP (Access)
 
OK, we're getting close, and that did help.

However, what I'm looking to do is that the user enters his data in a
form. If he enters a name, it should look up that name. If he uses a
wildcard, then the query should search for that.

Examples of _user_ input (for last name):
Smith
*th (or ? -- both should be possible)
Smi*
Sm*h

Currently, with my code, *th, Smi* and Sm*h don't return any data.

Thanks!

"Van T. Dinh" said:
The SQL String of the Query should be:

...
WHERE
((Person.LAST Like "*" & [forms]![person]![last] & "*")
OR ([forms]![person]![last] is null))
AND
((Person.FIRST Like "*" & [forms]![person]![first] & "*")
OR ([forms]![person]![first] is null));

You need to use Like AND wildcards together.

HTH
Van T. Dinh
MVP (Access)


Tom Jones said:
I've found great code by John Spencer in this NG for if/then query
solutions, which has turned out to be excellent for what I want to do.
However, I'd like to add one more functionality to his code, which I'm
having trouble doing--the ability to handle wildcards.

The code I'm using is as follows. I've modified his "LIKE" to be an "=",
since neither "LIKE" nor "=" allow me to use "*" or "?" wildcards.

<CODE>

Where (
((Person.LAST) = [forms]![person]![last] or [forms]![person]![last] is
null)

AND

((Person.FIRST) = [forms]![person]![first] or [forms]![person]![first]
is null)
);

</CODE>

Many thanks.
Click to expand...
Click to expand...
 
How are you running the queries? If you're using ADO, the wildcard
characters are % and _, not * and ?.

--
Doug Steele, Microsoft Access MVP



Tom Jones said:
OK, we're getting close, and that did help.

However, what I'm looking to do is that the user enters his data in a
form. If he enters a name, it should look up that name. If he uses a
wildcard, then the query should search for that.

Examples of _user_ input (for last name):
Smith
*th (or ? -- both should be possible)
Smi*
Sm*h

Currently, with my code, *th, Smi* and Sm*h don't return any data.

Thanks!

"Van T. Dinh" said:
The SQL String of the Query should be:

...
WHERE
((Person.LAST Like "*" & [forms]![person]![last] & "*")
OR ([forms]![person]![last] is null))
AND
((Person.FIRST Like "*" & [forms]![person]![first] & "*")
OR ([forms]![person]![first] is null));

You need to use Like AND wildcards together.

HTH
Van T. Dinh
MVP (Access)


Tom Jones said:
I've found great code by John Spencer in this NG for if/then query
solutions, which has turned out to be excellent for what I want to do.
However, I'd like to add one more functionality to his code, which I'm
having trouble doing--the ability to handle wildcards.

The code I'm using is as follows. I've modified his "LIKE" to be an "=",
since neither "LIKE" nor "=" allow me to use "*" or "?" wildcards.

<CODE>

Where (
((Person.LAST) = [forms]![person]![last] or [forms]![person]![last] is
null)

AND

((Person.FIRST) = [forms]![person]![first] or [forms]![person]![first]
is null)
);

</CODE>

Many thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top