[Forms]![clients]![filter]?

[JethroUK©]

i need to pass a form filter (Clients.filter), to a query as criteria

i tried:

[Forms]![clients]![filter]

[Forms]![clients.filter]

[Forms]![clients!filter]

all without success - any ideas?

 

[Douglas J Steele]

See whether [Forms]![clients].filter works.

 

[John Spencer]

I dont't think you can pass the filter string of a form directly to query as
criteria. After all what would it make of criteria that read FieldA = 5.
That would become something like

SELECT ...
FROM ...
WHERE SomeField = "FieldA=5"

Why do you need to do this? To generate a report? or for some other
reason?

 

[JethroUK©]

filters on their own works fine when pasted in as criteria:

what's wrong with '[FieldA] = 5' - it's explicit, so it should work
irrespective of which field you put it in

or in my case a typical filter would be:

[Enquired] Is Not Null AND [Letter Sent] Is Null

query produces the correct results wherever i put it

I am creating a database for our receptionist - it has a dozen different
filters for things she's likely to need/view/print - it creates some simple
reports based on the form filter & I want to create a std addressed
mailmerge letter, she can edit and merge with who ever's in the filter - i
figured the best way is to base the merged letter on a query, which in turn
should be based on whatever filter she chose - seemed straight forward until
i tried it

i'm amazed i can't pass any properties to a query e.g. textbox.tag

i might have to consider passing the form filter direct to the merged letter
but i figured that would be even harder

I dont't think you can pass the filter string of a form directly to query as
criteria. After all what would it make of criteria that read FieldA = 5.
That would become something like

SELECT ...
FROM ...
WHERE SomeField = "FieldA=5"

Why do you need to do this? To generate a report? or for some other
reason?

i need to pass a form filter (Clients.filter), to a query as criteria

i tried:

[Forms]![clients]![filter]

[Forms]![clients.filter]

[Forms]![clients!filter]

all without success - any ideas?

 

[JethroUK©]

tried that - the query ammends the syntax to

[Forms]![clients].[filter]

i assumed i could pass a property to a query - but maybe not

thanks anyhow

i might have to put it in a hidden textbox but it just seems like a bodge

See whether [Forms]![clients].filter works.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

i need to pass a form filter (Clients.filter), to a query as criteria

i tried:

[Forms]![clients]![filter]

[Forms]![clients.filter]

[Forms]![clients!filter]

all without success - any ideas?

 

[John Spencer]

So what field would you have that would be equal to the string

"[Enquired] Is Not Null AND [Letter Sent] Is Null"

That is what you are attempting to pass into the criteria. In SQL that
would read something like

SELECT ...
FROM ...
WHERE Enquired = "[Enquired] Is Not Null AND [Letter Sent] Is Null"

When what you seem to want is

SELECT ...
FROM ...
WHERE Enquired] Is Not Null AND [Letter Sent] Is Null

You can do this using VBA to build the query (or just build the where
clause) or by opening a report and passing it the filter string. As usual
you need to have the Fields named the same and available in both the form
and the record source of the report

DoCmd.OpenReport "ReportName",acViewPreview,,Forms!SomeFormName.Filter

filters on their own works fine when pasted in as criteria:

what's wrong with '[FieldA] = 5' - it's explicit, so it should work
irrespective of which field you put it in

or in my case a typical filter would be:

[Enquired] Is Not Null AND [Letter Sent] Is Null

query produces the correct results wherever i put it

I am creating a database for our receptionist - it has a dozen different
filters for things she's likely to need/view/print - it creates some
simple
reports based on the form filter & I want to create a std addressed
mailmerge letter, she can edit and merge with who ever's in the filter - i
figured the best way is to base the merged letter on a query, which in
turn
should be based on whatever filter she chose - seemed straight forward
until
i tried it

i'm amazed i can't pass any properties to a query e.g. textbox.tag

i might have to consider passing the form filter direct to the merged
letter
but i figured that would be even harder

I dont't think you can pass the filter string of a form directly to query as
criteria. After all what would it make of criteria that read FieldA = 5.
That would become something like

SELECT ...
FROM ...
WHERE SomeField = "FieldA=5"

Why do you need to do this? To generate a report? or for some other
reason?

i need to pass a form filter (Clients.filter), to a query as criteria

i tried:

[Forms]![clients]![filter]

[Forms]![clients.filter]

[Forms]![clients!filter]

all without success - any ideas?

 

[JethroUK©]

Trust me - The criteria in a form filter works fine as query criteria - (it
does not matter which field you put it in - because it's explicit)

if you type ' [apples] = 5 ' (NO QUOTES) as the criteria for the 'banana'
field - the query will still find records where apples = 5

you can query every field in the design from any one criteria cell provided
you explicitly name the fields e.g.

[Enquired] Is Not Null AND [Letter Sent] Is Null

it will be extracted 'as is' and dumped directly into the WHERE part of the
SQL statement - just try it if you do not beleive me

Not sure why you want to convert it to text - because the query design
wont - just like a query doesn't intepret an object value of 5 as "5"

but that's not the problem - the topic of this discussion is getting a query
to read an object property (form.Filter)- seemed like a simple syntax
solution but it appears it can't be done - unless you have the solution,
i'll have to look for another way round

Re: the report - Like i said - i already have a report - i clicked a button
and one fell out ) - i need a mail merge with M.S. Word document dependant
on the current form filter criteria

So what field would you have that would be equal to the string

"[Enquired] Is Not Null AND [Letter Sent] Is Null"

That is what you are attempting to pass into the criteria. In SQL that
would read something like

SELECT ...
FROM ...
WHERE Enquired = "[Enquired] Is Not Null AND [Letter Sent] Is Null"

When what you seem to want is

SELECT ...
FROM ...
WHERE Enquired] Is Not Null AND [Letter Sent] Is Null

You can do this using VBA to build the query (or just build the where
clause) or by opening a report and passing it the filter string. As usual
you need to have the Fields named the same and available in both the form
and the record source of the report

DoCmd.OpenReport "ReportName",acViewPreview,,Forms!SomeFormName.Filter

filters on their own works fine when pasted in as criteria:

what's wrong with '[FieldA] = 5' - it's explicit, so it should work
irrespective of which field you put it in

or in my case a typical filter would be:

[Enquired] Is Not Null AND [Letter Sent] Is Null

query produces the correct results wherever i put it

I am creating a database for our receptionist - it has a dozen different
filters for things she's likely to need/view/print - it creates some
simple
reports based on the form filter & I want to create a std addressed
mailmerge letter, she can edit and merge with who ever's in the filter - i
figured the best way is to base the merged letter on a query, which in
turn
should be based on whatever filter she chose - seemed straight forward
until
i tried it

i'm amazed i can't pass any properties to a query e.g. textbox.tag

i might have to consider passing the form filter direct to the merged
letter
but i figured that would be even harder

I dont't think you can pass the filter string of a form directly to

query
as

criteria. After all what would it make of criteria that read FieldA = 5.
That would become something like

SELECT ...
FROM ...
WHERE SomeField = "FieldA=5"

Why do you need to do this? To generate a report? or for some other
reason?

i need to pass a form filter (Clients.filter), to a query as criteria

i tried:

[Forms]![clients]![filter]

[Forms]![clients.filter]

[Forms]![clients!filter]

all without success - any ideas?