Tough Select giving me troubles!(for multi combobox search)

[Sailor]

Hello.

I am attempting to design a search that makes use of multiple comboboxes to allow users to select an array of criteria.

My present goal is to allow users to search by the following choices, which I'll explain a little more:

- paygrade
- department

PayGrade lives in a table tblPayGrade that has a 1:1 with tblSailors. Dept lives in a table tblDept that is 1:1 with tblSailors. (Each Sailor has one PG and one DEPT) The form is 'SearchForm.'


SELECT tblPayGrade.PayGrade, tblDept.Dept
FROM tblPayGrade, tblDept WHERE (((TblPayGrade.PayGrade)=[Forms]![SearchForm]![TblPayGrade.PayGrade] Or Is Null([Forms]![SearchForm]![TblPayGrade.PayGrade])) AND ((TblDept.Dept)=[Forms]![SearchForm]![TblDept.Dept] Or (Is Null([Forms]![SearchForm]![TblDept.Dept])));

Access has not loved this statement fom the beginning! However, after clearing a bunch of complaints, I am now getting "Missing Operand."

Perhaps I've been staring at this thing too long, because I don't see the problem. However, Access stubbornly refuses to see my point of view.

I would love to get any guidance on this vexing SELECT, I guess starting with "Missing Operand."

Many thanks!

John

 

[dcx693]

I don't see anything syntactically wrong immediately in the SELECT, but I'd be suspicious of queries that draw on multiple tables without any joins between them.

 

[Jon K]

And the function is IsNull(), not Is Null()

 

[Sailor]

Thanks. That's what I had originally but when I move back to IsNull() I get "Syntax error in query expression '(((TblPayGrade.PayGrade)=[Forms]![SearchForm]![TblPayGrade.PayGrade] Or IsNull([Forms]![SearchForm]![TblPayGrade.PayGrade])) AND ((TblDept.Dept)=[Forms]![SearchForm]![TblDept.Dept] Or (IsNull([Forms]![SearchForm]![TblDept.Dept])));.'"

When I have Is Null, the error is the Operand one. Hmm.

And the function is IsNull(), not Is Null()

 

[Mile-O]

[Forms]![SearchForm]![TblPayGrade.PayGrade]


[Forms] = the forms collection;

[SearchForm] = the name of the form you are referencing within the collection

[TblPayGrade.PayGrade] ????

You should put the name of the control that you are referencing that holds the data you are querying with.

 

[Sailor]

Thanks. I think I'll head back to the drawing board based on the input of you three.

I need to put the values of two comboboxes (ComboPayGrade) and (ComboDept) in there. Ok. Thks again.

[Forms]![SearchForm]![TblPayGrade.PayGrade]


[Forms] = the forms collection;

[SearchForm] = the name of the form you are referencing within the collection

[TblPayGrade.PayGrade] ????

You should put the name of the control that you are referencing that holds the data you are querying with.

 

[Mile-O]

If, for example, your comboboxes are called cboPayGrade and cboDept then try changing the query to be something like:

SELECT tblPayGrade.PayGrade, tblDept.Dept
FROM tblPayGrade, tblDept WHERE (((TblPayGrade.PayGrade)=[Forms]![SearchForm]![cboPayGrade] Or IsNull([Forms]![SearchForm]![cboPayGrade])) AND ((TblDept.Dept)=[Forms]![SearchForm]![cboDept] Or (IsNull([Forms]![SearchForm]![cboDept])));