Please help - subform filtering!

L

Leslie Isaacs

Hello All

This works:
Me.[Child0].Form.Filter = "[presc_patient] = [Forms]![frm prescriptions
main].[findwhat] "

.... but the problem is, I need the filter to search for records where
[presc_patient] CONTAINS [findwhat] - i.e. not just looking for an exact
match.

So I have tried:
Me.[Child0].Form.Filter = "[presc_patient] = ""*"" & [Forms]![frm
prescriptions main].[findwhat] & ""*"" "
and
Me.[Child0].Form.Filter = "[presc_patient] = ' * ' & [Forms]![frm
prescriptions main].[findwhat] & ' * ' "
.... but both of these return no records - even when the text entered in
[findwhat] is an exact match.

I have also tried:
Me.[Child0].Form.Filter = "[presc_patient] = """ & [Forms]![frm
prescriptions main].[findwhat] & """ "
.... but this only finds exact matches.

I should add that this code is behind a button on a main form (called [frm
prescriptions main]) and is intended to apply to the data displayed on a
subform (called [Child0]) on the main form. The text box [findwhat] is also
on the main form, and [presc_patient] is a field on the subform.

I hope someone can help with this - it's driving me nuts!

Many thanks
Les
 
N

Nikos Yannacopoulos

Les,

Try this:

Me.[Child0].Form.Filter = "[presc_patient] = '*" & [Forms]![frm
prescriptions main].[findwhat] & "*'"

Note the use of single quotes within the double ones, and no spaces
between quotes and *'s; the latter result in a search for a string
including the spaces!

HTH,
Nikos
 
L

Leslie Isaacs

Hello Nikos

Thanks for your reply.

I tried the code you suggested, but unfortunately it didn't work: no records
are returned, irrespective of the search string entered.
Is it just me, or is this one step away from a bug?

I would be very grateful if you have any further suggestions.

Many thanks
Les



Nikos Yannacopoulos said:
Les,

Try this:

Me.[Child0].Form.Filter = "[presc_patient] = '*" & [Forms]![frm
prescriptions main].[findwhat] & "*'"

Note the use of single quotes within the double ones, and no spaces
between quotes and *'s; the latter result in a search for a string
including the spaces!

HTH,
Nikos

Leslie said:
Hello All

This works:
Me.[Child0].Form.Filter = "[presc_patient] = [Forms]![frm prescriptions
main].[findwhat] "

... but the problem is, I need the filter to search for records where
[presc_patient] CONTAINS [findwhat] - i.e. not just looking for an exact
match.

So I have tried:
Me.[Child0].Form.Filter = "[presc_patient] = ""*"" & [Forms]![frm
prescriptions main].[findwhat] & ""*"" "
and
Me.[Child0].Form.Filter = "[presc_patient] = ' * ' & [Forms]![frm
prescriptions main].[findwhat] & ' * ' "
... but both of these return no records - even when the text entered in
[findwhat] is an exact match.

I have also tried:
Me.[Child0].Form.Filter = "[presc_patient] = """ & [Forms]![frm
prescriptions main].[findwhat] & """ "
... but this only finds exact matches.

I should add that this code is behind a button on a main form (called [frm
prescriptions main]) and is intended to apply to the data displayed on a
subform (called [Child0]) on the main form. The text box [findwhat] is also
on the main form, and [presc_patient] is a field on the subform.

I hope someone can help with this - it's driving me nuts!

Many thanks
Les
Click to expand...
Click to expand...
 
N

Nikos Yannacopoulos

Les,

If the first one in your original post worked, then I can't think of a
reason why this shouldn't.
Try adding this line right aferwords:

Debug.Print Me.[Child0].Form.Filter

and look at the filter expression in the immediate window. Is it what
you expected?

HTH,
Nikos

Leslie said:
Hello Nikos

Thanks for your reply.

I tried the code you suggested, but unfortunately it didn't work: no records
are returned, irrespective of the search string entered.
Is it just me, or is this one step away from a bug?

I would be very grateful if you have any further suggestions.

Many thanks
Les



Les,

Try this:

Me.[Child0].Form.Filter = "[presc_patient] = '*" & [Forms]![frm
prescriptions main].[findwhat] & "*'"

Note the use of single quotes within the double ones, and no spaces
between quotes and *'s; the latter result in a search for a string
including the spaces!

HTH,
Nikos

Leslie said:
Hello All

This works:
Me.[Child0].Form.Filter = "[presc_patient] = [Forms]![frm prescriptions
main].[findwhat] "

... but the problem is, I need the filter to search for records where
[presc_patient] CONTAINS [findwhat] - i.e. not just looking for an exact
match.

So I have tried:
Me.[Child0].Form.Filter = "[presc_patient] = ""*"" & [Forms]![frm
prescriptions main].[findwhat] & ""*"" "
and
Me.[Child0].Form.Filter = "[presc_patient] = ' * ' & [Forms]![frm
prescriptions main].[findwhat] & ' * ' "
... but both of these return no records - even when the text entered in
[findwhat] is an exact match.

I have also tried:
Me.[Child0].Form.Filter = "[presc_patient] = """ & [Forms]![frm
prescriptions main].[findwhat] & """ "
... but this only finds exact matches.

I should add that this code is behind a button on a main form (called
Click to expand...
[frm
prescriptions main]) and is intended to apply to the data displayed on a
subform (called [Child0]) on the main form. The text box [findwhat] is
Click to expand...
also
on the main form, and [presc_patient] is a field on the subform.

I hope someone can help with this - it's driving me nuts!

Many thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
L

Leslie Isaacs

Hello Nikos

Thankd for your further reply.

With the code set as
Me.[Child0].Form.Filter = "[presc_patient] = '*" & [Forms]![frm
prescriptions main].[findwhat] & "*'"
.... the filter expression in the intermediate window was:
[presc_patient] = '*isa*'
This IS NOT what I would have expected: it looks to me as if the asterisks
are being included as asterisks in the search string - instead of as wild
cards. Would you agree?

With the code set as
Me.[Child0].Form.Filter = "[presc_patient] = "" * "" & [Forms]![frm
prescriptions main].[findwhat] & "" * """

.... the filter expression in the intermediate window was:
[presc_patient] = " * " & [Forms]![frm prescriptions main].[findwhat] & " *
"
This IS what I would have expected: but it doesn't work.

I hope you will not give up on me with this!

Many thanks
Les



Nikos Yannacopoulos said:
Les,

If the first one in your original post worked, then I can't think of a
reason why this shouldn't.
Try adding this line right aferwords:

Debug.Print Me.[Child0].Form.Filter

and look at the filter expression in the immediate window. Is it what
you expected?

HTH,
Nikos

Leslie said:
Hello Nikos

Thanks for your reply.

I tried the code you suggested, but unfortunately it didn't work: no records
are returned, irrespective of the search string entered.
Is it just me, or is this one step away from a bug?

I would be very grateful if you have any further suggestions.

Many thanks
Les



Les,

Try this:

Me.[Child0].Form.Filter = "[presc_patient] = '*" & [Forms]![frm
prescriptions main].[findwhat] & "*'"

Note the use of single quotes within the double ones, and no spaces
between quotes and *'s; the latter result in a search for a string
including the spaces!

HTH,
Nikos

Leslie Isaacs wrote:

Hello All

This works:
Me.[Child0].Form.Filter = "[presc_patient] = [Forms]![frm prescriptions
main].[findwhat] "

... but the problem is, I need the filter to search for records where
[presc_patient] CONTAINS [findwhat] - i.e. not just looking for an exact
match.

So I have tried:
Me.[Child0].Form.Filter = "[presc_patient] = ""*"" & [Forms]![frm
prescriptions main].[findwhat] & ""*"" "
and
Me.[Child0].Form.Filter = "[presc_patient] = ' * ' & [Forms]![frm
prescriptions main].[findwhat] & ' * ' "
... but both of these return no records - even when the text entered in
[findwhat] is an exact match.

I have also tried:
Me.[Child0].Form.Filter = "[presc_patient] = """ & [Forms]![frm
prescriptions main].[findwhat] & """ "
... but this only finds exact matches.

I should add that this code is behind a button on a main form (called
[frm

prescriptions main]) and is intended to apply to the data displayed on a
subform (called [Child0]) on the main form. The text box [findwhat] is
also

on the main form, and [presc_patient] is a field on the subform.

I hope someone can help with this - it's driving me nuts!

Many thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
N

Nikos Yannacopoulos

Les,

Sorry, mate, I wasn't thinking! Of course it wouldn't work. When using
wildcards, the operator must be Like, not equal! This should do it:

Me.[Child0].Form.Filter = "[presc_patient] Like '*" & [Forms]![frm
prescriptions main].[findwhat] & "*'"

Again, apologies for the confusion.

HTH,
Nikos
 
N

Nikos Yannacopoulos

You're very welcome, and sorry for the confusion!

Leslie said:
Nikos, you're a star!
That works great, a thousand thanks!

Les




Les,

Sorry, mate, I wasn't thinking! Of course it wouldn't work. When using
wildcards, the operator must be Like, not equal! This should do it:

Me.[Child0].Form.Filter = "[presc_patient] Like '*" & [Forms]![frm
prescriptions main].[findwhat] & "*'"

Again, apologies for the confusion.

HTH,
Nikos
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform within a form 1
Help with DLookUp on subform 2
Resetting subform filters 4
PROBLEM: Subform total on main form returns #Name 5
Subform filtering 2
Problem referencing from subform 4
Suddenly getting #Name? 4
Filter for a subform 2

Top