CODEWITHSUNDEEP
ms-access
nman84
nman84

Reputation: 435

expression in ms access

This expression in search query is not giving any results.

code and code2 are combo boxes in the search form, CS_Code is a table column.

[CS_Code]=([Forms]![Search Form]![code] Or
[Forms]![Search Form]![code2]) Or
( [Forms]![Search Form]![code] Is Null Or
  [Forms]![Search Form]![code2] Is Null )

I am trying to get entries (search results) from table when CS_code is equal to code or code2

Upvotes: 0

Views: 67

Answers (2)

Andy Brown
Andy Brown

Reputation: 5522

You could shorten this using the IsNull function:

WHERE IsNull(([Forms]![Search Form]![code],[CS_Code]) = [CS_Code]

and similarly for the other field. If the combo box is null, this will return the table field value, which will always equal itself.

If you're doing this for several combo boxes, it might be time to step back and look at what you're trying to do. One possibility would be to write a VBA routine to loop over all of the combo boxes on the form.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91316

You need to repeat the comparison:

 WHERE ([CS_Code]=([Forms]![Search Form]![code] 
        Or [CS_Code]=[Forms]![Search Form]![code2]) 
 Or ([Forms]![Search Form]![code] Is Null
        Or [Forms]![Search Form]![code2] Is Null)

Upvotes: 2

Related Questions