SQL help ! thanks

[Dre313]

Can someone help me with my SQL..

Im doing a search of unique records.. I can't get it to work after adding the Is null stmts..

SELECT *
FROM tblInspections
WHERE ((tblInspections.strDate) = Between [Forms]![Search]![open] OR IsNull([Forms]![Search]![open]) And [Forms]![Search]![close] OR IsNull([Forms]![Search]![close])) AND ((tblInspections.REJ=[Forms]![Search]![REJ]) OR IsNull([Forms]![Search]![REJ])) AND ((tblInspections.strProject=[Forms]![Search]![strProject]) OR IsNull([Forms]![Search]![strProject]));

thanks

 

[Dre313]

This is my original Code:

SELECT *
FROM tblInspections
WHERE (((tblInspections.strDate) Between [Forms]![Search]![Open1] And [Forms]![Search]![Open2])) AND ((tblInspections.REJ=[Forms]![Search]![REJ]) OR IsNull([Forms]![Search]![REJ])) AND ((tblInspections.strProject=[Forms]![Search]![strProject]) OR IsNull([Forms]![Search]![strProject]));

When I add the IsNull stmt in.. I get errors.. What I want is.. even if the user leaves the date field blank.. it'll search for the other fields if asked..


thanks

 

[Golom]

You have some syntax problems to deal with.

Between Clause
The correct form is

Field BETWEEN Value1 AND Value2

You have

Field = BETWEEN Value1 AND Value2


Placement of IsNull
You have placed the "IsNull" calls INSIDE the BETWEEN clause. You have

... BETWEEN Value1 OR IsNull(Value1) AND Value2 OR IsNull(Value2) ...

I suspect that Value1 OR IsNull(Value1) is being evaluated to TRUE or FALSE and your BETWEEN clause is ultimately being evaluated as

... BETWEEN TRUE AND TRUE ...

You need a structure more like

(Field BETWEEN Value1 AND Value2)
OR IsNull(Value1) OR IsNull(Value2)

 

[Dre313]

ok I changed it to...

SQL:

SELECT *
FROM tblInspections
WHERE (((tblInspections.strDate) Between [Forms]![Search]![Open1] And [Forms]![Search]![Open2])) OR IsNull([Forms]![Search]![Open1])) OR OR IsNull([Forms]![Search]![Open2])) AND ((tblInspections.REJ=[Forms]![Search]![REJ]) OR IsNull([Forms]![Search]![REJ])) AND ((tblInspections.strProject=[Forms]![Search]![strProject]) OR IsNull([Forms]![Search]![strProject]));

And I'm still getting errors.. did I miss something ?

 

[Dre313]

oops there should be only one "OR" ... still getting errors.. its says extra ) in the query expression.

 

[Golom]

You have two "OR"s together and your parens are not matched. Try this

SELECT *
FROM tblInspections As T
WHERE (T.strDate Between [Forms]![Search]![Open1] And [Forms]![Search]![Open2] 
       OR IsNull([Forms]![Search]![Open1] OR IsNull([Forms]![Search]![Open2])

      AND (T.REJ=[Forms]![Search]![REJ] OR IsNull([Forms]![Search]![REJ]))

      AND (T.strProject=[Forms]![Search]![strProject] OR IsNull([Forms]![Search]![strProject]))

 

[Dre313]

I tried the code mentioned above... I getting a different error..

this time I'm getting..

Missing ),], or Item in query expression..

any ideas?

 

[Dre313]

I can't seem to find the "(" which should &quot" ...... I have 12 parenthesis all together but.. theres one more "(" then &quot"

I can't seem to find it.. can anyone else see it ??

thanks