CodingForums.com - View Single Post - How to return all data when no variable specified in SQL WHERE?

Old Pedant · 10-04-2012, 11:49 PM

Various ways.

The best way is to not even include the condition in the SQL if it's not specified.

For example:

Code:

SQL = "SELECT * FROM INCIDENTS WHERE 1=1 "
If department <> "" Then
    SQL = SQL & " AND H#DEPT1 = '" & Replace(department,"'","''") & "' "
End If
If username <> "" Then
    SQL = SQL & " AND H#USERNAME1 = '" & Replace(username,"'","''") & "' "
End If

10-04-2012, 11:49 PM	PM User \| #2
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,247 Thanks: 59 Thanked 3,998 Times in 3,967 Posts	Various ways. The best way is to not even include the condition in the SQL if it's not specified. For example: Code: SQL = "SELECT * FROM INCIDENTS WHERE 1=1 " If department <> "" Then SQL = SQL & " AND H#DEPT1 = '" & Replace(department,"'","''") & "' " End If If username <> "" Then SQL = SQL & " AND H#USERNAME1 = '" & Replace(username,"'","''") & "' " End If __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.