And Or statement, is there a limit to the number of "Or" in a query?

[luccas]

I am attempting to make a query for 10 text boxes. I have tried to enter it in the Zoom Box and I,ve entered it row by row in the query. Every time it gets cut short after being saved. How do I accomplish this?

Example of saved version (Incomplete)
Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text0]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text2]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text4]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text6]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text8]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text10]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text12]) & "*" Or Like "*"

Example of full version (Correct)
Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text0]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text2]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text4]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text6]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text8]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text10]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text12]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text14]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text16]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text18]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text20]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text22]) & "*" Or Like "*" & Nz([Forms]![frmDialogCategory/Ethnicity]![Text24]) & "*"

 

[Golom]

I don't think there's a limit to "OR" but there is a line length limit in Access. Your 'Full Version' example is a line of over 900 characters and I'm guessing that you've blown over the maximum line length.

 

[LogicalmanUS]

luccas,

As Golom states, you appear to have exceeded the maximum number of characters for a Query.

I do wonder if you need to create such a query. If this query would be used as a recordsource for a form or report, you could just create the query string and then hand it to the form or Report as the object opens.

Logicalman

 

[jsteph]

luccas,
Are you sure you need 'Like'? This complicates things and slows things down. If the text will be codes or strings that can only be one thing, you shouldn't need 'Like'. You can make the textboxes comboboxes to give the user a list of valid selections.
Then, you can concat as many textboxes as you wish, ie:

IN(nz([Forms]![frmDialogCategory/Ethnicity]![Text1]),nz([Forms]![frmDialogCategory/Ethnicity]![Text2]),nz([Forms]![frmDialogCategory/Ethnicity]![Text3]),etc,etc,etc,)

If this is in a query grid you don't need to put quotes in this IN clause. If you were building this as a sql string in code to be set to some recordset's source or querydefs source, you'd need to concat quotes in the IN clause.

ANOTHER question...is the reason that you are using the OR because the user can only enter ONE textbox, but you don't know which one he'll use? I've seen this before, and if that's the case, that's easy to overcome as well.

--jsteph

 

[luccas]

jsteph,

I need the ‘Like’ it is the only way I’ve tried that gives the correct result.

This in clause is in a query grid - an example of how to concat quotesin the in clause would help.

You are correct, the reason Or is being used is because the user could use any combination of text boxes.

luccas

 

[LogicalmanUS]

luccas,

Are all the text boxes required?

Logicalman

 

[LogicalmanUS]

LUCCAS,

If all boxes are not required, and the user may have any combination, then the code below shows how to create an efficient statement, using both LIKE and IN statements.

It is very inefficient to create statement having syntax like " WHERE myCol like '*'", as this will simply cause a search on the column that is not required.

Each is a function that can be added to an SQL statement simply by calling it thus:

mySQL = "SELECT * FROM myTABLE " & GET_WHERE_LIKE(colName)
mySQL = "SELECT * FROM myTABLE " & GET_WHERE_IN(colName)

Using * in IN statements is not usual, and therefore I have left them out in the latter Function.


Function GET_WHERE_LIKE(byVal colName as String) As String

Dim sWHERE As String
Dim frm As Form

Set frm = [Forms]![frmDialogCategory/Ethnicity]
sWHERE = ""

If Not IsNull(frm.Text0) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text0 & "*' "
End If
If Not IsNull(frm.Text2) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '* " & frm.Text2 & "*' "
End If
If Not IsNull(frm.Text4) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '* " & frm.Text4 & "*' "
End If
If Not IsNull(frm.Text6) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '* " & frm.Text6 & "*' "
End If
If Not IsNull(frm.Text8) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text8 & "*' "
End If
If Not IsNull(frm.Text10) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text10 & "*' "
End If
If Not IsNull(frm.Text12) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text12 & "*' "
End If
If Not IsNull(frm.Text14) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text14 & "*' "
End If
If Not IsNull(frm.Text16) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text16 & "*' "
End If
If Not IsNull(frm.Text18) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text18 & "*' "
End If
If Not IsNull(frm.Text20) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text20 & "*' "
End If
If Not IsNull(frm.Text22) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text22 & "*' "
End If
If Not IsNull(frm.Text24) Then
sWHERE = sWHERE & "OR " & colName & " LIKE '*" & frm.Text24 & "*' "
End If

GET_WHERE = "WHERE " & Mid(sWHERE, 3)
Set frm = Nothing

End Function

Function GET_WHERE_IN(byVal colName as Str) As String

Dim sWHERE As String
Dim frm As Form

Set frm = [Forms]![frmDialogCategory/Ethnicity]
sWHERE = ""

If Not IsNull(frm.Text0) Then
sWHERE = sWHERE & ", '" & frm.Text0 & "' "
End If
If Not IsNull(frm.Text2) Then
sWHERE = sWHERE & ", '" & frm.Text2 & "' "
End If
If Not IsNull(frm.Text4) Then
sWHERE = sWHERE & ", '" & frm.Text4 & "' "
End If
If Not IsNull(frm.Text6) Then
sWHERE = sWHERE & ", '" & frm.Text6 & "' "
End If
If Not IsNull(frm.Text8) Then
sWHERE = sWHERE & ", '" & frm.Text8 & "' "
End If
If Not IsNull(frm.Text10) Then
sWHERE = sWHERE & ", '" & frm.Text10 & "' "
End If
If Not IsNull(frm.Text12) Then
sWHERE = sWHERE & ", '" & frm.Text12 & "' "
End If
If Not IsNull(frm.Text14) Then
sWHERE = sWHERE & ", '" & frm.Text14 & "' "
End If
If Not IsNull(frm.Text16) Then
sWHERE = sWHERE & ", '" & frm.Text16 & "' "
End If
If Not IsNull(frm.Text18) Then
sWHERE = sWHERE & ", '" & frm.Text18 & "' "
End If
If Not IsNull(frm.Text20) Then
sWHERE = sWHERE & ", '" & frm.Text20 & "' "
End If
If Not IsNull(frm.Text22) Then
sWHERE = sWHERE & ", '" & frm.Text22 & "' "
End If
If Not IsNull(frm.Text24) Then
sWHERE = sWHERE & ", '" & frm.Text24 & "' "
End If

GET_WHERE = "WHERE " & colName & " IN (" & Mid(sWHERE, 2) & &quot"
Set frm = Nothing

End Function

Logicalman

 

[luccas]

I need the ‘Like’ it is the only way I’ve tried that gives the correct result.

This in clause is in a query grid - an example of how to concat quotesin the in clause would help.

You are correct, the reason Or is being used is because the user could use any combination of text boxes.

geodelb

 

[LogicalmanUS]

luccas,

Then the first function should assist you.

Logicalman