access97 vs access2000 advance filter

[swilson513]

In Access97 I was able to have an advance filter on a form that had a
Like statement so when you applied the filter EACH time it would asked
for the criteria. In 2000 the same filter doesn't asked for the
criteria after the first time you apply it. Is there a beter way of
doing this?

TIA

[Allen Browne]

Try an unbound text box on your form.

If it's called Text1 on Form1, you could then use:
Like [Forms].[Form1].[Text1] & "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sw********@yahoo.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

In Access97 I was able to have an advance filter on a form that had a
Like statement so when you applied the filter EACH time it would asked
for the criteria. In 2000 the same filter doesn't asked for the
criteria after the first time you apply it. Is there a beter way of
doing this?

TIA

[jeff]

I'm not sure I understand, would this be the statment in the advance filter?
What I have is a form that's based on a query that is based on a table. I do
a lot of copy and pasting because the records are similer. I had the advance
filter set up so I could enter a 3 didget code that all of my orders start
with and that would bring up all the records that started with them didgets
then I can choose the one that best matches what I'm ordering and copy and
paste it to a new record. The field that I'm copy and pasting is a lookup
from another table. This has worked very well until they upgraded us to
Access 2000.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41**********************@per-qv1-newsreader-01.iinet.net.au...

Try an unbound text box on your form.

If it's called Text1 on Form1, you could then use:
Like [Forms].[Form1].[Text1] & "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sw********@yahoo.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

In Access97 I was able to have an advance filter on a form that had a
Like statement so when you applied the filter EACH time it would asked
for the criteria. In 2000 the same filter doesn't asked for the
criteria after the first time you apply it. Is there a beter way of
doing this?

TIA

[Allen Browne]

Yes. The suggested statement goes into the Critieria line in your advanced
filter, under the field that contains the digits.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jeff" <no**********@spm.com> wrote in message
news:Ip%zd.270011$V41.66521@attbi_s52...

I'm not sure I understand, would this be the statment in the advance
filter? What I have is a form that's based on a query that is based on a
table. I do a lot of copy and pasting because the records are similer. I
had the advance filter set up so I could enter a 3 didget code that all of
my orders start with and that would bring up all the records that started
with them didgets then I can choose the one that best matches what I'm
ordering and copy and paste it to a new record. The field that I'm copy
and pasting is a lookup from another table. This has worked very well
until they upgraded us to Access 2000.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41**********************@per-qv1-newsreader-01.iinet.net.au...

Try an unbound text box on your form.

If it's called Text1 on Form1, you could then use:
Like [Forms].[Form1].[Text1] & "*"
<sw********@yahoo.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

In Access97 I was able to have an advance filter on a form that had a
Like statement so when you applied the filter EACH time it would asked
for the criteria. In 2000 the same filter doesn't asked for the
criteria after the first time you apply it. Is there a beter way of
doing this?

[swilson513]

I'm having no luck with this at all. I did find some stuff on MS sight
about filters not completley clearing and there was a work around but
then that would completley clear the filter so it would have to be have
the statement entered again.

In both 97 & 2000 this is the statement that is in the filters box on
the Form Property box
"((([Purchase order entry].Control_Number) Like [Enter Control Number]
& "*"))"

In 97 it works as expected asking for the control number each time the
filter is toggled, in 2000 when you toggle the filter it will take on
whatever the last number you entered was.
Any suggestions on this??

Thanks Again

[Allen Browne]

Look back to the previous answer.

It explained how to use a text box on a form, instead of a parameter box in
the query. Your example is still not using a text box.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps.org.

<sw********@aol.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

I'm having no luck with this at all. I did find some stuff on MS sight
about filters not completley clearing and there was a work around but
then that would completley clear the filter so it would have to be have
the statement entered again.

In both 97 & 2000 this is the statement that is in the filters box on
the Form Property box
"((([Purchase order entry].Control_Number) Like [Enter Control Number]
& "*"))"

In 97 it works as expected asking for the control number each time the
filter is toggled, in 2000 when you toggle the filter it will take on
whatever the last number you entered was.
Any suggestions on this??

Thanks Again

[swilson513]

I created the text box with the control source being the field that I
want to sort. Right or Wrong?

Like [Forms].[purchace order entry].[filter] & "*"

then put that statement in the advance queri, it acted the same as
before, worked the first time but then would retain the same
information.

Could it be that the data base is not converted to 2000?

Before this statment would prompt the user for the control number,
"like [Enter Control Number]&"*"".
I'm not sure if I'm not understanding or if I'm not explaining right.

[Allen Browne]

Without testing, I would expect the advanced filter to re-read the value
whenever the filter is re-evaluated. You could probably force this by
setting the FilterOn to True.

What I personally prefer to do is to use code to create the filter string,
and assign it to the Filter of the form in either the Click of a Filter
button, or the AfterUpdate of the text box (if there is only one text box
used to create the filter string). This forces it to be re-evaluated
immediately.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps.org.

<sw********@yahoo.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

I created the text box with the control source being the field that I
want to sort. Right or Wrong?

Like [Forms].[purchace order entry].[filter] & "*"

then put that statement in the advance queri, it acted the same as
before, worked the first time but then would retain the same
information.

Could it be that the data base is not converted to 2000?

Before this statment would prompt the user for the control number,
"like [Enter Control Number]&"*"".
I'm not sure if I'm not understanding or if I'm not explaining right.

[swilson513]

Thanks for all your suggestions, I ended up just using a couple macros
to run a queri, then I get the expected results. Thanks Again!