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

custom filters at runtime

P: n/a
hello, i am having an issue with a filter. What I want to do is filter
out all of the dates that have past and are not within a certain time
period. This time period is seleced from a combo box. Here is the
code I am attempting to use.

Dim D1 As Date
Dim D2 As Date
Dim T As Integer
Dim stfilter As String
D1 = Date

stfilter = ""
If cboTime = 1 Then
T = 180
ElseIf cboTime = 2 Then
T = 365
ElseIf cboTime = 3 Then
T = 730
ElseIf cboTime = 4 Then
T = 1825
ElseIf cboTime = 5 Then
T = 3650
ElseIf cboTime = 6 Then
T = 9125
ElseIf cboTime = 7 Then
T = 18250
End If

D2 = T + D1
stfilter = "Relief_Valve_Data.InspectionDue BETWEEN " & D1 & " And
" & D2
Me.Filter = stfilter
Me.FilterOn = True

When I run the filter, it filters out every single record instead of
only the ones I want. I dont know why it is not working because the
custom filter that appears in the properties window after I run it is
as follows:
Relief_Valve_Data.InspectionDue BETWEEN 1/8/2007 And 1/7/2012

any help would be greatly appreciated
thanks

Jan 8 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Since you're actually feeding an SQL WHERE clause without the WHERE to
set a filter, you need to surround your dates with # signs so it knows
the data type of your parameters. You want your stFilter to return
this:
Relief_Valve_Data.InspectionDue BETWEEN #1/8/2007# And #1/7/2012#

Try changing it to:
stfilter = "Relief_Valve_Data.InspectionDue BETWEEN #" & D1 & "# And #"
& D2 & "#"

FYI, if your parameters were strings instead of dates, you would need
to surround your parameters with ' signs, so your output would be like
this:
Relief_Valve_Data.LastName = 'Smith'

The only time you can exclude the # or the ' is if your parameters are
numeric.

HTH,
Jana

gi*********@gmail.com wrote:
hello, i am having an issue with a filter. What I want to do is filter
out all of the dates that have past and are not within a certain time
period. This time period is seleced from a combo box. Here is the
code I am attempting to use.

Dim D1 As Date
Dim D2 As Date
Dim T As Integer
Dim stfilter As String
D1 = Date

stfilter = ""
If cboTime = 1 Then
T = 180
ElseIf cboTime = 2 Then
T = 365
ElseIf cboTime = 3 Then
T = 730
ElseIf cboTime = 4 Then
T = 1825
ElseIf cboTime = 5 Then
T = 3650
ElseIf cboTime = 6 Then
T = 9125
ElseIf cboTime = 7 Then
T = 18250
End If

D2 = T + D1
stfilter = "Relief_Valve_Data.InspectionDue BETWEEN " & D1 & " And
" & D2
Me.Filter = stfilter
Me.FilterOn = True

When I run the filter, it filters out every single record instead of
only the ones I want. I dont know why it is not working because the
custom filter that appears in the properties window after I run it is
as follows:
Relief_Valve_Data.InspectionDue BETWEEN 1/8/2007 And 1/7/2012

any help would be greatly appreciated
thanks
Jan 8 '07 #2

P: n/a
gi*********@gmail.com wrote:
hello, i am having an issue with a filter. What I want to do is filter
out all of the dates that have past and are not within a certain time
period. This time period is seleced from a combo box. Here is the
code I am attempting to use.

Dim D1 As Date
Dim D2 As Date
Dim T As Integer
Dim stfilter As String
D1 = Date

stfilter = ""
If cboTime = 1 Then
T = 180
ElseIf cboTime = 2 Then
T = 365
ElseIf cboTime = 3 Then
T = 730
ElseIf cboTime = 4 Then
T = 1825
ElseIf cboTime = 5 Then
T = 3650
ElseIf cboTime = 6 Then
T = 9125
ElseIf cboTime = 7 Then
T = 18250
End If

D2 = T + D1
stfilter = "Relief_Valve_Data.InspectionDue BETWEEN " & D1 & " And
" & D2
Me.Filter = stfilter
Me.FilterOn = True

When I run the filter, it filters out every single record instead of
only the ones I want. I dont know why it is not working because the
custom filter that appears in the properties window after I run it is
as follows:
Relief_Valve_Data.InspectionDue BETWEEN 1/8/2007 And 1/7/2012

any help would be greatly appreciated
thanks
Try

stfilter = "Relief_Valve_Data.InspectionDue BETWEEN #" & D1 & "# And #"
& D2 & "#"
--
Smartin
Jan 9 '07 #3

P: n/a
thanks, i did not know about the # sign surrounding the dates. I did
that and it worked perfectly.

Jan 9 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.