CODEWITHSUNDEEP
sqlms-access
SarahSedaii
SarahSedaii

Reputation: 25

Writing IF THEN statements in WHERE Clause of SQL

I am working in Access to write a search form for my database. I want to have a form with 5 fields to search by. IF there something in the field, it needs to search within those parameters - Else the field needs to be ignored. here is what I am working with now, but I am having trouble getting it to work. The types of fields to search by will be 1. Combo Box 2. Date Ranges

Select *
From NLog
Where
If [Forms]![Search]![Textbox1] > 0 THEN [Forms]![Search]![Textbox1] = Nlog.CoNo
Else Conintue

Thank you!

Upvotes: 0

Views: 1374

Answers (2)

Alessandro Rossi
Alessandro Rossi

Reputation: 2450

If you read here you can see that a material injunction (IF-THEN) can be converted to a disjunction (OR). So in your case you'll have:

NOT [Forms]![Search]![Textbox1] > 0 OR [Forms]![Search]![Textbox1] = Nlog.CoNo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Just use regular logic:

Select *
From NLog
Where ([Forms]![Search]![Textbox1] <= 0 OR [Forms]![Search]![Textbox1] = Nlog.CoNo);

Note: this assumes that [Forms]![Search]![Textbox1] is not NULL. If that is a possibility, you can handle that using explicit logic or the NZ() function.

Upvotes: 2

Related Questions