By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,604 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Using filter error because of DSUM function

100+
P: 222
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
Expand|Select|Wrap|Line Numbers
  1. Filter = "Balance > " & 0
  2. Me.FilterOn = True
  3.  

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
Oct 13 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "Balance > 0"
  2. Me.FilterOn = True
  3.  
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
Oct 13 '08 #2

100+
P: 222
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

Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "Balance > 0"
  2. Me.FilterOn = True
  3.  
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.     Me.Filter = "balance > 0"
  4.     Me.FilterOn = True
  5.  
any idea?
I checked that my field [account] name is "balance"
Oct 13 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Oct 13 '08 #4

100+
P: 222
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 :)
Oct 13 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.