queries

[susan]

Help. I am a bear of little brain and so consequently I have
constructed my database by the "what happens if I click this" method
so please no solutions that require me to go and catch a squirrel (or
something).
This is my problem - The records on my database relate to people; I
can make my queries bring up the records for which ever person I ask
it too - so far so good - but, some surnames are duplicated and when
I query those names I either get all of them when I only want one of
them, or none of them. Why wont it search for both a first name and a
last name? I'm using Access 2003 if that helps
Thanks
Sue

[Salad]

Re: queries

susan wrote:

Help. I am a bear of little brain and so consequently I have
constructed my database by the "what happens if I click this" method
so please no solutions that require me to go and catch a squirrel (or
something).
This is my problem - The records on my database relate to people; I
can make my queries bring up the records for which ever person I ask
it too - so far so good - but, some surnames are duplicated and when
I query those names I either get all of them when I only want one of
them, or none of them. Why wont it search for both a first name and a
last name? I'm using Access 2003 if that helps
Thanks
Sue

Why won't it search for first/last names? Maybe because you didn't ask
it to.

I suppose you could create a query (Query1) with last/first name together
FullName : LastName & ", " & FirstName

Now you could use this query for some selects
SELECT Query1.Fullname
FROM Query1
WHERE Query1.Fullname Like [EnterName] & "*";

But a better approach might be to use a form. With the wizard you could
create a continuous form using your table. In the FormHeader add 2 text
boxes to enter last & first name (Lname, Fname) and a button to filter
when ready. BTW, if Lname and Fname are the field names in the table,
make them a different name.

In the OnCLick event of the command button enter
Dim strFilter As String
If Not IsNull(Me.LName ) Then
strFilter = "LName = """ & Me.LName """
Endif
If Not IsNull(Me.Name) Then
If strFilter "" Then strFilter = strFilter & " And "
strFilter = strFilter & "FName = """ & Me.FName """
Endif

'Make this your current filter
Me.Filter = strFilter

'If strFilter "" that means a name was entered
'If strFilter = "" then no names entered so show all recs
Me.FilterOn = (strFilter "")

Super

http://www.youtube.com/watch?v=ZGe5zHhzoPg&feature=user