Multiple Fields In Query Statement

Question

So the following query statement works just fine. But I need to add an additional field to check, is that possible?

So if FIELD1 or FIELD2 is equal to whatever value is in the Combo254.text, then select those records.

Here is the current query I am running:

Temp = Combo254.Text

Dim strSQL As String
Dim strWhere As String
strWhere = (Chr(34) + Combo254.Text + (Chr(34)))

strSQL = "SELECT * FROM MainQuery WHERE [FIELD1] Like " & strWhere

'MsgBox (strSQL)
[Form_Main].RecordSource = strSQL

Tmdean · Accepted Answer · 2013-06-20 20:39:01Z

2

Join each of the additional criteria with the word AND or OR.

As a side note, VBA allows you to include quotation marks in literal strings if you double the quotation marks. I think this is a bit more readable than using Chr(34).

strSQL = "SELECT * FROM MainQuery " & _
    "WHERE [FIELD1] Like """ & Combo254.Text & """ OR " & _
    "[FIELD2] Like """ & Combo254.Text & """"

In this situation, Access doesn't care if you use single quotes or double quotes, so you can rewrite the query like this:

strSQL = "SELECT * FROM MainQuery " & _
    "WHERE [FIELD1] Like '" & Combo254.Text & "' OR " & _
    "[FIELD2] Like '" & Combo254.Text & "'"

edited Jun 20, 2013 at 20:39

answered Jun 20, 2013 at 19:22

Tmdean

9,32945 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Muhnamana Over a year ago

Does this look about right? I'm getting an expected end statement, probably missing a ' or " somewhere. I'm not seeing it.

Muhnamana Over a year ago

strSQL = "SELECT * FROM MainQuery " & "WHERE [FIELD1] Like '" & Combo254.Text & "' OR " & "[FIELD2] Like '" & Combo254.Text "'"

Tmdean Over a year ago

You need an & before the final string literal. Combo254.Text & "'". Also, you only need to use & to concatenate strings when you need to join in a variable or split the string over multiple lines. If it's all on one line, then your first & is unnecessary, for example.

Muhnamana Over a year ago

Any chance you can edit your post to include it all on one line? My brain is fried right now, so a break will help too!

Collectives™ on Stack Overflow

Multiple Fields In Query Statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related