Specific FILTER

[editolis]

Hi All,
I am using the following filter on my form to see some results from a sub form when I press a button.
======================================================================

If Me.chkGrand = -1 Then
varWhere = varWhere & "[GrandSyn] = " & str(Me.GrandSyn) & " AND "
End If
======================================================================

Most of the times the numbers I am looking for are not the same. So I need the closest numbers from my number (Up and Down) if exist.
For example if my number have a value 2,25 and my filter show me 0 results then I need (when I press a second button) to show me the closest numbers (if exist).
So lets say my column on the sub form show me the following numbers: 2,00 – 2,20 - 2,30 – 2,35 then my filterButton have to filter the 2,20 and the 2,30.
I Need a help with the code so the filter show me the closest numbers (Up and Down).
Thank you in advance.

 

[editolis]

I found this and is ok for now but is not exactly what i am looking for.

If Me.chkDiffA = -1 Then
varWhere = varWhere & "[DiffA] Between " & _
str(Me.DiffA - 0.25) & " AND " & str(Me.DiffA + 0.25) & " AND "
End If

Some times the numbers are bigger or smaller. I need the closest.

Thank you.

 

[wiklendt]

I found this and is ok for now but is not exactly what i am looking for.

If Me.chkDiffA = -1 Then
varWhere = varWhere & "[DiffA] Between " & _
str(Me.DiffA - 0.25) & " AND " & str(Me.DiffA + 0.25) & " AND "
End If

Some times the numbers are bigger or smaller. I need the closest.

Thank you.

although you have this working, i'd like to suggest a small change to make things easier for you/user:

you can put a textbox on your form to manually enter your difference number. i do a similar thing for an ordering database i created.

that is, instead of:

        str(Me.DiffA - 0.25) & " AND " & str(Me.DiffA + 0.25) & " AND "

use a textbox called txtMargin, say, and change the code to:

        str(Me.DiffA - [COLOR=Red]Me.txtMargin[/COLOR]) & " AND " & str(Me.DiffA + [COLOR=Red]Me.txtMargin[/COLOR]) & " AND "

then you can dynamically alter this. on your form open, you can set the default value for txtMargin to be 0.25, and the user to alter this if it required.

i'm not sure, however, to automatically find the closest value regardless of size (i.e., it may one day be -0.25, another day be +7.65)

HTH

 

[editolis]

        str(Me.DiffA - [COLOR=Red]Me.txtMargin[/COLOR]) & " AND " & str(Me.DiffA + [COLOR=Red]Me.txtMargin[/COLOR]) & " AND "

Thank you for your reply Wiklendt.
  
I am one step further now. Here is the SOLUTION i have but i am trying to figure out how i am going to do it and put them all together because this sql change completly the recordset from the subform. 

========================================================
so when i press a second button i do this:

strSQL = "SELECT TOP 1 qryISSN.* FROM qryISSN ORDER BY Abs(DiffA-" & str(Me.DiffA) & ");"
Me!frmISSNSub.Form.RecordSource = strSQL
Me.frmISSNSub.Requery

FANTASTIC. This is exctaly what i am looking for.
========================================================

But that give s me another problem. When i click the other chk's (TRUE-FALSE) i have on the Main FORM to filter my data they show me results from my major FILTER. Not with the TOP 1.

My major filter working with this code:
========================================================
Private Sub cmdFilterSpec_Click()

strSQL = "SELECT * FROM qryISSN " & BuildFilterSpec

Me!frmISSNSub.Form.RecordSource = strSQL
Me.frmISSNSub.Requery

End Sub
======================================================

Somehow i have to mix them together...

 

[wiklendt]

        str(Me.DiffA - [COLOR=Red]Me.txtMargin[/COLOR]) & " AND " & str(Me.DiffA + [COLOR=Red]Me.txtMargin[/COLOR]) & " AND "

Thank you for your reply Wiklendt.
  
I am one step further now. Here is the SOLUTION i have but i am trying to figure out how i am going to do it and put them all together because this sql change completly the recordset from the subform. 

========================================================
so when i press a second button i do this:

strSQL = "SELECT TOP 1 qryISSN.* FROM qryISSN ORDER BY Abs(DiffA-" & str(Me.DiffA) & ");"
Me!frmISSNSub.Form.RecordSource = strSQL
Me.frmISSNSub.Requery

FANTASTIC. This is exctaly what i am looking for.
========================================================

But that give s me another problem. When i click the other chk's (TRUE-FALSE) i have on the Main FORM to filter my data they show me results from my major FILTER. Not with the TOP 1.

My major filter working with this code:
========================================================
Private Sub cmdFilterSpec_Click()

strSQL = "SELECT * FROM qryISSN " & BuildFilterSpec

Me!frmISSNSub.Form.RecordSource = strSQL
Me.frmISSNSub.Requery

End Sub
======================================================

Somehow i have to mix them together...[/quote]

so why not change

[code]
Private Sub cmdFilterSpec_Click()

strSQL = "SELECT * FROM qryISSN " & BuildFilterSpec

to

Private Sub cmdFilterSpec_Click()

strSQL = "SELECT TOP 1 qryISSN.* FROM qryISSN " & BuildFilterSpec

?

 

[editolis]

OK here is the situation:

when i use 1 of this 2 examples everything is ok:
=============================================================
Me.frmISSNSub.Form.RecordSource = "SELECT * FROM qryISSN " & BuildFilterSpec

========================= OR ==================

Me.frmISSNSub.Form.RecordSource = "SELECT TOP 1 qryISSN.* FROM qryISSN ORDER BY Abs(DiffB-" & str(Me.DiffB) & ");"

================================================================
BUT I WANT SOMETHING LIKE THIS (mix):

Me.frmISSNSub.Form.RecordSource = "SELECT TOP 1 qryISSN.* FROM qryISSN ORDER BY Abs(DiffB-" & str(Me.DiffB) & " )" & BuildFilterSpec

BUT That gives me: run time error 3075. (missing operator).

What i am doing wrong?

 

[wiklendt]

Me.frmISSNSub.Form.RecordSource = "SELECT TOP 1 qryISSN.* FROM qryISSN ORDER BY Abs(DiffB-" & str(Me.DiffB) & " )" & BuildFilterSpec

BUT That gives me: run time error 3075. (missing operator).

What i am doing wrong?

from access help (F1)

SELECT Statement (Microsoft Access SQL)

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, …]]} FROM tableexpression [, …] [IN externaldatabase] [WHERE… ] [GROUP BY… ] [HAVING… ] [ORDER BY… ] [WITH OWNERACCESS OPTION]

so it could be the order in which your operators appear.
i'm pretty sure you have to do it in this order:

SELECT
FROM
WHERE
ORDER BY

but it seems you have the last two the other way around.

try putting the build filter before the ORDER BY...

you also seem to have dropped the trailing ";" in your combined SQL.

 

[editolis]

OK FINALLY THIS IS:

Me.frmISSNSub.Form.RecordSource = "SELECT TOP 1 qryISSN.* FROM qryISSN " & BuildFilterSpec _
& " ORDER BY Abs(DiffB-" & str(Me.DiffB) & " );"

Thank you wiklendt.

Do you have paypall account for the 3$?

 

[wiklendt]

OK FINALLY THIS IS:

Me.frmISSNSub.Form.RecordSource = "SELECT TOP 1 qryISSN.* FROM qryISSN " & BuildFilterSpec _
& " ORDER BY Abs(DiffB-" & str(Me.DiffB) & " );"

Thank you wiklendt.

Do you have paypall account for the 3$?

LOL. no payment necessary glad it's worked for you