Form.Filter problems

G

Guest

Hello all. To me this seems like a silly question; but, I can't seem to get
around some problems using Form.Filter:

More often than not I randomly get an error 2055 when I change the filter of
a form. The filter is fairly complex; but, it sometimes works. It's over 500
bytes long.

I've tried assigning an empty string to the property before setting it, and
I've tried turning the filter off first; but, I can't seem to get around this
error.

This is my latest code:

Me.FilterOn = False
Me.Filter = ""
Me.Filter = "(((Table1.field1=""Ofq"") AND (Table1.field2=""Asdfr
Pagsdre"")) " _
& "OR ((Table1.field1=""123456"") AND (Table1.field2=""Asdfr Pagsdre""))
" _
& "OR ((Table1.field1=""654321"") AND (Table1.field2=""Asdfr Pagsdre""))
" _
& "OR ((Table1.field1=""615243"") AND (Table1.[field three]=""Asdfr
Pagsdre"")) " _
& "OR ((Table1.field1=""folr kler kruifd"") AND (Table1.[field
three]=""Asdfr Pagsdre"")) " _
& "OR ((Table1.field1=""hjk g drf"") AND (Table1.[field three]=""Asdfr
Pagsdre"")) " _
& "OR ((Table1.field1=""ldkgi jflghjdffe"") AND (Table1.[field
three]=""Asdfr Pagsdre"")))"
Me.FilterOn = True ' exception 2005 here.

The error description is:
The expression 'The expression '03="Asdfr Pagsdre")) OR ((field1="folr kler
kruifd") AND (field 03="Asdfr Pagsdre")) OR ((field1="hjk g drf") AND (field
03="Asdfr Pagsdre")) OR ((field1="ldkgi jflghjdffe") AND (field 03="Asdfr
Pagsdre")))' you entered is invalid' you entered is invalid.

There's a couple of strange things in the description, one being the
repeated "The expression"; but more strange is the truncation and
modification of the filter string. All the Table1. prefixes have been
removed and the field delimiters ('[' & ']') have also been removed.

Thanks for any help
 
S

SteveS

Peter said:
Hello all. To me this seems like a silly question; but, I can't seem to get
around some problems using Form.Filter:

More often than not I randomly get an error 2055 when I change the filter of
a form. The filter is fairly complex; but, it sometimes works. It's over 500
bytes long.

I've tried assigning an empty string to the property before setting it, and
I've tried turning the filter off first; but, I can't seem to get around this
error.

This is my latest code:

Me.FilterOn = False
Me.Filter = ""
Me.Filter = "(((Table1.field1=""Ofq"") AND (Table1.field2=""Asdfr
Pagsdre"")) " _
& "OR ((Table1.field1=""123456"") AND (Table1.field2=""Asdfr Pagsdre""))
" _
& "OR ((Table1.field1=""654321"") AND (Table1.field2=""Asdfr Pagsdre""))
" _
& "OR ((Table1.field1=""615243"") AND (Table1.[field three]=""Asdfr
Pagsdre"")) " _
& "OR ((Table1.field1=""folr kler kruifd"") AND (Table1.[field
three]=""Asdfr Pagsdre"")) " _
& "OR ((Table1.field1=""hjk g drf"") AND (Table1.[field three]=""Asdfr
Pagsdre"")) " _
& "OR ((Table1.field1=""ldkgi jflghjdffe"") AND (Table1.[field
three]=""Asdfr Pagsdre"")))"
Me.FilterOn = True ' exception 2005 here.

The error description is:
The expression 'The expression '03="Asdfr Pagsdre")) OR ((field1="folr kler
kruifd") AND (field 03="Asdfr Pagsdre")) OR ((field1="hjk g drf") AND (field
03="Asdfr Pagsdre")) OR ((field1="ldkgi jflghjdffe") AND (field 03="Asdfr
Pagsdre")))' you entered is invalid' you entered is invalid.

There's a couple of strange things in the description, one being the
repeated "The expression"; but more strange is the truncation and
modification of the filter string. All the Table1. prefixes have been
removed and the field delimiters ('[' & ']') have also been removed.

Thanks for any help
Click to expand...


Peter,

Have you tried setting the filter to:

Me.Filter = "(Table1.field1= 'Ofq') AND (Table1.field2= 'Asdfr Pagsdre')"

***
NOTE: the doubled double quotes ("") were replaced with a single quote (')
***

If that works, try (watch for line wrap):

Me.Filter = "(((Table1.field1= 'Ofq') AND (Table1.field2= 'Asdfr Pagsdre')) " _
& "OR ((Table1.field1= '123456') AND (Table1.field2= 'Asdfr Pagsdre')) " _
& "OR ((Table1.field1= '654321') AND (Table1.field2= 'Asdfr Pagsdre')) " _

& "OR ((Table1.field1= '615243') AND (Table1.[field three]= 'Asdfr
Pagsdre')) " _
& "OR ((Table1.field1= 'folr kler kruifd') AND (Table1.[field three]=
'Asdfr Pagsdre')) " _
& "OR ((Table1.field1= 'hjk g drf') AND (Table1.[field three]= 'Asdfr
Pagsdre')) " _
& "OR ((Table1.field1= 'ldkgi jflghjdffe') AND (Table1.[field three]=
'Asdfr Pagsdre')))"


Actually, you have one condition (field2) AND'ed to three OR'ed conditions for
[field1] which is then OR'ed to [field three] AND'ed to four OR'ed conditions
for [field1]

Without the values, it looks like this:

"([field2] AND ([field1] OR [field1] OR [field1]))
OR
([field three] AND ([field1] OR [field1] OR [field1] OR [field1]))"


So, you could rewrite it as (watch for line wrap):

Me.Filter = "((Table1.field2= 'Asdfr Pagsdre') AND " _
& " (Table1.field1= 'Ofq' OR Table1.field1= '123456' OR " _
& " Table1.field1= '654321'))" _
& " OR " _
& "((Table1.[field three]= 'Asdfr Pagsdre') AND " _
& " (Table1.field1= '615243' OR Table1.field1= 'folr kler kruifd' OR " _
& " Table1.field1= 'hjk g drf' OR Table1.field1= 'ldkgi jflghjdffe'))



Hope some of this helps...
 
G

Guest

Hi SteveS. Yep, that was one of the first things I tried. Access uses the
double quotes when it generates the filter using "Filter by form", so I just
left it that way.

-- Peter

SteveS said:
Peter said:
Hello all. To me this seems like a silly question; but, I can't seem to get
around some problems using Form.Filter:

More often than not I randomly get an error 2055 when I change the filter of
a form. The filter is fairly complex; but, it sometimes works. It's over 500
bytes long.

I've tried assigning an empty string to the property before setting it, and
I've tried turning the filter off first; but, I can't seem to get around this
error.

This is my latest code:

Me.FilterOn = False
Me.Filter = ""
Me.Filter = "(((Table1.field1=""Ofq"") AND (Table1.field2=""Asdfr
Pagsdre"")) " _
& "OR ((Table1.field1=""123456"") AND (Table1.field2=""Asdfr Pagsdre""))
" _
& "OR ((Table1.field1=""654321"") AND (Table1.field2=""Asdfr Pagsdre""))
" _
& "OR ((Table1.field1=""615243"") AND (Table1.[field three]=""Asdfr
Pagsdre"")) " _
& "OR ((Table1.field1=""folr kler kruifd"") AND (Table1.[field
three]=""Asdfr Pagsdre"")) " _
& "OR ((Table1.field1=""hjk g drf"") AND (Table1.[field three]=""Asdfr
Pagsdre"")) " _
& "OR ((Table1.field1=""ldkgi jflghjdffe"") AND (Table1.[field
three]=""Asdfr Pagsdre"")))"
Me.FilterOn = True ' exception 2005 here.

The error description is:
The expression 'The expression '03="Asdfr Pagsdre")) OR ((field1="folr kler
kruifd") AND (field 03="Asdfr Pagsdre")) OR ((field1="hjk g drf") AND (field
03="Asdfr Pagsdre")) OR ((field1="ldkgi jflghjdffe") AND (field 03="Asdfr
Pagsdre")))' you entered is invalid' you entered is invalid.

There's a couple of strange things in the description, one being the
repeated "The expression"; but more strange is the truncation and
modification of the filter string. All the Table1. prefixes have been
removed and the field delimiters ('[' & ']') have also been removed.

Thanks for any help
Click to expand...


Peter,

Have you tried setting the filter to:

Me.Filter = "(Table1.field1= 'Ofq') AND (Table1.field2= 'Asdfr Pagsdre')"

***
NOTE: the doubled double quotes ("") were replaced with a single quote (')
***

If that works, try (watch for line wrap):

Me.Filter = "(((Table1.field1= 'Ofq') AND (Table1.field2= 'Asdfr Pagsdre')) " _
& "OR ((Table1.field1= '123456') AND (Table1.field2= 'Asdfr Pagsdre')) " _
& "OR ((Table1.field1= '654321') AND (Table1.field2= 'Asdfr Pagsdre')) " _

& "OR ((Table1.field1= '615243') AND (Table1.[field three]= 'Asdfr
Pagsdre')) " _
& "OR ((Table1.field1= 'folr kler kruifd') AND (Table1.[field three]=
'Asdfr Pagsdre')) " _
& "OR ((Table1.field1= 'hjk g drf') AND (Table1.[field three]= 'Asdfr
Pagsdre')) " _
& "OR ((Table1.field1= 'ldkgi jflghjdffe') AND (Table1.[field three]=
'Asdfr Pagsdre')))"


Actually, you have one condition (field2) AND'ed to three OR'ed conditions for
[field1] which is then OR'ed to [field three] AND'ed to four OR'ed conditions
for [field1]

Without the values, it looks like this:

"([field2] AND ([field1] OR [field1] OR [field1]))
OR
([field three] AND ([field1] OR [field1] OR [field1] OR [field1]))"


So, you could rewrite it as (watch for line wrap):

Me.Filter = "((Table1.field2= 'Asdfr Pagsdre') AND " _
& " (Table1.field1= 'Ofq' OR Table1.field1= '123456' OR " _
& " Table1.field1= '654321'))" _
& " OR " _
& "((Table1.[field three]= 'Asdfr Pagsdre') AND " _
& " (Table1.field1= '615243' OR Table1.field1= 'folr kler kruifd' OR " _
& " Table1.field1= 'hjk g drf' OR Table1.field1= 'ldkgi jflghjdffe'))



Hope some of this helps...
Click to expand...
 
S

SteveS

Peter said:
Hi SteveS. Yep, that was one of the first things I tried. Access uses the
double quotes when it generates the filter using "Filter by form", so I just
left it that way.

-- Peter
Click to expand...

Peter,

What version of Access are you using?

I have A2K. I added a button to a test form and it worked with no problems.
Here is the code I used:

Private Sub btnFilterOn_Click()
Me.Filter = "(Query2.EmpName='Jane Doe' Or Query2.EmpName='Jo Blo') AND
(Query2.trackingno=1)"
Me.FilterOn = True
End Sub

Then, for grins, I tried:

Private Sub btnFilterOn_Click()
Me.Filter = "(Query2.EmpName=""Jane Doe"" Or Query2.EmpName=""Jo Blo"") AND
(Query2.trackingno=1)"
Me.FilterOn = True
End Sub

Again, no problems.


So I tried:

Private Sub btnFilterOn_Click()
Me.Filter = "(Query2.EmpName=""Jane Doe"" Or Query2.EmpName=""Jo Blo"")
AND " _
& " (Query2.trackingno=1)"

Me.FilterOn = True
End Sub

Still no problems.


Have you tried Compact and Repair? You might have some corruption.
 
G

Guest

Actually, mine works with smaller filters as well. When mine gets to be the
size of my original post the problem appears.

Try with more fields.

Compact and repair did not work. I'm working with a Microsoft Access
Project (adp file) with A2K 9.0.4119 SR-1.

-- Peter
 
S

SteveS

Peter said:
Actually, mine works with smaller filters as well. When mine gets to be the
size of my original post the problem appears.

Try with more fields.

Compact and repair did not work. I'm working with a Microsoft Access
Project (adp file) with A2K 9.0.4119 SR-1.
Click to expand...

Will the way I rewrote the filter work? It has 5 less comparisons but is
functionally the same as your original filter.
 
G

Guest

Hi. I managed to get some time to do some in-depth investigation...

I've found that Table1.[field three] is causing Access grief. If I change
it to just [field three] as in [field three]="Asdfr Pagsdre" it doesn't
complain.

I also optimized the query, as you pointed out in your first post.
For example:
Me.Filter = "((Table1.field2= 'Asdfr Pagsdre') AND " _
& " (Table1.field1= 'Ofq' OR Table1.field1= '123456' OR " _
& " Table1.field1= '654321'))"

....but, as a form filter that doesn't seem to want to work. If you view the
filter with "Filter by Form" it is displayed as:
Me.Filter = "(Table1.field2= 'Asdfr Pagsdre' AND " _
& " Table1.field1= 'Ofq') OR Table1.field1= '123456' OR " _
& " Table1.field1= '654321'))"

Giving me completely wrong results.

I'm working with inherited code; it's probably better to just scrap it an
start from scratch; but, I'd like to find out how to fix this in case I run
into it again.

If anybody on the Access dev. team is on this newgroup, what assumptions
have I gotten wrong?

-- Peter
 

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

Search any field 10
how query multiple fields into one field? 1
add variable table name to query results 4
crosstab query fails when based on query with subquery? 6
Concantenation HELP! 6
Relationships and Cascading Updates problem 2
Running Sum Query of Ranked Data 6
Updatable query no longer updatable, and slow IN clause 1

Top