docmd.applyfilter not working

  • Thread starter Thread starter Haggis
  • Start date Start date
H

Haggis

Hello all,

relative novice to Access coding ...bear with me :)

this works DoCmd.ApplyFilter , "" & Combo185.Value & " = " &
Combo186.Value & ""

this works DoCmd.ApplyFilter , "[val_pass] = 1"

this does not ...

DoCmd.ApplyFilter , "" & Combo185.Value & " = " & Combo186.Value & "" And
"[val_pass] = 1"

I am trying to 2 to 3 filters at once on a form.

thanks for any thoughts!!
 
Hi David,

A good programming practice is to rename controls from the generic names
that MS provides to a more descriptive name. Which tells you more: Combo185
and Combo186.Value or cboDepartment and cboJobType??

Value is the default property for (most) controls; it is not necessary to
type it.
this works DoCmd.ApplyFilter , "" & Combo185.Value & " = " &
Combo186.Value & ""
Click to expand...

You have empty string concantated with the value of Combo185, concantated
with the string " = ", concantated with the value of Combo186.

If Combo185 = 5 and Combo186 = 5 then your filter would read

DoCmd.ApplyFilter , 5 = 5 (ie TRUE)

this works DoCmd.ApplyFilter , "[val_pass] = 1"
Click to expand...

This is normal. You have a field name compared to a value.

DoCmd.ApplyFilter , "" & Combo185.Value & " = " & Combo186.Value & "" And
"[val_pass] = 1"
Click to expand...

This one is an empty string concantated with the value of Combo185,
concantated with the string " = ", concantated with the value of Combo186,
concantated with the string [val_pass] = 1. Here the big error is the word
AND is not within quotes.


You *could* try:

DoCmd.ApplyFilter , Combo185 & " = " & Combo186 & " And [val_pass] = 1"


It all depends on what Combo185 and Combo186 are.


A typical filter might be:

DoCmd.ApplyFilter , "[SomeFieldName] " = " & Combo186 & " And [val_pass] =
1"

where the field type of [SomeFieldName] is a number. If the field type of
[SomeFieldName] is text the filter might look like:

DoCmd.ApplyFilter , "[SomeFieldName] '" = " & Combo186 & "' And [val_pass]
= 1"

Expanded, it looks like

"[SomeFieldName] ' " = " & Combo186 & " ' And [val_pass] = 1"


If Combo186 data type is Date/Time, then the delimiters are the hash mark (#):

DoCmd.ApplyFilter , "[SomeFieldName] #" = " & Combo186 & "# And [val_pass]
= 1"


If you post the names of the fields you want to filter on and the field data
types, it would be easier to help you with the filter string.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Haggis said:
Hello all,

relative novice to Access coding ...bear with me :)

this works DoCmd.ApplyFilter , "" & Combo185.Value & " = " &
Combo186.Value & ""

this works DoCmd.ApplyFilter , "[val_pass] = 1"

this does not ...

DoCmd.ApplyFilter , "" & Combo185.Value & " = " & Combo186.Value & "" And
"[val_pass] = 1"

I am trying to 2 to 3 filters at once on a form.

thanks for any thoughts!!
Click to expand...
 
Hi Steve ,
thanks for your help ..I'm going to try your suggestions

combo185 is text name of a field in table (user select)

combo186 is text from discrete list of field in table (user select from
combo185 above)

val_pass is just as you said checking field value in table.

syntax is always a headache for me!

Cheers
David
Steve Sanford said:
Hi David,

A good programming practice is to rename controls from the generic names
that MS provides to a more descriptive name. Which tells you more:
Combo185
and Combo186.Value or cboDepartment and cboJobType??

Value is the default property for (most) controls; it is not necessary to
type it.
this works DoCmd.ApplyFilter , "" & Combo185.Value & " = " &
Combo186.Value & ""
Click to expand...

You have empty string concantated with the value of Combo185, concantated
with the string " = ", concantated with the value of Combo186.

If Combo185 = 5 and Combo186 = 5 then your filter would read

DoCmd.ApplyFilter , 5 = 5 (ie TRUE)

this works DoCmd.ApplyFilter , "[val_pass] = 1"
Click to expand...

This is normal. You have a field name compared to a value.

DoCmd.ApplyFilter , "" & Combo185.Value & " = " & Combo186.Value & ""
And
"[val_pass] = 1"
Click to expand...

This one is an empty string concantated with the value of Combo185,
concantated with the string " = ", concantated with the value of
Combo186,
concantated with the string [val_pass] = 1. Here the big error is the
word
AND is not within quotes.


You *could* try:

DoCmd.ApplyFilter , Combo185 & " = " & Combo186 & " And [val_pass] = 1"


It all depends on what Combo185 and Combo186 are.


A typical filter might be:

DoCmd.ApplyFilter , "[SomeFieldName] " = " & Combo186 & " And [val_pass]
=
1"

where the field type of [SomeFieldName] is a number. If the field type
of
[SomeFieldName] is text the filter might look like:

DoCmd.ApplyFilter , "[SomeFieldName] '" = " & Combo186 & "' And
[val_pass]
= 1"

Expanded, it looks like

"[SomeFieldName] ' " = " & Combo186 & " ' And [val_pass] = 1"


If Combo186 data type is Date/Time, then the delimiters are the hash mark
(#):

DoCmd.ApplyFilter , "[SomeFieldName] #" = " & Combo186 & "# And
[val_pass]
= 1"


If you post the names of the fields you want to filter on and the field
data
types, it would be easier to help you with the filter string.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Haggis said:
Hello all,

relative novice to Access coding ...bear with me :)

this works DoCmd.ApplyFilter , "" & Combo185.Value & " = " &
Combo186.Value & ""

this works DoCmd.ApplyFilter , "[val_pass] = 1"

this does not ...

DoCmd.ApplyFilter , "" & Combo185.Value & " = " & Combo186.Value & ""
And
"[val_pass] = 1"

I am trying to 2 to 3 filters at once on a form.

thanks for any thoughts!!
Click to expand...
Click to expand...
 
Hi there,

tried this

DoCmd.ApplyFilter , Combo185.Value & " = " & Combo186.Value & " And
[val_pass] = 1"

but get the popup asking for value for combo186.value , when i type it in
...it gives error 'no records'
I do know that a select statement with static values returns records ... I
want this to be user selectable so I am trying to use a variable as a field
name

or this
DoCmd.ApplyFilter , Combo185.Value & " = " & Combo186.Value & "' And
val_pass = 1"

this give error

" syntax error in string in query expression 'company = RIO' And val_pass
= 1' "

combo185 = "Company" - field in table (text)
combo186 = "RIO" - data in field 'company' (text)

ps. yes , i agree , i usually name my objects better ...this is just for
testing at the moment


Thanks!

Steve Sanford said:
Hi David,

A good programming practice is to rename controls from the generic names
that MS provides to a more descriptive name. Which tells you more:
Combo185
and Combo186.Value or cboDepartment and cboJobType??

Value is the default property for (most) controls; it is not necessary to
type it.
this works DoCmd.ApplyFilter , "" & Combo185.Value & " = " &
Combo186.Value & ""
Click to expand...

You have empty string concantated with the value of Combo185, concantated
with the string " = ", concantated with the value of Combo186.

If Combo185 = 5 and Combo186 = 5 then your filter would read

DoCmd.ApplyFilter , 5 = 5 (ie TRUE)

this works DoCmd.ApplyFilter , "[val_pass] = 1"
Click to expand...

This is normal. You have a field name compared to a value.

DoCmd.ApplyFilter , "" & Combo185.Value & " = " & Combo186.Value & ""
And
"[val_pass] = 1"
Click to expand...

This one is an empty string concantated with the value of Combo185,
concantated with the string " = ", concantated with the value of
Combo186,
concantated with the string [val_pass] = 1. Here the big error is the
word
AND is not within quotes.


You *could* try:

DoCmd.ApplyFilter , Combo185 & " = " & Combo186 & " And [val_pass] = 1"


It all depends on what Combo185 and Combo186 are.


A typical filter might be:

DoCmd.ApplyFilter , "[SomeFieldName] " = " & Combo186 & " And [val_pass]
=
1"

where the field type of [SomeFieldName] is a number. If the field type
of
[SomeFieldName] is text the filter might look like:

DoCmd.ApplyFilter , "[SomeFieldName] '" = " & Combo186 & "' And
[val_pass]
= 1"

Expanded, it looks like

"[SomeFieldName] ' " = " & Combo186 & " ' And [val_pass] = 1"


If Combo186 data type is Date/Time, then the delimiters are the hash mark
(#):

DoCmd.ApplyFilter , "[SomeFieldName] #" = " & Combo186 & "# And
[val_pass]
= 1"


If you post the names of the fields you want to filter on and the field
data
types, it would be easier to help you with the filter string.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Haggis said:
Hello all,

relative novice to Access coding ...bear with me :)

this works DoCmd.ApplyFilter , "" & Combo185.Value & " = " &
Combo186.Value & ""

this works DoCmd.ApplyFilter , "[val_pass] = 1"

this does not ...

DoCmd.ApplyFilter , "" & Combo185.Value & " = " & Combo186.Value & ""
And
"[val_pass] = 1"

I am trying to 2 to 3 filters at once on a form.

thanks for any thoughts!!
Click to expand...
Click to expand...
 
David,

Try this:

DoCmd.ApplyFilter , "[Company] = 'RIO'" & " And [val_pass] = 1"

(Expanded it is , " [Company] = ' RIO ' " )


If that works, then try:

DoCmd.ApplyFilter , "[Company] = '" & Me.Combo186 & "' And [val_pass] = 1"

(expanded -> ] = ' " & Me.Combo186 & " ' And )


If that works, then try:


DoCmd.ApplyFilter , "[" & Me.Combo185 & "] = '" & Me.Combo186 & "' And
[val_pass] = 1"


This should also work:

' I put all of the declarations at the beginning of the sub
Dim strFilter as String

' where ever in the code
strFilter = "[" & Me.Combo185 & "] = '" & Me.Combo186 & "' And [val_pass] =
1"
'--------------------------
' for debugging - delete or comment out the msgbox when filter is working
correctly
msgbox strFilter
'--------------------------

DoCmd.ApplyFilter , strFilter


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Haggis said:
Hi there,

tried this

DoCmd.ApplyFilter , Combo185.Value & " = " & Combo186.Value & " And
[val_pass] = 1"
Click to expand...
 
Thanks Steve!

I'll let you know how it goes :)

Steve Sanford said:
David,

Try this:

DoCmd.ApplyFilter , "[Company] = 'RIO'" & " And [val_pass] = 1"

(Expanded it is , " [Company] = ' RIO ' " )


If that works, then try:

DoCmd.ApplyFilter , "[Company] = '" & Me.Combo186 & "' And [val_pass] = 1"

(expanded -> ] = ' " & Me.Combo186 & " ' And )


If that works, then try:


DoCmd.ApplyFilter , "[" & Me.Combo185 & "] = '" & Me.Combo186 & "' And
[val_pass] = 1"


This should also work:

' I put all of the declarations at the beginning of the sub
Dim strFilter as String

' where ever in the code
strFilter = "[" & Me.Combo185 & "] = '" & Me.Combo186 & "' And [val_pass]
=
1"
'--------------------------
' for debugging - delete or comment out the msgbox when filter is working
correctly
msgbox strFilter
'--------------------------

DoCmd.ApplyFilter , strFilter


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Haggis said:
Hi there,

tried this

DoCmd.ApplyFilter , Combo185.Value & " = " & Combo186.Value & " And
[val_pass] = 1"
Click to expand...
but get the popup asking for value for combo186.value , when i type it in
...it gives error 'no records'
I do know that a select statement with static values returns records ...
I
want this to be user selectable so I am trying to use a variable as a
field
name

or this
DoCmd.ApplyFilter , Combo185.Value & " = " & Combo186.Value & "' And
val_pass = 1"

this give error

" syntax error in string in query expression 'company = RIO' And
val_pass
= 1' "

combo185 = "Company" - field in table (text)
combo186 = "RIO" - data in field 'company' (text)

ps. yes , i agree , i usually name my objects better ...this is just for
testing at the moment


Thanks!
Click to expand...
Click to expand...
 
I hate syntax problems!!!

Steve ...this is what finally worked

DoCmd.ApplyFilter , "[" & Combo185 & "] = ('" & Combo186 & "') and
[val_pass] = '1'"

Thanks for your help!

Haggis said:
Thanks Steve!

I'll let you know how it goes :)

Steve Sanford said:
David,

Try this:

DoCmd.ApplyFilter , "[Company] = 'RIO'" & " And [val_pass] = 1"

(Expanded it is , " [Company] = ' RIO ' " )


If that works, then try:

DoCmd.ApplyFilter , "[Company] = '" & Me.Combo186 & "' And [val_pass] =
1"

(expanded -> ] = ' " & Me.Combo186 & " ' And )


If that works, then try:


DoCmd.ApplyFilter , "[" & Me.Combo185 & "] = '" & Me.Combo186 & "' And
[val_pass] = 1"


This should also work:

' I put all of the declarations at the beginning of the sub
Dim strFilter as String

' where ever in the code
strFilter = "[" & Me.Combo185 & "] = '" & Me.Combo186 & "' And
[val_pass] =
1"
'--------------------------
' for debugging - delete or comment out the msgbox when filter is working
correctly
msgbox strFilter
'--------------------------

DoCmd.ApplyFilter , strFilter


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Haggis said:
Hi there,

tried this

DoCmd.ApplyFilter , Combo185.Value & " = " & Combo186.Value & " And
[val_pass] = 1"
Click to expand...
but get the popup asking for value for combo186.value , when i type it
in
...it gives error 'no records'
I do know that a select statement with static values returns records ...
I
want this to be user selectable so I am trying to use a variable as a
field
name

or this
DoCmd.ApplyFilter , Combo185.Value & " = " & Combo186.Value & "' And
val_pass = 1"

this give error

" syntax error in string in query expression 'company = RIO' And
val_pass
= 1' "

combo185 = "Company" - field in table (text)
combo186 = "RIO" - data in field 'company' (text)

ps. yes , i agree , i usually name my objects better ...this is just for
testing at the moment


Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top