Between on two fields

[NotAnExpert]

Good morning all

I have a query which populates a subform based on values as below:

main form txtOD, txtID textboxes with an afterupdate event on each set to requery the subform

I want the query to be able to show all records if nothing is entered in any of the 2 search boxes. I would have normally used NZ for this.

Ideally, I would like to limit/filter the list to show records where the OD field contains values that are:

between the value in txtOD +0.1 and txtOD - 0.2 on the mainform,
between the value in txtID -0.1 and txtID +0.2 on the mainform

for the criteria I have used:

Between ([forms]![frmDFGtools]![txtOD]-0.2) And ([forms]![frmDFGtools]![txtOD])

so I can filter based on txtOD alone as long as values exist, can filter based on txtID alone as long as values exist or both as long as they sit in the between values...

I just can't seem to get this one to work...

As always, any help would be great!

 

[Ranman256]

you cant put the -0.2 in the query,
put it in the form. Add 2 more boxes to hold the tolerance value that get the range once you enter the main box value....

'after update
txtID1 = txtID -.2
txtID2 = txtID+.2

query:
Between ([forms]![frmDFGtools]![txtID1]) And ([forms]![frmDFGtools]![txtID2])

 

[arnelgp]

the criteria would be:

([yourFieldForOD] Between (iif(Trim([forms]![frmDFGtools]![txtOD] & "")="",[yourFieldForOD],[forms]![frmDFGtools]![txtOD]-0.2)) And (iif(Trim([forms]![frmDFGtools]![txtOD] & "")="",[yourFieldForOD],[forms]![frmDFGtools]![txtOD]+0.1)))
And
([yourFieldForID] Between (iif(Trim([forms]![frmDFGtools]![txtID] & "")="",[yourFieldForID],[forms]![frmDFGtools]![txtID]-0.2)) And (iif(Trim([forms]![frmDFGtools]![txtID] & "")="",[yourFieldForID],[forms]![frmDFGtools]![txtID]+0.1)))

 

[NotAnExpert]

Hi both and thank you for taking the time to respond.

Just so you are aware, the code I wrote above works perfectly fine in the query as I have already tested it. The problem I have is that I want the list to filter down with the rules I have in there rather than making everything dissappear until a matching set exists...

for instance:

tblTools
OD
14.2
14.3

ID
7.2
10.2

qryTools
rOD
Criteria: Between [Forms]![frmDFGToolList]![txtOD]-0.2 And [Forms]![frmDFGToolList]![txtOD]+0.1

rID
Between [forms]![frmDFGToolList]![txtID]-0.1 And [Forms]![frmDFGToolList]![txtID]+0.2

entering in the two search boxes set up on the main form:

txtOD = 14.3

on it's own lists nothing

txtID = 10.1 on its own lists nothing

both txtOD and txtID with the appropriate values work and lists the appropriate number of records.

HOWEVER i'm wanting to list to show records even if it just matches one of the values if the other box is left blank.

I used to use the like nz(stuff, "*") criteria but I can't use between in the middle of it apparently.

 

[arnelgp]

what does ID and OD?

inside dia. and outside dia.

i think you will get nothing when done that way.
you need only one criteria on each fields.

Where 
   [ID] >= IIF(TRIM([forms]![frmDFGtools]![txtID] & "")="", [ID], [forms]![frmDFGtools]![txtID] - 0.1)
   AND
   [OD] <= IIF(TRIM([forms]![frmDFGtools]![txtOD] & "")="", [OD], [forms]![frmDFGtools]![txtOD] + 0.1)

 

[NotAnExpert]

Hi, thanks for replying again.

Please find the screenshots for my set up and the results from the query on the subform i receive.

As you can see, the query does work, it just doesn't work on an individual basis.

I am positive that this is because the query demands that both boxes be filled in, it's just that I need to figure out what will allow me to have just one of the search boxes filled in.

I also want to show ALL records if the search boxes are blank, again thank you for assistance so far.

 

[Mark_]

I think the issue is you want to set one of FOUR different criteria;
1) IF Neither form field is filled, no filter.
2) If the first form field is filled but not the second, filter on the first.
3) If the second form field is filtered but not the first , filter on the second.
4) If BOTH fields are filled, filter on both.

This means both of your fields need to conditionally have their filters set.

For myself, once you start getting into this I'd switch to using an SQL statement and conditionally formatting the SQL statement's where. Something like

DIM asSQL as STRING
DIM asWhere as STRING
DIM asOrder as STRING
asSQL = "SELECT Your, Fields, Here FROM tblTableName"

asWhere = "" 'Clear anything we had
IF Condition THEN asWhere = asWhere & "Your Where Clause Here"
IF Condition THEN asWhere = asWhere & "Your Where Clause Here"
IF Condition THEN asWhere = asWhere & "Your Where Clause Here"

asOrder = " ORDER BY FieldName " ' so you can change order if needed.

'We make the SQL work first and have ONE variable we are not messing with to hold it.
'We make the WHERE clause next. We keep them separate initially for debugging.
'Then we put them together along with how we want to order them.
asSQL = asSQL & asWhere & asOrder

Depending on how you want your users to see your data you may want to also order by the column they filter on.

Does this give you a different approach to your issue?