Easy ?

[Guest]

I am using unbound form fields to create a filter in a query to view it on a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is Null

But now I would like to have this to work when the users input only a couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[Rick B]

Use "Like"

Like "*" & Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*"
or Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is Null

 

[[MVP] S.Clark]

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*" Or
IsNull(Fieldname)

 

[Guest]

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the focus lies on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare List].[Language ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code + City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare List].Region)
LIKE Forms!SearchPractician!Region & "*" Or Forms!SearchPractician!Region Is
Null) And (([Läkare List].[First name]) LIKE Forms!SearchPractician!FN & "*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare List].[Last name]) LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN Is Null) And
(([Läkare List].[Name Practice/Hospital]) LIKE Forms!SearchPractician!NPH &
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare List].[Language ID])
LIKE Forms!SearchPractician!Lang & "*" Or Forms!SearchPractician!Lang Is
Null) And (([Läkare List].Phonenumber) LIKE Forms!SearchPractician!Tel & "*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare List].Faxnumber) LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax Is Null) And
(([Läkare List].[E-mail address]) LIKE Forms!SearchPractician!Epost & "*" Or
Forms!SearchPractician!Epost Is Null) And (([Läkare List].Street) LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str Is Null) And
(([Läkare List].[Postal Code + City]) LIKE Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not enough memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*" Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query to view it on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is Null

But now I would like to have this to work when the users input only a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[[MVP] S.Clark]

Do you understand that this part of your query string doesn't do anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query can do with
it.

You need to translate whatever that UI input is to something relevant in the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the focus lies on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code + City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare List].Region)
LIKE Forms!SearchPractician!Region & "*" Or Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE Forms!SearchPractician!FN &
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare List].[Last name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN Is Null) And
(([Läkare List].[Name Practice/Hospital]) LIKE Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or Forms!SearchPractician!Lang Is
Null) And (([Läkare List].Phonenumber) LIKE Forms!SearchPractician!Tel &
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare List].Faxnumber) LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax Is Null)
And
(([Läkare List].[E-mail address]) LIKE Forms!SearchPractician!Epost & "*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare List].Street) LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str Is Null)
And
(([Läkare List].[Postal Code + City]) LIKE Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*" Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query to view it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is Null

But now I would like to have this to work when the users input only a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[Guest]

Steve,
Thanks for pointing out to me about the country thing, I removed that one as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I combine ? How ?
This one works...
WHERE ((([Läkare List].[First name]) Like [Forms]![SearchPractician]![FN] &
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like [Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of things, but I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice

Do you understand that this part of your query string doesn't do anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query can do with
it.

You need to translate whatever that UI input is to something relevant in the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the focus lies on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code + City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare List].Region)
LIKE Forms!SearchPractician!Region & "*" Or Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE Forms!SearchPractician!FN &
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare List].[Last name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN Is Null) And
(([Läkare List].[Name Practice/Hospital]) LIKE Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or Forms!SearchPractician!Lang Is
Null) And (([Läkare List].Phonenumber) LIKE Forms!SearchPractician!Tel &
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare List].Faxnumber) LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax Is Null)
And
(([Läkare List].[E-mail address]) LIKE Forms!SearchPractician!Epost & "*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare List].Street) LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str Is Null)
And
(([Läkare List].[Postal Code + City]) LIKE Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*" Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query to view it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is Null

But now I would like to have this to work when the users input only a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[[MVP] S.Clark]

WHERE ((([Läkare List].[Last name]) Like [Forms]![SearchPractician]![LN] &
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I removed that one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I combine ? How ?
This one works...
WHERE ((([Läkare List].[First name]) Like [Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like [Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of things, but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice

Do you understand that this part of your query string doesn't do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query can do
with
it.

You need to translate whatever that UI input is to something relevant in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the focus lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code + City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare List].[Last name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN Is Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax Is Null)
And
(([Läkare List].[E-mail address]) LIKE Forms!SearchPractician!Epost &
"*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare List].Street) LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str Is Null)
And
(([Läkare List].[Postal Code + City]) LIKE
Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice


:

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*" Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query to view
it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is Null

But now I would like to have this to work when the users input only
a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[Guest]

Thanks,
I guess I didn't make myself clear before. sorry that I am wasting your time
on this but how do I add extra conditions so that I can put a B for first
name to show all doctors who have first name starting with b AND/OR Last name
starting with J as inputted in other field. And/Or phonenumber starting on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to a field in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them together ?

Thanks again,m

Ben

--
Novice

WHERE ((([Läkare List].[Last name]) Like [Forms]![SearchPractician]![LN] &
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I removed that one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I combine ? How ?
This one works...
WHERE ((([Läkare List].[First name]) Like [Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like [Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of things, but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice

Do you understand that this part of your query string doesn't do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query can do
with
it.

You need to translate whatever that UI input is to something relevant in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the focus lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code + City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare List].[Last name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN Is Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax Is Null)
And
(([Läkare List].[E-mail address]) LIKE Forms!SearchPractician!Epost &
"*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare List].Street) LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str Is Null)
And
(([Läkare List].[Postal Code + City]) LIKE
Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice


:

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*" Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query to view
it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is Null

But now I would like to have this to work when the users input only
a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[[MVP] S.Clark]

No waste, no apology needed. Remember, one other person might read this and
find value.

It comes down to basic Order of Operations(Arithmetic)

These two are the same:
Where A OR B AND C
Where A OR (B AND C)

But this one is different
Where (A OR B) AND C

Your need is like the last one. Just exchange the letters for your
criteria.

Thanks,
I guess I didn't make myself clear before. sorry that I am wasting your
time
on this but how do I add extra conditions so that I can put a B for first
name to show all doctors who have first name starting with b AND/OR Last
name
starting with J as inputted in other field. And/Or phonenumber starting on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to a field
in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them together ?

Thanks again,m

Ben

--
Novice

WHERE ((([Läkare List].[Last name]) Like [Forms]![SearchPractician]![LN]
&
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I removed that
one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I combine ?
How ?
This one works...
WHERE ((([Läkare List].[First name]) Like
[Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of things,
but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice


:

Do you understand that this part of your query string doesn't do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query can do
with
it.

You need to translate whatever that UI input is to something relevant
in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the focus
lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code + City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE
Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare List].[Last
name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN Is
Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or
Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE
Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare
List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax Is
Null)
And
(([Läkare List].[E-mail address]) LIKE Forms!SearchPractician!Epost
&
"*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare List].Street)
LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str Is
Null)
And
(([Läkare List].[Postal Code + City]) LIKE
Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice


:

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*" Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query to
view
it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is Null

But now I would like to have this to work when the users input
only
a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[Guest]

Well, hope you had a nice weekend.
Thanks again but it still didn't operated the way it should... here is how I
replaced in you suggestion...

WHERE (([Läkare List].[Last name]) Like Forms!SearchPractician!LN & "*") Or
(NOT IsNull([Läkare List].[Last Name])) AND (([Läkare List].[First name])
Like Forms!SearchPractician!FN & "*") Or (NOT IsNull([Läkare List].[First
Name]));

Still only works for the Last name, not for the first name and there are
more criteria to add
Thanks

--
Novice

No waste, no apology needed. Remember, one other person might read this and
find value.

It comes down to basic Order of Operations(Arithmetic)

These two are the same:
Where A OR B AND C
Where A OR (B AND C)

But this one is different
Where (A OR B) AND C

Your need is like the last one. Just exchange the letters for your
criteria.

Thanks,
I guess I didn't make myself clear before. sorry that I am wasting your
time
on this but how do I add extra conditions so that I can put a B for first
name to show all doctors who have first name starting with b AND/OR Last
name
starting with J as inputted in other field. And/Or phonenumber starting on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to a field
in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them together ?

Thanks again,m

Ben

--
Novice

WHERE ((([Läkare List].[Last name]) Like [Forms]![SearchPractician]![LN]
&
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I removed that
one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I combine ?
How ?
This one works...
WHERE ((([Läkare List].[First name]) Like
[Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of things,
but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice


:

Do you understand that this part of your query string doesn't do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query can do
with
it.

You need to translate whatever that UI input is to something relevant
in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the focus
lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code + City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE
Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare List].[Last
name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN Is
Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or
Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE
Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare
List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax Is
Null)
And
(([Läkare List].[E-mail address]) LIKE Forms!SearchPractician!Epost
&
"*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare List].Street)
LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str Is
Null)
And
(([Läkare List].[Postal Code + City]) LIKE
Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice


:

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*" Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query to
view
it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is Null

But now I would like to have this to work when the users input
only
a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[[MVP] S.Clark]

Suppose that ContactName and ContactTitle are LastName and FirstName. Is
this for what you strive?

WHERE
(((Customers.ContactName) Like "Test*")
AND
(Not (Customers.ContactName) Is Null))
OR
(((Customers.ContactTitle) Like "Test*")
AND
(Not (Customers.ContactTitle) Is Null));

Well, hope you had a nice weekend.
Thanks again but it still didn't operated the way it should... here is how
I
replaced in you suggestion...

WHERE (([Läkare List].[Last name]) Like Forms!SearchPractician!LN & "*")
Or
(NOT IsNull([Läkare List].[Last Name])) AND (([Läkare List].[First name])
Like Forms!SearchPractician!FN & "*") Or (NOT IsNull([Läkare List].[First
Name]));

Still only works for the Last name, not for the first name and there are
more criteria to add
Thanks

--
Novice

No waste, no apology needed. Remember, one other person might read this
and
find value.

It comes down to basic Order of Operations(Arithmetic)

These two are the same:
Where A OR B AND C
Where A OR (B AND C)

But this one is different
Where (A OR B) AND C

Your need is like the last one. Just exchange the letters for your
criteria.

Thanks,
I guess I didn't make myself clear before. sorry that I am wasting your
time
on this but how do I add extra conditions so that I can put a B for
first
name to show all doctors who have first name starting with b AND/OR
Last
name
starting with J as inputted in other field. And/Or phonenumber starting
on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to a
field
in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them
together ?

Thanks again,m

Ben

--
Novice


:

WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN]
&
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I removed
that
one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I combine ?
How ?
This one works...
WHERE ((([Läkare List].[First name]) Like
[Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of
things,
but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice


:

Do you understand that this part of your query string doesn't do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query can
do
with
it.

You need to translate whatever that UI input is to something
relevant
in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the focus
lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code +
City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE
Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare List].[Last
name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN Is
Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or
Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE
Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare
List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax Is
Null)
And
(([Läkare List].[E-mail address]) LIKE
Forms!SearchPractician!Epost
&
"*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare List].Street)
LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str Is
Null)
And
(([Läkare List].[Postal Code + City]) LIKE
Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not
enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice


:

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*"
Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query to
view
it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is
Null

But now I would like to have this to work when the users input
only
a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[Guest]

Yes, this is what I strive... I tried implementing this and considering
"Test*" as My unbound fields on form "SearchPractician" but it just doesn't
work to make two fields searchable...
My goal is a similar thing as the FilterOnForm but returning a continuous
form instead of a unique record...
Any other suggestions to get something like this because this appears to be
endless struggling...
I am a tough student huh, master ?



--
Novice

Suppose that ContactName and ContactTitle are LastName and FirstName. Is
this for what you strive?

WHERE
(((Customers.ContactName) Like "Test*")
AND
(Not (Customers.ContactName) Is Null))
OR
(((Customers.ContactTitle) Like "Test*")
AND
(Not (Customers.ContactTitle) Is Null));

Well, hope you had a nice weekend.
Thanks again but it still didn't operated the way it should... here is how
I
replaced in you suggestion...

WHERE (([Läkare List].[Last name]) Like Forms!SearchPractician!LN & "*")
Or
(NOT IsNull([Läkare List].[Last Name])) AND (([Läkare List].[First name])
Like Forms!SearchPractician!FN & "*") Or (NOT IsNull([Läkare List].[First
Name]));

Still only works for the Last name, not for the first name and there are
more criteria to add
Thanks

--
Novice

No waste, no apology needed. Remember, one other person might read this
and
find value.

It comes down to basic Order of Operations(Arithmetic)

These two are the same:
Where A OR B AND C
Where A OR (B AND C)

But this one is different
Where (A OR B) AND C

Your need is like the last one. Just exchange the letters for your
criteria.

Thanks,
I guess I didn't make myself clear before. sorry that I am wasting your
time
on this but how do I add extra conditions so that I can put a B for
first
name to show all doctors who have first name starting with b AND/OR
Last
name
starting with J as inputted in other field. And/Or phonenumber starting
on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to a
field
in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them
together ?

Thanks again,m

Ben

--
Novice


:

WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN]
&
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I removed
that
one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I combine ?
How ?
This one works...
WHERE ((([Läkare List].[First name]) Like
[Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of
things,
but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice


:

Do you understand that this part of your query string doesn't do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query can
do
with
it.

You need to translate whatever that UI input is to something
relevant
in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the focus
lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code +
City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE
Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare List].[Last
name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN Is
Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or
Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE
Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare
List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax Is
Null)
And
(([Läkare List].[E-mail address]) LIKE
Forms!SearchPractician!Epost
&
"*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare List].Street)
LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str Is
Null)
And
(([Läkare List].[Postal Code + City]) LIKE
Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not
enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice


:

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] & "*"
Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query to
view
it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] Is
Null

But now I would like to have this to work when the users input
only
a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[[MVP] S.Clark]

Did you implement the WHERE clause that I put in the last post, as it is
different from your last one?

Yes, this is what I strive... I tried implementing this and considering
"Test*" as My unbound fields on form "SearchPractician" but it just
doesn't
work to make two fields searchable...
My goal is a similar thing as the FilterOnForm but returning a continuous
form instead of a unique record...
Any other suggestions to get something like this because this appears to
be
endless struggling...
I am a tough student huh, master ?



--
Novice

Suppose that ContactName and ContactTitle are LastName and FirstName. Is
this for what you strive?

WHERE
(((Customers.ContactName) Like "Test*")
AND
(Not (Customers.ContactName) Is Null))
OR
(((Customers.ContactTitle) Like "Test*")
AND
(Not (Customers.ContactTitle) Is Null));

Well, hope you had a nice weekend.
Thanks again but it still didn't operated the way it should... here is
how
I
replaced in you suggestion...

WHERE (([Läkare List].[Last name]) Like Forms!SearchPractician!LN &
"*")
Or
(NOT IsNull([Läkare List].[Last Name])) AND (([Läkare List].[First
name])
Like Forms!SearchPractician!FN & "*") Or (NOT IsNull([Läkare
List].[First
Name]));

Still only works for the Last name, not for the first name and there
are
more criteria to add
Thanks

--
Novice


:

No waste, no apology needed. Remember, one other person might read
this
and
find value.

It comes down to basic Order of Operations(Arithmetic)

These two are the same:
Where A OR B AND C
Where A OR (B AND C)

But this one is different
Where (A OR B) AND C

Your need is like the last one. Just exchange the letters for your
criteria.

Thanks,
I guess I didn't make myself clear before. sorry that I am wasting
your
time
on this but how do I add extra conditions so that I can put a B for
first
name to show all doctors who have first name starting with b AND/OR
Last
name
starting with J as inputted in other field. And/Or phonenumber
starting
on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to a
field
in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them
together ?

Thanks again,m

Ben

--
Novice


:

WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN]
&
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I removed
that
one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I
combine ?
How ?
This one works...
WHERE ((([Läkare List].[First name]) Like
[Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of
things,
but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice


:

Do you understand that this part of your query string doesn't do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query
can
do
with
it.

You need to translate whatever that UI input is to something
relevant
in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the
focus
lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code +
City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE
Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare
List].[Last
name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN
Is
Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or
Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE
Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare
List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax
Is
Null)
And
(([Läkare List].[E-mail address]) LIKE
Forms!SearchPractician!Epost
&
"*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare
List].Street)
LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str
Is
Null)
And
(([Läkare List].[Postal Code + City]) LIKE
Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not
enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice


:

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] &
"*"
Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query
to
view
it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3]
Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3]
Is
Null

But now I would like to have this to work when the users
input
only
a
couple
of letters... instead of the full word/name.

Thanks,
Ben

 

[Guest]

Yes, I implemented it like below :
WHERE ((([Läkare List].[Last Name] Like Forms!SearchPractician!LN & "*") AND
(NOT ([Läkare List].[Last Name]) Is Null)) OR (([Läkare List].[First Name]
Like Forms!SearchPractician!FN & "*") AND (NOT ([Läkare List].[First Name])
Is Null)));

But this didn't give any results... so I tried like this:
WHERE ((([Läkare List].[Last Name] Like"J*") AND (NOT ([Läkare List].[Last
Name]) Is Null)) OR (([Läkare List].[First Name] Like "S*") AND (NOT
([Läkare List].[First Name]) Is Null)));

Query result on this is...
Sara Jones
Steve Difrotta

Where I believe that it should only show Sara Jones as I asked S* and J*...



--
Novice

Did you implement the WHERE clause that I put in the last post, as it is
different from your last one?

Yes, this is what I strive... I tried implementing this and considering
"Test*" as My unbound fields on form "SearchPractician" but it just
doesn't
work to make two fields searchable...
My goal is a similar thing as the FilterOnForm but returning a continuous
form instead of a unique record...
Any other suggestions to get something like this because this appears to
be
endless struggling...
I am a tough student huh, master ?



--
Novice

Suppose that ContactName and ContactTitle are LastName and FirstName. Is
this for what you strive?

WHERE
(((Customers.ContactName) Like "Test*")
AND
(Not (Customers.ContactName) Is Null))
OR
(((Customers.ContactTitle) Like "Test*")
AND
(Not (Customers.ContactTitle) Is Null));

Well, hope you had a nice weekend.
Thanks again but it still didn't operated the way it should... here is
how
I
replaced in you suggestion...

WHERE (([Läkare List].[Last name]) Like Forms!SearchPractician!LN &
"*")
Or
(NOT IsNull([Läkare List].[Last Name])) AND (([Läkare List].[First
name])
Like Forms!SearchPractician!FN & "*") Or (NOT IsNull([Läkare
List].[First
Name]));

Still only works for the Last name, not for the first name and there
are
more criteria to add
Thanks

--
Novice


:

No waste, no apology needed. Remember, one other person might read
this
and
find value.

It comes down to basic Order of Operations(Arithmetic)

These two are the same:
Where A OR B AND C
Where A OR (B AND C)

But this one is different
Where (A OR B) AND C

Your need is like the last one. Just exchange the letters for your
criteria.

Thanks,
I guess I didn't make myself clear before. sorry that I am wasting
your
time
on this but how do I add extra conditions so that I can put a B for
first
name to show all doctors who have first name starting with b AND/OR
Last
name
starting with J as inputted in other field. And/Or phonenumber
starting
on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to a
field
in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them
together ?

Thanks again,m

Ben

--
Novice


:

WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN]
&
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I removed
that
one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I
combine ?
How ?
This one works...
WHERE ((([Läkare List].[First name]) Like
[Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of
things,
but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice


:

Do you understand that this part of your query string doesn't do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the Query
can
do
with
it.

You need to translate whatever that UI input is to something
relevant
in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the
focus
lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID], [Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name], [Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code +
City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And (([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE
Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare
List].[Last
name])
LIKE
Forms!SearchPractician!LN & "*" Or Forms!SearchPractician!LN
Is
Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or
Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE
Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare
List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or Forms!SearchPractician!Fax
Is
Null)
And
(([Läkare List].[E-mail address]) LIKE
Forms!SearchPractician!Epost
&
"*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare
List].Street)
LIKE
Forms!SearchPractician!Str & "*" Or Forms!SearchPractician!Str
Is
Null)
And
(([Läkare List].[Postal Code + City]) LIKE
Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not
enough
memory...
is this too tough for access or am I doing something wrong ?

Thanks again,
Ben
--
Novice


:

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] &
"*"
Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a query
to
view
it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3]
Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3]
Is

 

[[MVP] S.Clark]

You can always write some VBA code that writes a dynamic query based on the
UI entries.

with txtLN
if not isnull(.value) then
strW = "LastName LIKE '" & .value & "*'"
End With

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Yes, I implemented it like below :
WHERE ((([Läkare List].[Last Name] Like Forms!SearchPractician!LN & "*")
AND
(NOT ([Läkare List].[Last Name]) Is Null)) OR (([Läkare List].[First
Name]
Like Forms!SearchPractician!FN & "*") AND (NOT ([Läkare List].[First
Name])
Is Null)));

But this didn't give any results... so I tried like this:
WHERE ((([Läkare List].[Last Name] Like"J*") AND (NOT ([Läkare List].[Last
Name]) Is Null)) OR (([Läkare List].[First Name] Like "S*") AND (NOT
([Läkare List].[First Name]) Is Null)));

Query result on this is...
Sara Jones
Steve Difrotta

Where I believe that it should only show Sara Jones as I asked S* and
J*...



--
Novice

Did you implement the WHERE clause that I put in the last post, as it is
different from your last one?

Yes, this is what I strive... I tried implementing this and considering
"Test*" as My unbound fields on form "SearchPractician" but it just
doesn't
work to make two fields searchable...
My goal is a similar thing as the FilterOnForm but returning a
continuous
form instead of a unique record...
Any other suggestions to get something like this because this appears
to
be
endless struggling...
I am a tough student huh, master ?



--
Novice


:

Suppose that ContactName and ContactTitle are LastName and FirstName.
Is
this for what you strive?

WHERE
(((Customers.ContactName) Like "Test*")
AND
(Not (Customers.ContactName) Is Null))
OR
(((Customers.ContactTitle) Like "Test*")
AND
(Not (Customers.ContactTitle) Is Null));

Well, hope you had a nice weekend.
Thanks again but it still didn't operated the way it should... here
is
how
I
replaced in you suggestion...

WHERE (([Läkare List].[Last name]) Like Forms!SearchPractician!LN &
"*")
Or
(NOT IsNull([Läkare List].[Last Name])) AND (([Läkare List].[First
name])
Like Forms!SearchPractician!FN & "*") Or (NOT IsNull([Läkare
List].[First
Name]));

Still only works for the Last name, not for the first name and there
are
more criteria to add
Thanks

--
Novice


:

No waste, no apology needed. Remember, one other person might read
this
and
find value.

It comes down to basic Order of Operations(Arithmetic)

These two are the same:
Where A OR B AND C
Where A OR (B AND C)

But this one is different
Where (A OR B) AND C

Your need is like the last one. Just exchange the letters for your
criteria.

Thanks,
I guess I didn't make myself clear before. sorry that I am
wasting
your
time
on this but how do I add extra conditions so that I can put a B
for
first
name to show all doctors who have first name starting with b
AND/OR
Last
name
starting with J as inputted in other field. And/Or phonenumber
starting
on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to
a
field
in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them
together ?

Thanks again,m

Ben

--
Novice


:

WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN]
&
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I
removed
that
one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I
combine ?
How ?
This one works...
WHERE ((([Läkare List].[First name]) Like
[Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of
things,
but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice


:

Do you understand that this part of your query string doesn't
do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the
Query
can
do
with
it.

You need to translate whatever that UI input is to something
relevant
in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the
focus
lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID],
[Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name],
[Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code
+
City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And
(([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE
Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare
List].[Last
name])
LIKE
Forms!SearchPractician!LN & "*" Or
Forms!SearchPractician!LN
Is
Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or
Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE
Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare
List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or
Forms!SearchPractician!Fax
Is
Null)
And
(([Läkare List].[E-mail address]) LIKE
Forms!SearchPractician!Epost
&
"*"
Or
Forms!SearchPractician!Epost Is Null) And (([Läkare
List].Street)
LIKE
Forms!SearchPractician!Str & "*" Or
Forms!SearchPractician!Str
Is
Null)
And
(([Läkare List].[Postal Code + City]) LIKE
Forms!SearchPractician!PCCity &
"*" Or Forms!SearchPractician!PCCity Is Null));


Oh, BTW I can't open my query in design. Error message: not
enough
memory...
is this too tough for access or am I doing something wrong
?

Thanks again,
Ben
--
Novice


:

Use the LIKE Operator:

WHERE Fieldname LIKE
Forms![SelectRunningProjectForOverview]![Kommandoknapp3] &
"*"
Or
IsNull(Fieldname)

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using unbound form fields to create a filter in a
query
to
view
it
on
a
continuous form.
Works great with :

Forms![SelectRunningProjectForOverview]![Kommandoknapp3]
Or
Forms![SelectRunningProjectForOverview]![Kommandoknapp3]
Is

 

[Guest]

Seems like a possible solution but hehe... where do I have to put it then ?
Merry Xmas

Ben
--
Novice

You can always write some VBA code that writes a dynamic query based on the
UI entries.

with txtLN
if not isnull(.value) then
strW = "LastName LIKE '" & .value & "*'"
End With

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Yes, I implemented it like below :
WHERE ((([Läkare List].[Last Name] Like Forms!SearchPractician!LN & "*")
AND
(NOT ([Läkare List].[Last Name]) Is Null)) OR (([Läkare List].[First
Name]
Like Forms!SearchPractician!FN & "*") AND (NOT ([Läkare List].[First
Name])
Is Null)));

But this didn't give any results... so I tried like this:
WHERE ((([Läkare List].[Last Name] Like"J*") AND (NOT ([Läkare List].[Last
Name]) Is Null)) OR (([Läkare List].[First Name] Like "S*") AND (NOT
([Läkare List].[First Name]) Is Null)));

Query result on this is...
Sara Jones
Steve Difrotta

Where I believe that it should only show Sara Jones as I asked S* and
J*...



--
Novice

Did you implement the WHERE clause that I put in the last post, as it is
different from your last one?

Yes, this is what I strive... I tried implementing this and considering
"Test*" as My unbound fields on form "SearchPractician" but it just
doesn't
work to make two fields searchable...
My goal is a similar thing as the FilterOnForm but returning a
continuous
form instead of a unique record...
Any other suggestions to get something like this because this appears
to
be
endless struggling...
I am a tough student huh, master ?



--
Novice


:

Suppose that ContactName and ContactTitle are LastName and FirstName.
Is
this for what you strive?

WHERE
(((Customers.ContactName) Like "Test*")
AND
(Not (Customers.ContactName) Is Null))
OR
(((Customers.ContactTitle) Like "Test*")
AND
(Not (Customers.ContactTitle) Is Null));

Well, hope you had a nice weekend.
Thanks again but it still didn't operated the way it should... here
is
how
I
replaced in you suggestion...

WHERE (([Läkare List].[Last name]) Like Forms!SearchPractician!LN &
"*")
Or
(NOT IsNull([Läkare List].[Last Name])) AND (([Läkare List].[First
name])
Like Forms!SearchPractician!FN & "*") Or (NOT IsNull([Läkare
List].[First
Name]));

Still only works for the Last name, not for the first name and there
are
more criteria to add
Thanks

--
Novice


:

No waste, no apology needed. Remember, one other person might read
this
and
find value.

It comes down to basic Order of Operations(Arithmetic)

These two are the same:
Where A OR B AND C
Where A OR (B AND C)

But this one is different
Where (A OR B) AND C

Your need is like the last one. Just exchange the letters for your
criteria.

Thanks,
I guess I didn't make myself clear before. sorry that I am
wasting
your
time
on this but how do I add extra conditions so that I can put a B
for
first
name to show all doctors who have first name starting with b
AND/OR
Last
name
starting with J as inputted in other field. And/Or phonenumber
starting
on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to
a
field
in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them
together ?

Thanks again,m

Ben

--
Novice


:

WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN]
&
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I
removed
that
one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I
combine ?
How ?
This one works...
WHERE ((([Läkare List].[First name]) Like
[Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of
things,
but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice


:

Do you understand that this part of your query string doesn't
do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the
Query
can
do
with
it.

You need to translate whatever that UI input is to something
relevant
in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the
focus
lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID],
[Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name],
[Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code
+
City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And
(([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE
Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare
List].[Last
name])
LIKE
Forms!SearchPractician!LN & "*" Or
Forms!SearchPractician!LN
Is
Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or
Forms!SearchPractician!Lang
Is
Null) And (([Läkare List].Phonenumber) LIKE
Forms!SearchPractician!Tel
&
"*"
Or Forms!SearchPractician!Tel Is Null) And (([Läkare
List].Faxnumber)
LIKE
Forms!SearchPractician!Fax & "*" Or
Forms!SearchPractician!Fax

 

[Guest]

Well, I found it the answer to my last Q via another forum question... but
thanks a million for your help...
Here is what it became, in case someone would need it...

Private Sub SearchButton_Click()
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT [Doctorsearchquery].ID,
[Doctorsearchquery].[Country ID], [Doctorsearchquery].Region,
[Doctorsearchquery].[First name], [Doctorsearchquery].[Last name],
[Doctorsearchquery].[Name Practice/Hospital],
[Doctorsearchquery].[Male/Female], [Doctorsearchquery].[Language ID],
[Doctorsearchquery].Phonenumber, [Doctorsearchquery].Faxnumber,
[Doctorsearchquery].[E-mail address], [Doctorsearchquery].Street,
[Doctorsearchquery].[Postal Code + City] FROM [Doctorsearchquery]"


'Build the WHERE clause from the non-Null boxes.
If Not IsNull(Me.FN) Then
strSql = strSql & "([First Name] Like ""*" & Me.FN & "*"" ) AND """
End If

If Not IsNull(Me.LN) Then
strSql = strSql & "([Last Name] Like ""*" & Me.LN & "*"") AND "
End If

If Not IsNull(Me.NPH) Then
strSql = strSql & "([Name Practice/Hospital] Like ""*" & Me.NPH &
"*"") AND "
End If

If Not IsNull(Me.Tel) Then
strSql = strSql & "([Phonenumber] Like ""*" & Me.Tel & "*"") AND "
End If

If Not IsNull(Me.Fax) Then
strSql = strSql & "([Faxnumber] Like ""*" & Me.Fax & "*"") AND "
End If

If Not IsNull(Me.Epost) Then
strSql = strSql & "([E-mail address] Like ""*" & Me.Epost & "*"")
AND "
End If

If Not IsNull(Me.Str) Then
strSql = strSql & "([Street] Like ""*" & Me.Str & "*"") AND "
End If

If Not IsNull(Me.PCCity) Then
strSql = strSql & "([Postal Code + City]Like ""*" & Me.PCCity &
"*"") AND "
End If

'etc for other text boxes.

lngLen = Len(strSql) - 5 'Chop off the trailiing " AND ".
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

'Finally, show the search results in this form.
Me.RecordSource = strSql
End Sub


--
Novice

Seems like a possible solution but hehe... where do I have to put it then ?
Merry Xmas

Ben
--
Novice

You can always write some VBA code that writes a dynamic query based on the
UI entries.

with txtLN
if not isnull(.value) then
strW = "LastName LIKE '" & .value & "*'"
End With

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Yes, I implemented it like below :
WHERE ((([Läkare List].[Last Name] Like Forms!SearchPractician!LN & "*")
AND
(NOT ([Läkare List].[Last Name]) Is Null)) OR (([Läkare List].[First
Name]
Like Forms!SearchPractician!FN & "*") AND (NOT ([Läkare List].[First
Name])
Is Null)));

But this didn't give any results... so I tried like this:
WHERE ((([Läkare List].[Last Name] Like"J*") AND (NOT ([Läkare List].[Last
Name]) Is Null)) OR (([Läkare List].[First Name] Like "S*") AND (NOT
([Läkare List].[First Name]) Is Null)));

Query result on this is...
Sara Jones
Steve Difrotta

Where I believe that it should only show Sara Jones as I asked S* and
J*...



--
Novice


:

Did you implement the WHERE clause that I put in the last post, as it is
different from your last one?

Yes, this is what I strive... I tried implementing this and considering
"Test*" as My unbound fields on form "SearchPractician" but it just
doesn't
work to make two fields searchable...
My goal is a similar thing as the FilterOnForm but returning a
continuous
form instead of a unique record...
Any other suggestions to get something like this because this appears
to
be
endless struggling...
I am a tough student huh, master ?



--
Novice


:

Suppose that ContactName and ContactTitle are LastName and FirstName.
Is
this for what you strive?

WHERE
(((Customers.ContactName) Like "Test*")
AND
(Not (Customers.ContactName) Is Null))
OR
(((Customers.ContactTitle) Like "Test*")
AND
(Not (Customers.ContactTitle) Is Null));

Well, hope you had a nice weekend.
Thanks again but it still didn't operated the way it should... here
is
how
I
replaced in you suggestion...

WHERE (([Läkare List].[Last name]) Like Forms!SearchPractician!LN &
"*")
Or
(NOT IsNull([Läkare List].[Last Name])) AND (([Läkare List].[First
name])
Like Forms!SearchPractician!FN & "*") Or (NOT IsNull([Läkare
List].[First
Name]));

Still only works for the Last name, not for the first name and there
are
more criteria to add
Thanks

--
Novice


:

No waste, no apology needed. Remember, one other person might read
this
and
find value.

It comes down to basic Order of Operations(Arithmetic)

These two are the same:
Where A OR B AND C
Where A OR (B AND C)

But this one is different
Where (A OR B) AND C

Your need is like the last one. Just exchange the letters for your
criteria.

Thanks,
I guess I didn't make myself clear before. sorry that I am
wasting
your
time
on this but how do I add extra conditions so that I can put a B
for
first
name to show all doctors who have first name starting with b
AND/OR
Last
name
starting with J as inputted in other field. And/Or phonenumber
starting
on
016... and so on...

I have 7 fields on my [SelectPractician] form (all correspond to
a
field
in
my query/table for filter/search)

this means seven separate WHERE conditions... how to bring them
together ?

Thanks again,m

Ben

--
Novice


:

WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN]
&
"*")) OR (NOT IsNull([Läkare List].[Last name]))

Steve,
Thanks for pointing out to me about the country thing, I
removed
that
one
as
I have a sort on that in the continuous form anyway...

But hmmm, I made it work on a single one... but now, Can I
combine ?
How ?
This one works...
WHERE ((([Läkare List].[First name]) Like
[Forms]![SearchPractician]![FN]
&
"*")) OR (((IsNull([Läkare List].[Fast name]))<>False))

But how to add this one so that you can search on both ?
WHERE ((([Läkare List].[Last name]) Like
[Forms]![SearchPractician]![LN] &
"*")) OR (((IsNull([Läkare List].[Last name]))<>False))

Or one of them but give all possibilities...
I can't seem to get them to work together... tried a couple of
things,
but
I
am not an expert at all (guess that was obvious already).

Thanks again,
Ben
--
Novice


:

Do you understand that this part of your query string doesn't
do
anything:

Forms!SearchPractician!Country Is Null

That is not a field in your table, so there's nothing the
Query
can
do
with
it.

You need to translate whatever that UI input is to something
relevant
in
the
table.

Thanks both... but it doesn't do it

Here's what I tried to make out of it with your help... the
focus
lies
on
this as you suggested :

WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null)

And here is my complete query:

SELECT [Läkare List].ID, [Läkare List].[Country ID],
[Läkare
List].Region,
[Läkare List].[First name], [Läkare List].[Last name],
[Läkare
List].[Name
Practice/Hospital], [Läkare List].[Male/Female], [Läkare
List].[Language
ID],
[Läkare List].Phonenumber, [Läkare List].Faxnumber, [Läkare
List].[E-mail
address], [Läkare List].Street, [Läkare List].[Postal Code
+
City]
FROM [Läkare List]
WHERE ((([Läkare List].[Country ID]) LIKE
Forms!SearchPractician!Country &
"*" Or Forms!SearchPractician!Country Is Null) And
(([Läkare
List].Region)
LIKE Forms!SearchPractician!Region & "*" Or
Forms!SearchPractician!Region
Is
Null) And (([Läkare List].[First name]) LIKE
Forms!SearchPractician!FN
&
"*"
Or Forms!SearchPractician!FN Is Null) And (([Läkare
List].[Last
name])
LIKE
Forms!SearchPractician!LN & "*" Or
Forms!SearchPractician!LN
Is
Null)
And
(([Läkare List].[Name Practice/Hospital]) LIKE
Forms!SearchPractician!NPH
&
"*" Or Forms!SearchPractician!NPH Is Null) And (([Läkare
List].[Language
ID])
LIKE Forms!SearchPractician!Lang & "*" Or
Forms!SearchPractician!Lang