SQL command

[NotAnExpert]

Hi, I am running a form with a subform, the main form has several search boxes but I want the query to work primarily from two.

i.e.

OD and ID

What i'm after is typing a number in the OD textbox, same for the ID textbox and the query return any record where the OD is between = OD-1 and OD+1, and as a second part the ID is between ID-1 and ID+1.

I have gotten it to work in a way BUT I need to also include records where the OD or ID might have been left blank? Ideally I would like to do this within the SQL view (as i'm wanting to do everything using SQL eventually)

This is what I have so far:

SELECT tblDFGtools.TOOL, tblDFGtools.rOD, tblDFGtools.rID, tblDFGtools.THK, tblDFGtools.SHAPE, tblDFGtools.NOTES, tblDFGtools.SHELF
FROM tblDFGtools
WHERE ((tblDFGtools.rOD Between [txtOD]-1 And [txtOD]+1) AND (tblDFGtools.rID Between [txtID]-1 And [txtID]+1))
OR ((tblDFGtools.rOD Between [txtOD]-1 And [txtOD]+1) OR (tblDFGtools.rID Between [txtID]-1 And [txtID]+1))
;

It just doesn't appear to work quite the way I need it.

List all records where OD between txtOD -1 and txtOD +1 AND ID box filled in then list all records where ID = txtID -1 and txtID +1
or
List all records where OD between txtOD -1 and txtOD +1 AND txtID box left blank
or
List all records where ID between txtID -1 and txtID +1 AND txtOD box left blank
or ALL records where both boxes left blank...

what it seems to be doing is all at once, so I pretty much see all records or none...

I have the thought that it's how i'm formatting the AND/OR bits but I must be missing something there...

Any help gratefully received.

 

[theDBguy]

Hi. I might try something like:

(OD Between txtOD-1 And txtOD+1 OR txtOD Is Null) AND (ID Between txtID-1 And txtID+1 OR txtID Is Null)

 

[arnelgp]

you need to use nz:

... WHERE (((tblDFGTools.rOD)>=Nz([Forms]![yourFormName]![txtOD]-1,tblDFGTools.[rOD]) 
      And (tblDFGTools.rOD)<=Nz([Forms]![yourForm]![txtOD]+1,tblDFGTools.[rOD])) 
        AND ((tblDFGTools.rID)>=Nz([Forms]![yourForm]![txtID]-1,tblDFGTools.[rID]) 
          And (tblDFGTools.rID)<=Nz([Forms]![yourForm]![txID]+1,tblDFGTools.[rID])));

 

[NotAnExpert]

I'm afraid theDBGuy your code didn't quite work out for me, but thanks to both you and arnelgp, the second set of code worked beautifully.

Very much appreciated and that one little thing changes the dynamic of a very useful tool in the office.

Very kind!

 

[theDBguy]

I'm afraid theDBGuy your code didn't quite work out for me, but thanks to both you and arnelgp, the second set of code worked beautifully.

Very much appreciated and that one little thing changes the dynamic of a very useful tool in the office.

Very kind!

Hi. Glad to hear you got it sorted out. Good luck with your project.