Sql

[shamas21]

Hi All

Im having trouble with the following SQL statement.

SELECT qrysearch.*
FROM qrysearch
WHERE IIf ( ISNULL ( Forms![Search]![cbxDivDirector] ) , [DIVISIONAL DIRECTOR] = "Eric Daniels", [DIVISIONAL DIRECTOR] =  Forms![Search]![cbxDivDirector] )

My problem is in the WHERE clause. All i want to know is if the Forms![Search]![cbxDivDirector] is Null (doesnt have a value in it) then i want the query to return "Eric Daniels" in the filter.

This works fine when the Form "Search" is in Design mode, but it bring up the parameter box if its in View mode.

I dont get why it does this?

Thanks

 

[LPurvis]

You're perhaps talking about just:
SELECT *
FROM qrysearch
WHERE [DIVISIONAL DIRECTOR] = Nz(Forms![Search]![cbxDivDirector], "Eric Daniels")

How are you using this query though? Executed via the UI or code?

I'd always look to avoid hard coding a value like that into a query definition personally. (And it's unlikely to be a text value it references - but the related ID instead. See how it fits with what you're actually doing...)

 

[dallr]

Leigh like you are popping up all over the place now. LOL

Dane a.k.a dallr

 

[boblarson]

Leigh like you are popping up all over the place now. LOL

Dane a.k.a dallr

MVP Insurance (just kidding)

 

[dallr]

"MVP Insurance", I like that one Bob!!

 

[LPurvis]

lol. Ha - were that the case then I'd have left it rather too late for consideration.
I'm a July baby.