SQL Expressions in UNION Query - help

[Wendy]

Can I use the acStart, or something like it, within a UNION query to
instruct the query to use "Start with in Field" rather than the default of
"Match". ??

If yes, could you correct my very simple expression?

UNION ALL SELECT [PatientNumber], [LastName], [FirstName], [BirthDate],
[SSN]
FROM [OldPatientRecords]
WHERE [LastName]= [Type Last Name:]
ORDER BY [FirstName];

 

['69 Camaro]

Hi, Wendy.

You won't be able to control this setting with SQL.

You need to change the default search option setting for Access, instead.
To do so, select the Tools -> Options menu to open the "Options" dialog
window. Select the "Edit/Find" tab. Select the "Start of Field Search"
option button at the top left of the window, then the "OK" button to
close the window. Access will continue to use your current setting that
you've already manually applied in the "Find And Replace" window (whether
it's "Any Part of Field," "Whole Field," or "Start of Field") for any future
search during your current session. However, the next time you open the
database in Access, it will use your new default "Start of Field" for
searches, so you won't have to apply it manually again.

And one shouldn't store SSN's with their owners' names in an Access
database, because Access is not very secure.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[John Vinson]

Can I use the acStart, or something like it, within a UNION query to
instruct the query to use "Start with in Field" rather than the default of
"Match". ??

If yes, could you correct my very simple expression?

UNION ALL SELECT [PatientNumber], [LastName], [FirstName], [BirthDate],
[SSN]
FROM [OldPatientRecords]
WHERE [LastName]= [Type Last Name:]
ORDER BY [FirstName];

Change the criterion to

WHERE [LastName] LIKE [Type last name, or the beggining of it:] & "*"

The LIKE operator honors wildcards such as *; this will find JOHN,
JOHNSON, JOHNSTON and the like if the user types JOHN into the prompt.

I presume that this is one clause of two or more in a UNION query...

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Wendy]

Gunny, I tried your suggestion, but the defined Queries I've written (one
using straight "wizard" expressions
and one using SQL UNION Query) continue to do Match only. For example, I
need to find a person in
the Last Name field with a value of "washington (porter)" (beats me why they
typed in his last name in this
structure rather than putting the "porter" part in the Comments field, but
they did. I use a [Type in Last
Name:] prompt in the simple query, and have tried using a syntax of
"washington*" or with the "?" and
neither finds this particular record. I don't want these users to have to
be in the table at all, so I'm trying to
control their activities via forms or queries off the Switchboard.

And, yes, I double checked the File/Edit option under Tools, closed and
restarted Access.....

Got any other ideas??? And, I appreciate the comment about the SSN number.

Hi, Wendy.

You won't be able to control this setting with SQL.

You need to change the default search option setting for Access, instead.
To do so, select the Tools -> Options menu to open the "Options" dialog
window. Select the "Edit/Find" tab. Select the "Start of Field Search"
option button at the top left of the window, then the "OK" button to
close the window. Access will continue to use your current setting that
you've already manually applied in the "Find And Replace" window (whether
it's "Any Part of Field," "Whole Field," or "Start of Field") for any future
search during your current session. However, the next time you open the
database in Access, it will use your new default "Start of Field" for
searches, so you won't have to apply it manually again.

And one shouldn't store SSN's with their owners' names in an Access
database, because Access is not very secure.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

Can I use the acStart, or something like it, within a UNION query to
instruct the query to use "Start with in Field" rather than the default of
"Match". ??

If yes, could you correct my very simple expression?

UNION ALL SELECT [PatientNumber], [LastName], [FirstName], [BirthDate],
[SSN]
FROM [OldPatientRecords]
WHERE [LastName]= [Type Last Name:]
ORDER BY [FirstName];

 

['69 Camaro]

Thanks, John, for providing the correct answer. I obviously misinterpreted
what Wendy was "searching" for!

Gunny

Can I use the acStart, or something like it, within a UNION query to
instruct the query to use "Start with in Field" rather than the default of
"Match". ??

If yes, could you correct my very simple expression?

UNION ALL SELECT [PatientNumber], [LastName], [FirstName], [BirthDate],
[SSN]
FROM [OldPatientRecords]
WHERE [LastName]= [Type Last Name:]
ORDER BY [FirstName];

Change the criterion to

WHERE [LastName] LIKE [Type last name, or the beggining of it:] & "*"

The LIKE operator honors wildcards such as *; this will find JOHN,
JOHNSON, JOHNSTON and the like if the user types JOHN into the prompt.

I presume that this is one clause of two or more in a UNION query...

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

['69 Camaro]

Sorry, Wendy.

I misinterpreted what you were requesting. I thought you were asking about
the search feature to search through the results of the query after
displaying the result set. John Vinson has the answer you need for the
syntax for the query's criteria.

Gunny

Gunny, I tried your suggestion, but the defined Queries I've written (one
using straight "wizard" expressions
and one using SQL UNION Query) continue to do Match only. For example, I
need to find a person in
the Last Name field with a value of "washington (porter)" (beats me why they
typed in his last name in this
structure rather than putting the "porter" part in the Comments field, but
they did. I use a [Type in Last
Name:] prompt in the simple query, and have tried using a syntax of
"washington*" or with the "?" and
neither finds this particular record. I don't want these users to have to
be in the table at all, so I'm trying to
control their activities via forms or queries off the Switchboard.

And, yes, I double checked the File/Edit option under Tools, closed and
restarted Access.....

Got any other ideas??? And, I appreciate the comment about the SSN number.

message news:[email protected]...

Hi, Wendy.

You won't be able to control this setting with SQL.

You need to change the default search option setting for Access, instead.
To do so, select the Tools -> Options menu to open the "Options" dialog
window. Select the "Edit/Find" tab. Select the "Start of Field Search"
option button at the top left of the window, then the "OK" button to
close the window. Access will continue to use your current setting that
you've already manually applied in the "Find And Replace" window (whether
it's "Any Part of Field," "Whole Field," or "Start of Field") for any future
search during your current session. However, the next time you open the
database in Access, it will use your new default "Start of Field" for
searches, so you won't have to apply it manually again.

And one shouldn't store SSN's with their owners' names in an Access
database, because Access is not very secure.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

default

of

"Match". ??

If yes, could you correct my very simple expression?

UNION ALL SELECT [PatientNumber], [LastName], [FirstName], [BirthDate],
[SSN]
FROM [OldPatientRecords]
WHERE [LastName]= [Type Last Name:]
ORDER BY [FirstName];

 

[Wendy]

MAHVELOUS!!! Thanks. One last tiny question....
My syntax is below. Can I eliminate one or the other of the [Type Last
Name....] prompts? As written, of course, the user is prompted twice. I
have one other question dealing with the structure of a field, but will post
that separately.

SELECT [PatientNumber], [LastName], [FirstName], [BirthDate], [SSN]
FROM [PatientInfo]
WHERE [LastName] LIKE [Type Last Name or Beginning of it :] & "*"

UNION ALL SELECT [PatientNumber], [LastName], [FirstName], [BirthDate],
[SSN]
FROM [OldPatientRecords]
WHERE [LastName] LIKE [Type Last Name or Beginning of it ::] & "*"
ORDER BY [FirstName];

Can I use the acStart, or something like it, within a UNION query to
instruct the query to use "Start with in Field" rather than the default of
"Match". ??

If yes, could you correct my very simple expression?

UNION ALL SELECT [PatientNumber], [LastName], [FirstName], [BirthDate],
[SSN]
FROM [OldPatientRecords]
WHERE [LastName]= [Type Last Name:]
ORDER BY [FirstName];

Change the criterion to

WHERE [LastName] LIKE [Type last name, or the beggining of it:] & "*"

The LIKE operator honors wildcards such as *; this will find JOHN,
JOHNSON, JOHNSTON and the like if the user types JOHN into the prompt.

I presume that this is one clause of two or more in a UNION query...

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

MAHVELOUS!!! Thanks. One last tiny question....
My syntax is below. Can I eliminate one or the other of the [Type Last
Name....] prompts? As written, of course, the user is prompted twice. I
have one other question dealing with the structure of a field, but will post
that separately.

SELECT [PatientNumber], [LastName], [FirstName], [BirthDate], [SSN]
FROM [PatientInfo]
WHERE [LastName] LIKE [Type Last Name or Beginning of it :] & "*"

UNION ALL SELECT [PatientNumber], [LastName], [FirstName], [BirthDate],
[SSN]
FROM [OldPatientRecords]
WHERE [LastName] LIKE [Type Last Name or Beginning of it ::] & "*"
ORDER BY [FirstName];

Remove the extra : in the second prompt. If the two prompts are
ABSOLUTELY IDENTICAL, you should just get prompted once.

Or, you can create a small unbound form (frmCrit) with a textbox
(frmNamepart) and use a criterion of

LIKE [Forms]![frmCrit]![txtNamepart] & "*"

You could then have a button on the form to open the Query, or
(better) a Form or Report based on the query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Wendy]

Thanks once again! I did not even realize there was a second colon in the
second WHERE statement.... no wonder the 2nd prompt. That took care of
it. And I will look into your suggestions of the small form to better
handle this.

MAHVELOUS!!! Thanks. One last tiny question....
My syntax is below. Can I eliminate one or the other of the [Type Last
Name....] prompts? As written, of course, the user is prompted twice. I
have one other question dealing with the structure of a field, but will post
that separately.

SELECT [PatientNumber], [LastName], [FirstName], [BirthDate], [SSN]
FROM [PatientInfo]
WHERE [LastName] LIKE [Type Last Name or Beginning of it :] & "*"

UNION ALL SELECT [PatientNumber], [LastName], [FirstName], [BirthDate],
[SSN]
FROM [OldPatientRecords]
WHERE [LastName] LIKE [Type Last Name or Beginning of it ::] & "*"
ORDER BY [FirstName];

Remove the extra : in the second prompt. If the two prompts are
ABSOLUTELY IDENTICAL, you should just get prompted once.

Or, you can create a small unbound form (frmCrit) with a textbox
(frmNamepart) and use a criterion of

LIKE [Forms]![frmCrit]![txtNamepart] & "*"

You could then have a button on the form to open the Query, or
(better) a Form or Report based on the query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps