Colin

SQL = "SELECT * FROM qryConsolX WHERE ((Col5 IS NOT NULL)  AND (tickx=1)) ORDER BY Tick"

Should be all you need. Technically, you probably don't even need the parenthesis, but it's an old habit, I think makes it easier to read and see what's supposed to be happening

Donald - many thanks, and yes your suggestion does work, so much appreciated.  But being like Oliver Twist, I'm going to post another similar almost identical post which doesn't seem to wor with a simple AND join.  I won't post on this thread otherwise it could get very long.  Thanks again - Colin

Thanks again to Donald for solving the previous VBA SQL question, posted last night.  This variation regarding combining SQL WHERE routines in VBA only produces an error message.  

Routine one

Private Sub SBox_Change()
    SQL = "SELECT * FROM qryConsolX " & _
        "WHERE Tick LIKE """ & SBox.Text & "*"" ORDER BY qryConsolX.Tick"
    Me.RecordSource = SQL
    SQL2 = SQL
    SBox.SetFocus
    SBox.SelStart = Len(SBox.Text)
End Sub

Routine two

Private Sub Command92_Click()
    SBox = Null
    SQL = "SELECT * FROM qryConsolX WHERE (((qryConsolX.Col5) IS NOT NULL)) ORDER BY qryConsolX.Tick"
    Me.RecordSource = SQL
    SQL2 = SQL
    DoCmd.GoToControl "SBox"
End Sub
++++
I wish to combine the two routines but using the following SQL line, I've used AND to combine the two WHERE statements, produces an error.  

SQL = "SELECT * FROM qryConsolX WHERE (((qryConsolX.Col5) IS NOT NULL)) AND (Tick LIKE """ & SBox.Text & "*"") ORDER BY qryConsolX.Tick"

Can anyone help.

Double Double Quotes

What error message is it giving?

If you can't resolve the problem after reviewing the video Alex suggested, I would msgbox SQL before assigning it to me.recordsource that way you can see what Access sees.

You could try cleaning up the excess parenthesis Access puts in, but it shouldn't make a difference:

SQL = "SELECT * FROM qryConsolX WHERE ((Col5 IS NOT NULL) AND (Tick LIKE """ & SBox.Text & "*"")) ORDER BY Tick"

Alex & Donald - many thanks for your help.

I've now managed to generate an error message:

"Run-time error '2185'

You can't reference a property or method for a control unless the control has the focus."

The quicksand is now above my knees, probably above my waist.

Don't tell Richard but I don't think he has discussed this problem in a video.  I  have found on YouTube another Access expert who discusses the problem but his solution doesn't appear to work for me.  

If you have any ideas who to overcome '2185' that would be great but in the meantime, thank you for reading my post.

What I meant to say in the last paragraph is that I really appreciate you two for reading the post and coming up with solutions.  Don't worry about trying to get me out of the quagmire that I seem to have dug myself into.

Show us the full code with the combined SQL statement in place.

Most likely, move your command: SBox.SetFocus or docmd.GoToControl "SBox" so it is BEFORE the SQL declaration.

You're referencing the "Text" property of SBox in your SQL statement.

Should be able to ref the text box directly too
No need to set SBox = Null, just use = "".

Also SetFocus

Donald & Alex - belated thanks for the comments, much appreciated.  

Clearly I was doing something wrong regarding the grammar of SQL in VBA statements (fingers crossed Richard does a Techhelp video in 2026 about SQL grammar when inserted into VBA).

I headed back to Richard's legendary Sort seminar.  I have used the coding developed in lessons 14 to 18. I'm sure that I could be much more concise but it works!  

The result is what I was seeking, the ability to select in a form on two fields using SQL.

See below.  
++++
Private Sub RequeryForm()
Dim WhereStr As String
WhereStr = ""
Text94 = "1"
If Text94 = "1" Then
If WhereStr <> "" Then WhereStr = WhereStr & " AND "
    WhereStr = WhereStr & "DateTSXx LIKE """ & 1 & """"
End If
If SBox <> "" Then
If WhereStr <> "" Then WhereStr = WhereStr & " AND "
    WhereStr = WhereStr & "tick LIKE """ & SBox & "*"""
End If
SQL = "SELECT * from tblXX"
If WhereStr <> "" Then
SQL = SQL & " WHERE " & WhereStr
End If
SQL = SQL & " ORDER BY " & "Tick" & ""
SQL2 = SQL
Me.RecordSource = SQL
End Sub

There's a whole Access SQL Seminars series if you haven't already.

Alex - thanks for the heads up, I have them.  I'm keeping my fingers crossed that Richard produces more videos about using SQL in Microsoft Access.

What exactly are you wanting to see?
(There's stuff littered in various videos all over the site)

Alex - many thanks for your comments.

You have put your finger on what I would love to see by using the word 'scattered' in your reply.  Richard is amazing because (a) he is a great teacher, (b) knows so much about Access and (c) is incredibly productive.  I'm amazed how Richard manages to produce so many high quality videos week in and week out, year in year out.  Yes, there is a hell of a lot about SQL amongst Richard's many videos.

But what I would love to see under the Christmas tree in two days time is a Richard video that is over an hour long entitled Search & SQL.  A video that focuses on search using SQL and little else.  The search seminar which is available, and I have purchased, is great but it covers so much.  An hour of using SQL for search would be wonderful.

As one gets older - take one: the ability to forget things as one gets older becomes ever more impressive, so being able to work through an hour long video called Search & SQL once in a while that covers using SQL in forms to search for records would be great.

As one gets older - take two: I often forget how I have constructed a database, so Richard's suggestion of having a status box on the form is an excellent one, SQL lends itself to status boxes because it is easy to include SQL searches in status boxes.  This is another reason why it is an advantage for using SQL in forms rather than just using queries, hence my keenness to use SQL in nearly all my databases.

There are many TechHelps with different aspects of searching you could use too:
Easy Search Form
Easy Search Form 2
Search As You Type
Search Form 2.0
Wildcard Search & Like
Search Multiple Fields

Alex - many belated thanks for the names of the various TechHelps, like all of Richard's TechHelps, all are useful.

The problem I have is my attention span is akin to Dory in Finding Nemo. I'm hoping that Santa Claus comes down the chimney tonight and presents me with an hour long video from Richard entitled "VBA & SQL - the ultimate search video" which is about an hour long, races through the ways a form can be turbo changed by using SQL, no need to use complicated queries.  For instance: pressing on column headings to sort A-Z, press the heading again and the sort is Z-A, entering a letter in a label in the header and the form immediately changes because a search has been activated.  And in a label in the footer, the SQL being used is shown, which is useful because being a Dory, after several weeks I have often forget how I have constructed a form. Season's greetings.