Query filter by code

[banem2]

I cannot find similar answer...

I am interested for correct syntax to filter query by VBA function.

Query filter: Between [Table1].[ID1]+1 And [Table1].[ID1]+4

When I use function this way:

Query filter: fGetFilter([Table1].[ID1])

Function fGetFilter (intID As Integer)
fGetFilter = "Between " & intID+1 & " And " & intID+4
End Function

Or
Function fGetFilter (intID As Integer)
fGetFilter = "Between [t_properties].[group]+ " & 1 & _
" And [t_properties].[group] +" & 4
End Function

This does not work. How to properly use Between in function to filter
query?

--

This approach needs to be used as ID's wraps. I have 2 fields with
numbers 1-9. Second field is filtered by first field value. If first
field has value of 7, second field filter needs to show data where
second field = 8 or 9 or 1 or 2. It goes to first field +4, but wrap
at 9 back to 1.

So my idea is to write function which will return proper filter, ie.

Function fGetFilter (intID as Integer)
Select Case intID
Case 8
fGetFilter = "Like 9 or Like 1 or Like 2 or Like 3"
End Select
End Function

I know it cannot work this way, but this is just to give an idea. Any
simple solution?

 

[Wolfgang Kais]

Hello <[email protected]>.

I cannot find similar answer...

I am interested for correct syntax to filter query by VBA function.

Query filter: Between [Table1].[ID1]+1 And [Table1].[ID1]+4

When I use function this way:

Query filter: fGetFilter([Table1].[ID1])

Function fGetFilter (intID As Integer)
fGetFilter = "Between " & intID+1 & " And " & intID+4
End Function

Or
Function fGetFilter (intID As Integer)
fGetFilter = "Between [t_properties].[group]+ " & 1 & _
" And [t_properties].[group] +" & 4
End Function

This does not work. How to properly use Between in function to
filter query?

Both functions return a filter string. Unfortunately xou cannot
dynamically retrieve the where clause in the query by a function.
(The query can't retrieve a part of it's sql string dynamically)

This approach needs to be used as ID's wraps. I have 2 fields
with numbers 1-9. Second field is filtered by first field value.
If first field has value of 7, second field filter needs to show
data where second field = 8 or 9 or 1 or 2. It goes to first field
+4, but wrap at 9 back to 1.

So my idea is to write function which will return proper filter, ie.

Function fGetFilter (intID as Integer)
Select Case intID
Case 8
fGetFilter = "Like 9 or Like 1 or Like 2 or Like 3"
End Select
End Function

I know it cannot work this way, but this is just to give an idea.
Any simple solution?

Function fGetFilter(intID As Integer) As String
Dim i as Integer, strList As String
For i = 0 To 3
strList = strList & "," & ((intID + i) Mod 9) + 1
Next
fGetFilter = "IN (" & Mid(strList, 2) & ")"
End Function

But as stated above: I think that you will not be able to use that
function properly.
Suppose you want to filter ID2 depending on ID1, you probably should
use a calculated field "(ID2+9-ID1) Mod 9" and use "Between 1 and 4"
as criterion.

 

[banem2]

Suppose you want to filter ID2 depending on ID1, you probably should

use a calculated field "(ID2+9-ID1) Mod 9" and use "Between 1 and 4"
as criterion.

Indeed - there is simple solution. Thanks!

GroupFilter: ([t_properties].[group]+9-[t_properties2].[group]) Mod 9
Between 1 And 4