Exclude a field from WHERE clause, if null

  • Thread starter Thread starter AAA
  • Start date Start date
A

AAA

Hi all,

I'm new to access - sorry, will you help ?
I want to exclude a form field from the SQL query, if it is left blank.

I do NOT want to set the field as 'Required' , just ignore it as a filter,
in case it left blank.

Is there a way to do this ?

Currently, I get exceptions, due to NULL values.

Thanks
(please post here - my email is no good)
 
AAA said:
Hi all,

I'm new to access - sorry, will you help ?
I want to exclude a form field from the SQL query, if it is left blank.

I do NOT want to set the field as 'Required' , just ignore it as a
filter, in case it left blank.

Is there a way to do this ?

Currently, I get exceptions, due to NULL values.
Click to expand...


If the field can't contain a zero-length string, then you can just test if
the field is Null:

SELECT * FROM MyTable WHERE SomeField Is Null

If the field might contain zero-length strings as well as Nulls, and you
want to exclude both, then you could write:


SELECT * FROM MyTable
WHERE SomeField Is Null OR SomeField = ''
 
Hello and thanks,

I'm afraid you misunderstood my question:
The NULL values are not in the DB, but in the form!

For instance, say I have a FirstName and LastName form fields.
You can search for "George" as first-name and "Bush" as last-name.
BUT, if the user only fills-in the FirstName with "George",
I want to retrieve all "George" records, regardless of LastName.

Currently, I get exceptions because the WHERE clause contains
parts that refer to the LastName form field, which is left blank (null).

Thanks
Meir
 
AAA said:
Hello and thanks,

I'm afraid you misunderstood my question:
The NULL values are not in the DB, but in the form!

For instance, say I have a FirstName and LastName form fields.
You can search for "George" as first-name and "Bush" as last-name.
BUT, if the user only fills-in the FirstName with "George",
I want to retrieve all "George" records, regardless of LastName.

Currently, I get exceptions because the WHERE clause contains
parts that refer to the LastName form field, which is left blank (null).

Thanks
Meir
Click to expand...


Ah, now I understand. If you're using a static, stored query, you can set
it up like this:

SELECT * FROM MyTable WHERE
((FirstName = [Forms]![frmMyForm]![txtFirstName])
OR ([Forms]![frmMyForm]![txtFirstName] Is Null))
AND
((LastName = [Forms]![frmMyForm]![txtLastName])
OR ([Forms]![frmMyForm]![txtLastName] Is Null))

If you're building the query on the fly in VBA code, you can simply check
which form controls are supplied and build the SQL statement accordingly:

'----- start of example code -----
Dim strSQL As String
Const Q As String = """"
Constr QQ As String = Q & Q

If Not IsNull(Me!txtFirstName) Then
strSQL = strSQL " AND (FirstName = " & _
Q & Replace(Me!FirstName, Q, QQ) & Q & ")"
End If

If Not IsNull(Me!txtLastName) Then
strSQL = strSQL " AND (LastName = " & _
Q & Replace(Me!LastName, Q, QQ) & Q & ")"
End If

' If any criteria were specified, convert to a WHERE clause
' and drop leading " AND ".
If Len(strSQL) > 0 Then
strSQL = " WHERE " & Mid(StrSQL, 6)
End If

strSQL = "SELECT * FROM MyTable" & strSQL
'----- end of example code -----
 
Back
Top