strSQL multiple WHERE statements

  • Thread starter Thread starter Fred
  • Start date Start date
F

Fred

I have a SQL query I'm trying to extend abit. The query is listed
below and works fine.

strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') "

I need to add the following condition to the query.

WHERE tblPeople.DataClosed = Null.

Can anyone tell me the syntax I might use for this?
 
I have a SQL query I'm trying to extend abit.  The query is listed
below and works fine.

strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') "

I need to add the following condition to the query.

WHERE tblPeople.DataClosed = Null.

Can anyone tell me the syntax I might use for this?
Click to expand...

How about your friendly neighborhood AND?
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*' AND tblPeople.DataClosed = Null) "
 
How about your friendly neighborhood AND?
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*' AND tblPeople.DataClosed = Null) "
Click to expand...

Adding this seems to prevent the search string from finding any
records. The functionality is that the user types a value into a text
box and that test box is linked to this SQL query.
 
Fred said:
I have a SQL query I'm trying to extend abit. The query is listed
below and works fine.

strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') "

I need to add the following condition to the query.

WHERE tblPeople.DataClosed = Null.

Can anyone tell me the syntax I might use for this?
Click to expand...

strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" & txtSearchString &
"*') "
strSQL = strSQL & "AND tblPeople.DataClosed IS Null"

Note the use of the keyword IS instead of =.
 
I have a SQL query I'm trying to extend abit. The query is listed
below and works fine.

strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') "

I need to add the following condition to the query.

WHERE tblPeople.DataClosed = Null.

Can anyone tell me the syntax I might use for this?
Click to expand...



Nothing is ever = Null.
But you can use
IsNull([FieldName])
or
[FieldName] Is Null

Also, it's not clear from your question as to whether or not you wish
to 'AND' the second condition or 'OR' it.

Try:
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') And tblPeople.DateClosed Is Null"

Change the And to OR if that is what you need.
 
I have a SQL query I'm trying to extend abit.  The query is listed
below and works fine.
Click to expand...
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') "
Click to expand...
I need to add the following condition to the query.
Click to expand...
WHERE tblPeople.DataClosed = Null.
Click to expand...
Can anyone tell me the syntax I might use for this?
Click to expand...

Nothing is ever = Null.
But you can use
IsNull([FieldName])
or
[FieldName] Is Null

Also, it's not clear from your question as to whether or not you wish
to 'AND' the second condition or 'OR' it.

Try:
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
 txtSearchString & "*') And tblPeople.DateClosed Is Null"

 Change the And to OR if that is what you need.
Click to expand...

Thanks for the suggestions. I've tried both of these formats.

Here's a copy and paste of the code I just added.

strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') And tblPeople.DateClosed Is Null"

This produces no results. I tested the null statement with some other
criteria like = 1/2/2010 which has valid data in the table but still
was unable to return any results.
 
Fred,

Slight change, see if the below works...

"WHERE ((tblPeople.FirstName) Like " & txtSearchString & "*") OR
((tblPeople.DateClosed Is Null))"

If that doesn't work then try it with the OR part and see if that works.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I have a SQL query I'm trying to extend abit. The query is listed
below and works fine.
Click to expand...
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') "
Click to expand...
I need to add the following condition to the query.
Click to expand...
WHERE tblPeople.DataClosed = Null.
Click to expand...
Can anyone tell me the syntax I might use for this?
Click to expand...

Nothing is ever = Null.
But you can use
IsNull([FieldName])
or
[FieldName] Is Null

Also, it's not clear from your question as to whether or not you wish
to 'AND' the second condition or 'OR' it.

Try:
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') And tblPeople.DateClosed Is Null"

Change the And to OR if that is what you need.
Click to expand...

Thanks for the suggestions. I've tried both of these formats.

Here's a copy and paste of the code I just added.

strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') And tblPeople.DateClosed Is Null"

This produces no results. I tested the null statement with some other
criteria like = 1/2/2010 which has valid data in the table but still
was unable to return any results.
 
Fred,

Slight change, see if the below works...

"WHERE ((tblPeople.FirstName) Like " & txtSearchString & "*") OR
((tblPeople.DateClosed Is Null))"

If that doesn't work then try it with the OR part and see if that works.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Nothing is ever = Null.
But you can use
IsNull([FieldName])
or
[FieldName] Is Null
Click to expand...
Also, it's not clear from your question as to whether or not you wish
to 'AND' the second condition or 'OR' it.
Click to expand...
Try:
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') And tblPeople.DateClosed Is Null"
Click to expand...
Change the And to OR if that is what you need.
Click to expand...

Thanks for the suggestions.  I've tried both of these formats.

Here's a copy and paste of the code I just added.

strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') And tblPeople.DateClosed Is Null"

This produces no results.  I tested the null statement with some other
criteria like = 1/2/2010 which has valid data in the table but still
was unable to return any results.
Click to expand...

I appreciate the suggestions.

Still not working as intended.

Perhaps it would help if I shared the entire code?


Private Sub txtFirstName_Change()
Dim txtSearchString As Variant
Dim strSQL As String
txtSearchString = Me![txtFirstName].Text
strSQL = "SELECT DISTINCTROW tblPeople.LastName, tblPeople.FirstName,
tblPeople.Address, tblPeople.MiddleName, tblPeople.FaxNumber,
tblPeople.AlternatePhone, tblPeople.DateClosed,
tblPeople.FollowupDate FROM tblPeople "
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like " &
txtSearchString & "*') OR ((tblPeople.DateClosed Is Null))"
strSQL = strSQL & "ORDER BY tblPeople.FaxNumber DESC"
Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!txtFirstName.SetFocus
End Sub
 
Still not working as intended.
Click to expand...

I think you may need the syntactical quotemarks around the string. Since a
name might contain a ' character (O'Rielly for example) use a doublequote
delimiter; to include a doublequote in a doublequote delimited string double
the doublequote (how's THAT for doubletalk!):

"WHERE ((tblPeople.FirstName) Like """ & txtSearchString & "*""") OR
((tblPeople.DateClosed Is Null))"
Perhaps it would help if I shared the entire code?
Click to expand...

Most likely it would!
 
Fred,

You say "...still not working as I intened..." perhaps I missed it but what
results are you expecting?

After reading your code, not sure why you felt the need for txtSearchString?
(Watch out for word wrap.)

'****Start of UNTESTED Code****
Private Sub txtFirstName_Change()
On Error Resume Next 'Cause I'm too lazy to include real error handling
(that be Gina not you)

Dim strSQL As String

strSQL = "SELECT DISTINCTROW tblPeople.LastName, tblPeople.FirstName,
tblPeople.Address, tblPeople.MiddleName, tblPeople.FaxNumber,
tblPeople.AlternatePhone, tblPeople.DateClosed, tblPeople.FollowupDate FROM
tblPeople"
strSQL = strSQL & "WHERE (((tblPeople.FirstName) Like [Type in Any Part of
First Name or Enter for All] & "*")) OR (((tblPeople.DateClosed) Is Null))"
strSQL = strSQL & "ORDER BY tblPeople.FaxNumber DESC;"

Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!txtFirstName.SetFocus
End Sub
'****End of UNTESTED Code****

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Fred,

Slight change, see if the below works...

"WHERE ((tblPeople.FirstName) Like " & txtSearchString & "*") OR
((tblPeople.DateClosed Is Null))"

If that doesn't work then try it with the OR part and see if that works.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm


Nothing is ever = Null.
But you can use
IsNull([FieldName])
or
[FieldName] Is Null
Click to expand...
Also, it's not clear from your question as to whether or not you wish
to 'AND' the second condition or 'OR' it.
Click to expand...
Try:
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') And tblPeople.DateClosed Is Null"
Click to expand...
Change the And to OR if that is what you need.
Click to expand...

Thanks for the suggestions. I've tried both of these formats.

Here's a copy and paste of the code I just added.

strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like '" &
txtSearchString & "*') And tblPeople.DateClosed Is Null"

This produces no results. I tested the null statement with some other
criteria like = 1/2/2010 which has valid data in the table but still
was unable to return any results.
Click to expand...

I appreciate the suggestions.

Still not working as intended.

Perhaps it would help if I shared the entire code?


Private Sub txtFirstName_Change()
Dim txtSearchString As Variant
Dim strSQL As String
txtSearchString = Me![txtFirstName].Text
strSQL = "SELECT DISTINCTROW tblPeople.LastName, tblPeople.FirstName,
tblPeople.Address, tblPeople.MiddleName, tblPeople.FaxNumber,
tblPeople.AlternatePhone, tblPeople.DateClosed,
tblPeople.FollowupDate FROM tblPeople "
strSQL = strSQL & "WHERE ((tblPeople.FirstName) Like " &
txtSearchString & "*') OR ((tblPeople.DateClosed Is Null))"
strSQL = strSQL & "ORDER BY tblPeople.FaxNumber DESC"
Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!txtFirstName.SetFocus
End Sub
 
Slight change, see if the below works...

"WHERE ((tblPeople.FirstName) Like " & txtSearchString & "*") OR
((tblPeople.DateClosed Is Null))"

If that doesn't work then try it with the OR part and see if that
works.
Click to expand...

Why did it take this long into the thread for someone to figure this
out?

You can't test for equality or LIKE against as string value and also
test for AND Is Null, because the two are mutually contradictory. If
it matches the LIKE it's not Null, and if it Is Null, then it can't
match the LIKE, but if you use AND, you're asking it to match both,
i.e., it's empty, yet it has a value in it that matches.
 
Slight change, see if the below works...

"WHERE ((tblPeople.FirstName) Like " & txtSearchString & "*") OR
((tblPeople.DateClosed Is Null))"

If that doesn't work then try it with the OR part and see if that
works.
Click to expand...

Oh, darn, disregard my post -- I was misreading it as criteria on
the same field!
 
[nothing that was helpful!]

I misread the post -- the criteria weren't on the same field at all,
which was clear from the beginning.
 
Back
Top