Modify SQL query to account for three parameters instead of one

[m_low]

Hi everyone,

I have a search box function that works using the following code

SELECT *
FROM tblArchitectureFirms
WHERE [ArchitectureFirmName] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*"
OR [ArchitectureFirmType] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*"
OR [ArchitectureFirmAddress] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*"
OR [ArchitectureFirmContact] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*"
OR [ArchitectureFirmPhoneNumber] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*"
OR [ArchitectureFirmEmail] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*"
OR [ArchitectureFirmWebsite] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*"
OR [ArchitectureFirmNotes] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*";

However i need to add two combobox paramaters to further refine results a Mailing Consent Given: Yes, no, or Null and a Solicited: yes, no, or null

If it is selected null then i want that option ignored and for the database to search just using the other two or one paramaters.





This needs to be done in SQL as it is going on sharepoint.

Any ideas?

Thanks!

Michael

[ranman256]

Test all controls for a possible filter...

Select Case True
Case not isnull(cboState)
    sWhere = sWhere & " and [state]='" & cboState & "'"
Case not IsNull(txtName)
    sWhere = sWhere & " and [Name]='" & txtName & "'"
Case not IsNull(chkContact)
    sWhere = sWhere & " and [Contact]=" & chkContact.value
End Select

sSql = "SELECT * FROM tblArchitectureFirms WHERE "
sSql = sSql &  "[ArchitectureFirmName] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*"
sSql = sSql &  "OR [ArchitectureFirmType] LIKE "*" & Forms!frmArchitectureFirms!txtSearchBoxArchitectureFirms & "*"
sSql = sSql & sWhere

[m_low]

Thanks! I will try this out.

[ssanfu]

You might want to check out

Use a parameter to return all records if Null
http://access.mvps.org/access/queries/qry0001.htm

[orange]

Further to Steve's suggestion, I am attaching some thoughts about Forms and controls. The issue of NULL and zero length strings (ZLS) is mentioned. Although not all specific to your issue, you may find some interesting tips.
Good luck.

[kagoodwin13]

One limitation of [Field] OR [Field] IS NULL is optimization. If there is a search form with several search fields, all of which can be an input or can be null, the number of combinations of conditions to check causes the query to run pretty slow. I prefer to use the LIKE "*" & [Field] & "*"