Tip:
Highlight text to annotate it
X
I had made a copy of odb27
and I'm calling this one odb28 user parameters
part 2 we're going to build on what we did in the last
video so I'm going into queries
and design view - I am going to add in the table
and then for our query I'm going to add the
asterisks to select everything then I want to add in the
last name again but I don't want it to appear in our result set
so I'm going to unchecked the visible checkbox
and for our criterion
I'm going to type in...
:Please_
Enter_a_
LastName clicking in the next field to lock that in
now I'm going to go over to SQL View
I will reformat this
this is where we left off in the last
video. we have our select our table and are where - that will
select by last name we're going to do
a little more in this video I want to add in
a wild card so we can search on the full last name
or if we only know part of the last name we can type that in
its just like we did in the wild-card video
with one small change. In the wild-card video
we were able to put everything in between two quotes
in fact let's do that here for the first name so our first name
will have to have lower case a in it. and right now
our last name will have to be exactly as we type it in
if we change the equals to like and put in
left parentheses percent sign in then
are input value, it's going to think that the input value is part
of our search value so this won't work
what we need to do is use wildcards
and concatenate them with the input parameter
when I see concatenate it means we just want to make
two or more text strings into a single text string - let me type this in
and then we'll talk about it
here we have like then a percent sign
in single quotes, then we have what is referred to as
a pipe character, 2 of them actually - right nex next to each other
this character
also known as vertical bar is usually found
above the backslash on a US keyboard
it looks like a line with the space in the middle of it on my keyboard
but it shows up as the single vertical line
on the screen then we have are input parameter
then we've got a concatenation field again and then
our wildcard. this whole thing will be treated as a single string of text
just like the percent sign "a" percent sign
in the line above it. let's run this query
and see how it works; and we see we have an error
looking at our first name and last name...
our first name is green and the last name
is orange we want them both be orange it looks like we've got the wrong quotes
I will fix that... and we wiill run this again
now its asking us to enter in a last name.
I'm going to type in
in the lower case "r" to find any last name
with a lower case "r" and a first name with a with a lower case "a"
and we see we've got nine
all the last names have the
lower case "r" all the first names have a lower case "a"
I'll close the result set hitting F4
Let's go over to design view
the interesting thing about this
is under first name are wildcard characters
have changed from a percent sign to an asterisk
but where we have the concatenation are wildcard is still
a percent sign. I'm going to save this
as odb28
enter the last name first name A
clicking OK now I'm going to save us again
save as and I'm going to take off the
A at the end I'm
also went to remove the first-name criteria
I need to click of this to lock it in
and I'm going to save this again
now we can close the query window
I'm going to save our database
now I'm going to create a quick
form and use our new query as the input
Let's go over to the Form Wizard
for our input
this is where we went to use our new form
odb28 last name first name
without the A I'm going to select all the fields
next we don't want
the subform for this one
I'm going to use in columns labels on top
clicking Next we're going to make this one
just a view only so we don't want to allow modifications
we don't want to allow deletions and we don't want the user to be able to
add any data. this is just a view only form
and a style
I'll pick the ice blue 3D look and to give it a name
I will call this form
frm-odb27 please enter an employee name
and we're going to go into work with the form - right away
it's asking for a last name and I'm going to type in
"son" any name that has son in it
should show up clicking OK
here's our first one Anderson and we can see down here that we've got five second
click to the next one
Robinson, Thompson,
and Jackson, as well Johnson
now I want to type in a different name but there is no easy way
to rerun my form from here
so I'm going to close my form and open it back up
in design view actually, I should rename this it's
27 we want this to be 28
now it's how we want it. I'm going to edit this form
I'm going to add in a push-button
right click open up the controls
I'm going to call this button refresh
the label will say refresh
and down at the bottom where we have our action
I'm going to change this to refresh form
I want to save this form and now I'm going to go over in
to design mood on/off
it s me for the last name I will type in the name
Lee - clicking OK
here we see Donna Lee
now if I click Refresh
It's asking me for another last name I will type in the name
Wright
and we see that we've got George Wright I will try it with one more
are type in the name Jackson
and there we have Barb Jackson I've already saved the form
so I can close it I want to save my database
all all cool all
all