Using filter error because of DSUM function

[wassimdaccache]

dears

I have a continuous forms. It contains 2 fields [customername][customerid]
I made another field to check their accounts. it is [account].

the source of [account] is based on a DSUM function. "it is working well"

Now I want to filter this form on a criteria where account > 0.

It is not working using this code

Filter = "Balance > " & 0
Me.FilterOn = True
 

I was searching this topics i found that it is impossible to do it, because the [account] field is based on a DSUM function. Is that true ?

Do you think is there any better method for checking my customers account ?




Thank you in advance

Regards,


WASSIM S DACCACHE

[Stewart Ross]

Hi Wassim. DSUM is not the problem at all. Line 1 in your filter code is incorrect; it should be referring to the form's filter property, but by leaving out the shortcut form reference (the Me keyword) you are actually referring to an undeclared variable called Filter - and when you then set the form filter on there is no filter to apply!

It ought to be

Me.Filter = "Balance > 0"
Me.FilterOn = True
 

By the way, there is no need to separate out the 0 when putting together the filter string.

You can, and should, set the VBA compiler options to require variable declarations - it will help trap this type of 'undeclared variable by mistake' problem. From the VB Editor choose Tools, Options, and ensure Require Variable Declaration is checked. See also the use of compiler directive Option Explicit in the header of a module, as this also forces explicit declaration of all variables within that module.

At least then if you did not intend to use a variable (as in this instance when you were trying to set a form's filter property) the compiler error will flag that you have done something wrong...

-Stewart

[wassimdaccache]

Hi Wassim. DSUM is not the problem at all. Line 1 in your filter code is incorrect; it should be referring to the form's filter property, but by leaving out the shortcut form reference (the Me keyword) you are actually referring to an undeclared variable called Filter - and when you then set the form filter on there is no filter to apply!

It ought to be

Me.Filter = "Balance > 0"
Me.FilterOn = True
 

By the way, there is no need to separate out the 0 when putting together the filter string.

You can, and should, set the VBA compiler options to require variable declarations - it will help trap this type of 'undeclared variable by mistake' problem. From the VB Editor choose Tools, Options, and ensure Require Variable Declaration is checked. See also the use of compiler directive Option Explicit in the header of a module, as this also forces explicit declaration of all variables within that module.

At least then if you did not intend to use a variable (as in this instance when you were trying to set a form's filter property) the compiler error will flag that you have done something wrong...

-Stewart

Dear sir

I Appreciate your kind reply.

I did it, I got a new input box with a "balance" title.

 
 
    Me.Filter = "balance > 0"
    Me.FilterOn = True
 

any idea?
I checked that my field [account] name is "balance"

[Stewart Ross]

Wassim, it is the fields of the underlying query or table that you are filtering, not controls on your form. If you want to filter on a field called Balance you must have a field called Balance in your form's recordsource query or table for this to work.

If you are trying to filter on the value of a control shown on your form, a control that is not named the same as a bound field, the simple answer is that you cannot.

If possible place calculated fields in the recordsource query of your form. You will then be able to refer to the calculated field just as with any other field

-Stewart

[wassimdaccache]

Wassim, it is the fields of the underlying query or table that you are filtering, not controls on your form. If you want to filter on a field called Balance you must have a field called Balance in your form's recordsource query or table for this to work.

If you are trying to filter on the value of a control shown on your form, a control that is not named the same as a bound field, the simple answer is that you cannot.

If possible place calculated fields in the recordsource query of your form. You will then be able to refer to the calculated field just as with any other field

-Stewart

aaa OK OK now I understood. I am not able to filter [balance] because it is not on the recordsource of my form.

Actually I did as you advice me. I put the source of balance textbox in the recordsource of my form , I use this code filter above and it is working well.


THANK YOU AGAIN

Have a nice day :)