You are talking apples and cocunuts or I am bananas.
Or is used in a query as part of the WHERE condition.
Union is used to append queries together.
Give an example of your expected input data and then expected output.

Signature
KARL DEWEY
Build a little - Test a little
> I am trying to make a function to convert a query with one or multiple OR
> conditions to a query with one or multiple
[quoted text clipped - 38 lines]
>
> RBS
RB Smissaert - 14 Jun 2007 21:33 GMT
> Give an example of your expected input data and then expected output
Not saying you are bananas, but that is exactly what I did.
RBS
> You are talking apples and cocunuts or I am bananas.
> Or is used in a query as part of the WHERE condition.
[quoted text clipped - 46 lines]
>>
>> RBS
OK, if anybody is interested in this I have put together a function that
works
for my particular situation where the OR applies to a particular field.
Not sure if Access is clever enough to automatically convert to the faster
method.
Function ConvertOr2Union(strSQL As String, _
Optional bUnionAll As Boolean = True) As String
Dim i As Long
Dim c As Long
Dim x As Long
Dim arrORS
Dim arrORSWithRead
Dim arrSQL
Dim strUnion As String
Dim strSQLUnion As String
Dim lWHEREPos As Long
Dim strAfterWHERE As String
Dim lStart As Long
Dim lEnd As Long
Dim strReadPart As String
Dim lUBReadBits As Long
If bUnionAll Then
strUnion = " UNION ALL "
Else
'this will remove duplicate rows and will be slower
'--------------------------------------------------
strUnion = " UNION "
End If
'get the OR parts after the WHERE
'--------------------------------
lWHEREPos = InStr(1, strSQL, "WHERE", vbTextCompare)
strAfterWHERE = Mid$(strSQL, lWHEREPos + 5)
arrORS = Split(strAfterWHERE, "OR", , vbTextCompare)
'count the READ_CODE OR parts
'----------------------------
For i = 0 To UBound(arrORS)
If InStr(1, UCase(arrORS(i)), "READ_CODE", vbBinaryCompare) > 0 Then
c = c + 1
End If
Next i
lUBReadBits = c - 1
If lUBReadBits = 0 Then
'no Read OR's so give original SQL back and get out
'--------------------------------------------------
ConvertOr2Union = strSQL
Exit Function
End If
'set up an array with the READ_CODE OR parts
'-------------------------------------------
ReDim arrORSWithRead(0 To lUBReadBits) As String
For i = 0 To lUBReadBits
If InStr(1, UCase(arrORS(i)), "READ_CODE", vbBinaryCompare) > 0 Then
arrORSWithRead(x) = arrORS(i)
x = x + 1
End If
Next i
'get the whole READ_CODE conditions string to be replaced
'--------------------------------------------------------
lStart = InStr(1, strSQL, arrORSWithRead(0), vbBinaryCompare)
lEnd = InStr(InStr(1, strSQL, arrORSWithRead(lUBReadBits),
vbBinaryCompare), _
strSQL, "AND", vbTextCompare)
If lEnd = 0 Then
lEnd = InStr(InStr(1, strSQL, arrORSWithRead(lUBReadBits),
vbBinaryCompare), _
strSQL, "ORDER", vbTextCompare)
End If
If lEnd = 0 Then
lEnd = Len(strSQL)
End If
strReadPart = Mid$(strSQL, lStart, lEnd - lStart)
'now get the clean READ_CODE OR bits
'-----------------------------------
arrORSWithRead = Split(strReadPart, "OR", -1, vbTextCompare)
'setup the new UNION SQL
'-----------------------
ReDim arrSQL(0 To lUBReadBits) As String
For i = 0 To lUBReadBits
If i = 0 Then
'clear opening bracket
'---------------------
If InStr(1, arrORSWithRead(0), "(", vbBinaryCompare) > 0 Then
arrORSWithRead(0) = Replace(arrORSWithRead(0), "(", "", 1, 1,
vbBinaryCompare)
End If
End If
If i = lUBReadBits Then
'clear closing bracket
'---------------------
If InStr(1, arrORSWithRead(lUBReadBits), ")", vbBinaryCompare) > 0
Then
arrORSWithRead(lUBReadBits) = _
Replace(arrORSWithRead(lUBReadBits), ")", "", 1, 1, vbBinaryCompare)
End If
End If
arrSQL(i) = Replace(strSQL, strReadPart, arrORSWithRead(i), 1, 1,
vbBinaryCompare)
Next i
For i = 0 To lUBReadBits
If i = 0 Then
strSQLUnion = arrSQL(i)
Else
strSQLUnion = strSQLUnion & strUnion & arrSQL(i)
End If
Next i
ConvertOr2Union = strSQLUnion
End Function
RBS
>I am trying to make a function to convert a query with one or multiple OR
>conditions to a query with one or multiple
[quoted text clipped - 38 lines]
>
> RBS