UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Vb. Passing Multiple Variables To Form.filter    
 
   
RMonly
post Jan 8 2016, 11:49 AM
Post#1



Posts: 3
Joined: 8-January 16



Hi,
Private Sub Form_Open(Cancel As Integer)
'This is a register where I want to test for non-attendance in the past 3 consecutive weeks.
'The fields are week numbers as text numerals. e.g. 43.
'How do I get the Filter to accept the variables?
Dim a
Dim b
Dim c
Dim d
a = Form_REGISTER.Label7.Caption 'This returns the current week number.
b = a - 1
c = a - 2
d = a - 3
'Form.Filter = "([b] Is Null) And ([c] Is Null) And ([d] Is Null)" 'Will not accept the variables.

Form.Filter = "([42] Is Null) And ([41] Is Null) and ([40]is null)" 'Works fine.

Form.FilterOn = True

End Sub

Hopefully,
RMonly.
Go to the top of the page
 
GroverParkGeorge
post Jan 8 2016, 11:53 AM
Post#2


UA Admin
Posts: 31,199
Joined: 20-June 02
From: Newcastle, WA


Are [a], [b] and [c] fields in the table?
Go to the top of the page
 
fyed
post Jan 8 2016, 11:58 AM
Post#3



Posts: 33
Joined: 2-June 11



you must build the filter dynamically.

CODE
strFilter =  "([" & b & "] Is Null) And ([" & c & "] Is Null) and ([" & d & "]is null)"
Form.Filter = strFilter
Go to the top of the page
 
fyed
post Jan 8 2016, 11:59 AM
Post#4



Posts: 33
Joined: 2-June 11



although I must say that any time you have number as column headers, you have a data normalization problem.

You should have a column for WeekNum, instead of individual columns for each week.
Go to the top of the page
 
GroverParkGeorge
post Jan 8 2016, 12:10 PM
Post#5


UA Admin
Posts: 31,199
Joined: 20-June 02
From: Newcastle, WA


Thanks, Dale. I was going to comment on the apparent normalization problem at some point, too.

And glad to see you here.
Go to the top of the page
 
RMonly
post Jan 8 2016, 05:13 PM
Post#6



Posts: 3
Joined: 8-January 16



Hi GPG,
Yes they were fields. Fyed has provided the answer but Thank You for your interest. Happy New Year!
Go to the top of the page
 
RMonly
post Jan 8 2016, 05:16 PM
Post#7



Posts: 3
Joined: 8-January 16



Hi Fyed, Thank you very much! Works perfectly! Happy New Year!
Go to the top of the page
 
fyed
post Jan 8 2016, 06:29 PM
Post#8



Posts: 33
Joined: 2-June 11



glad I could help
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 09:39 AM