Error 2465 setting recordsource to SQL

N

NeoFax

I am receiving the error 2465, "Cannot find "|"..." error. Here is my
code:

Private Sub setFormSource()
Dim strSQL_Hdr As String
Dim strSQL As String
Dim strSQL_Ftr As String
Dim strWhere As String

strSQL_Hdr = "SELECT [tblOpen Orders].Vendor, [tblOpen Orders].[Vendor
name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item, [tblOpen
Orders].Type, [tblOpen Orders].MRPCn, [tblOpen Orders].Material,
[tblOpen Orders].[Short text], [tblOpen Orders].[Item Date], [tblOpen
Orders].[Item deliv], [tblOpen Orders].Quantity, [tblOpen Orders].[Exc
Code], [tblOpen Orders].[Open Qty], [tblOpen Orders].[GI Date],
[tblOpen Orders].HAWB, IIf(Date()>[Item deliv],(Date()-[Item deliv]),
0) AS [Days Late], [tblOpen Orders].[Net Price], [tblOpen Orders].[Net
Value], ([Net Price]*[Open Qty]) AS [Open Value]" & Chr(10)
strSQL = strSQL_Hdr & "FROM [tblOpen Orders]" & Chr(10)
strSQL = strSQL & "GROUP BY [tblOpen Orders].Vendor, [tblOpen Orders].
[Vendor name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item,
[tblOpen Orders].Type, [tblOpen Orders].MRPCn, [tblOpen
Orders].Material, [tblOpen Orders].[Short text], [tblOpen Orders].
[Item Date], [tblOpen Orders].[Item deliv], [tblOpen Orders].Quantity,
[tblOpen Orders].[Exc Code], [tblOpen Orders].[Open Qty], [tblOpen
Orders].[GI Date], [tblOpen Orders].HAWB, [tblOpen Orders].[Net
Price], [tblOpen Orders].[Net Value]" & Chr(10)

If Not IsNull(Me.cmbField1) Then
strWhere = "(([tblOpen Orders].[" & Me.cmbField1 & "])"
End If

guessFieldType (Me.cmbField1)

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator1) Then
Select Case Me.cmbOperator1
Case 0
MsgBox "No operator", vbInformation, "Nothing to do."
Case 1
strWhere = strWhere & "="
Case 2
strWhere = strWhere & ">"
Case 3
strWhere = strWhere & ">="
Case 4
strWhere = strWhere & "<"
Case 5
strWhere = strWhere & "<="
Case 6
strWhere = strWhere & "Is NULL)"
Me.Filter = strWhere
Me.FilterOn = True
Exit Sub
Case 7
strWhere = strWhere & "Like"
Case 8
strWhere = strWhere & "Not Like"
End Select
End If

If guessedField = "Num" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria1) Then
strWhere = strWhere & Me.txtCriteria1 & ")"
End If
ElseIf guessedField = "Date" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria1) Then
strWhere = strWhere & "#" & Me.txtCriteria1 & "#)"
End If
ElseIf guessedField = "Text" Then
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtCriteria1) Then
strWhere = strWhere & """" & Me.txtCriteria1 & """)"
End If
End If

'strSQL_Ftr = "HAVING (" & strWhere & " AND(([tblOpen Orders].[Open
Qty])>0));"
strSQL_Ftr = "HAVING (" & strWhere & ");"

strSQL = strSQL & strSQL_Ftr

'Dim rs As New ADODB.Recordset
'rs.Open strSQL, , adOpenStatic, adLockOptimistic


Me.[frmAll_Open_Orders_1].Form.RecordSource = strSQL

End Sub

Any help would be appreciated. Thanks!
 
N

NeoFax

Chr(10) is a carriage return...AFAIK, those are meaningless in SQL.  Use a
Space instead.  If you want, you can declare a constant SPACE = " ".  If you
want, you can run all the SQL together in one line and it will still execute.
Start there and tell us what happens.




I am receiving the error 2465, "Cannot find "|"..." error.  Here is my
code:
Click to expand...
Private Sub setFormSource()
Dim strSQL_Hdr As String
Dim strSQL As String
Dim strSQL_Ftr As String
Dim strWhere As String
Click to expand...
strSQL_Hdr = "SELECT [tblOpen Orders].Vendor, [tblOpen Orders].[Vendor
name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item, [tblOpen
Orders].Type, [tblOpen Orders].MRPCn, [tblOpen Orders].Material,
[tblOpen Orders].[Short text], [tblOpen Orders].[Item Date], [tblOpen
Orders].[Item deliv], [tblOpen Orders].Quantity, [tblOpen Orders].[Exc
Code], [tblOpen Orders].[Open Qty], [tblOpen Orders].[GI Date],
[tblOpen Orders].HAWB, IIf(Date()>[Item deliv],(Date()-[Item deliv]),
0) AS [Days Late], [tblOpen Orders].[Net Price], [tblOpen Orders].[Net
Value], ([Net Price]*[Open Qty]) AS [Open Value]" & Chr(10)
strSQL = strSQL_Hdr & "FROM [tblOpen Orders]" & Chr(10)
strSQL = strSQL & "GROUP BY [tblOpen Orders].Vendor, [tblOpen Orders].
[Vendor name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item,
[tblOpen Orders].Type, [tblOpen Orders].MRPCn, [tblOpen
Orders].Material, [tblOpen Orders].[Short text], [tblOpen Orders].
[Item Date], [tblOpen Orders].[Item deliv], [tblOpen Orders].Quantity,
[tblOpen Orders].[Exc Code], [tblOpen Orders].[Open Qty], [tblOpen
Orders].[GI Date], [tblOpen Orders].HAWB, [tblOpen Orders].[Net
Price], [tblOpen Orders].[Net Value]" & Chr(10)
Click to expand...
If Not IsNull(Me.cmbField1) Then
   strWhere = "(([tblOpen Orders].[" & Me.cmbField1 & "])"
End If
Click to expand...
guessFieldType (Me.cmbField1)
Click to expand...
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator1) Then
   Select Case Me.cmbOperator1
   Case 0
     MsgBox "No operator", vbInformation, "Nothing to do."
   Case 1
     strWhere = strWhere & "="
   Case 2
     strWhere = strWhere & ">"
   Case 3
     strWhere = strWhere & ">="
   Case 4
     strWhere = strWhere & "<"
   Case 5
     strWhere = strWhere & "<="
   Case 6
     strWhere = strWhere & "Is NULL)"
     Me.Filter = strWhere
     Me.FilterOn = True
     Exit Sub
   Case 7
     strWhere = strWhere & "Like"
   Case 8
     strWhere = strWhere & "Not Like"
   End Select
End If
Click to expand...
If guessedField = "Num" Then
 'Num field example. Do not add the extra quotes.
 If Not IsNull(Me.txtCriteria1) Then
   strWhere = strWhere & Me.txtCriteria1 & ")"
 End If
ElseIf guessedField = "Date" Then
 'Num field example. Do not add the extra quotes.
 If Not IsNull(Me.txtCriteria1) Then
   strWhere = strWhere & "#" & Me.txtCriteria1 & "#)"
 End If
ElseIf guessedField = "Text" Then
 'Text field example. Use quotes around the value in the string.
 If Not IsNull(Me.txtCriteria1) Then
   strWhere = strWhere & """" & Me.txtCriteria1 & """)"
 End If
End If
Click to expand...
'strSQL_Ftr = "HAVING (" & strWhere & " AND(([tblOpen Orders].[Open
Qty])>0));"
strSQL_Ftr = "HAVING (" & strWhere & ");"
Click to expand...
strSQL = strSQL & strSQL_Ftr
Click to expand...
'Dim rs As New ADODB.Recordset
'rs.Open strSQL, , adOpenStatic, adLockOptimistic
Click to expand...
Me.[frmAll_Open_Orders_1].Form.RecordSource = strSQL
Click to expand...
End Sub
Click to expand...
Any help would be appreciated.  Thanks!
Click to expand...
Click to expand...

Originally this is how I had it, and received the error. So I then
added the Chr(10) to make it look like the SQL view. If I put in a
break to check the variable strSQL and copy and paste this into a temp
query, the query works. However, it is the last line where I am
setting the forms recordsource is where I get the error. The only
other assumption I have is that the form currently does not have a
recordsource set and that could be causing the error.
 
N

NeoFax

Chr(10) is a carriage return...AFAIK, those are meaningless in SQL.  Use a
Space instead.  If you want, you can declare a constant SPACE = " "..  If you
want, you can run all the SQL together in one line and it will still execute.
Start there and tell us what happens.
Click to expand...
NeoFax said:
I am receiving the error 2465, "Cannot find "|"..." error.  Here is my
code:
Private Sub setFormSource()
Dim strSQL_Hdr As String
Dim strSQL As String
Dim strSQL_Ftr As String
Dim strWhere As String
strSQL_Hdr = "SELECT [tblOpen Orders].Vendor, [tblOpen Orders].[Vendor
name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item, [tblOpen
Orders].Type, [tblOpen Orders].MRPCn, [tblOpen Orders].Material,
[tblOpen Orders].[Short text], [tblOpen Orders].[Item Date], [tblOpen
Orders].[Item deliv], [tblOpen Orders].Quantity, [tblOpen Orders].[Exc
Code], [tblOpen Orders].[Open Qty], [tblOpen Orders].[GI Date],
[tblOpen Orders].HAWB, IIf(Date()>[Item deliv],(Date()-[Item deliv]),
0) AS [Days Late], [tblOpen Orders].[Net Price], [tblOpen Orders].[Net
Value], ([Net Price]*[Open Qty]) AS [Open Value]" & Chr(10)
strSQL = strSQL_Hdr & "FROM [tblOpen Orders]" & Chr(10)
strSQL = strSQL & "GROUP BY [tblOpen Orders].Vendor, [tblOpen Orders].
[Vendor name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item,
[tblOpen Orders].Type, [tblOpen Orders].MRPCn, [tblOpen
Orders].Material, [tblOpen Orders].[Short text], [tblOpen Orders].
[Item Date], [tblOpen Orders].[Item deliv], [tblOpen Orders].Quantity,
[tblOpen Orders].[Exc Code], [tblOpen Orders].[Open Qty], [tblOpen
Orders].[GI Date], [tblOpen Orders].HAWB, [tblOpen Orders].[Net
Price], [tblOpen Orders].[Net Value]" & Chr(10)
If Not IsNull(Me.cmbField1) Then
   strWhere = "(([tblOpen Orders].[" & Me.cmbField1 & "])"
End If
guessFieldType (Me.cmbField1)
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator1) Then
   Select Case Me.cmbOperator1
   Case 0
     MsgBox "No operator", vbInformation, "Nothing to do."
   Case 1
     strWhere = strWhere & "="
   Case 2
     strWhere = strWhere & ">"
   Case 3
     strWhere = strWhere & ">="
   Case 4
     strWhere = strWhere & "<"
   Case 5
     strWhere = strWhere & "<="
   Case 6
     strWhere = strWhere & "Is NULL)"
     Me.Filter = strWhere
     Me.FilterOn = True
     Exit Sub
   Case 7
     strWhere = strWhere & "Like"
   Case 8
     strWhere = strWhere & "Not Like"
   End Select
End If
If guessedField = "Num" Then
 'Num field example. Do not add the extra quotes.
 If Not IsNull(Me.txtCriteria1) Then
   strWhere = strWhere & Me.txtCriteria1 & ")"
 End If
ElseIf guessedField = "Date" Then
 'Num field example. Do not add the extra quotes.
 If Not IsNull(Me.txtCriteria1) Then
   strWhere = strWhere & "#" & Me.txtCriteria1 & "#)"
 End If
ElseIf guessedField = "Text" Then
 'Text field example. Use quotes around the value in the string.
 If Not IsNull(Me.txtCriteria1) Then
   strWhere = strWhere & """" & Me.txtCriteria1 & """)"
 End If
End If
'strSQL_Ftr = "HAVING (" & strWhere & " AND(([tblOpen Orders].[Open
Qty])>0));"
strSQL_Ftr = "HAVING (" & strWhere & ");"
strSQL = strSQL & strSQL_Ftr
'Dim rs As New ADODB.Recordset
'rs.Open strSQL, , adOpenStatic, adLockOptimistic
Me.[frmAll_Open_Orders_1].Form.RecordSource = strSQL
End Sub
Any help would be appreciated.  Thanks!
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Originally this is how I had it, and received the error.  So I then
added the Chr(10) to make it look like the SQL view.  If I put in a
break to check the variable strSQL and copy and paste this into a temp
query, the query works.  However, it is the last line where I am
setting the forms recordsource is where I get the error.  The only
other assumption I have is that the form currently does not have a
recordsource set and that could be causing the error.- Hide quoted text -

- Show quoted text -
Click to expand...

OK, my assumption is correct. By setting the recordsource to a
generic query the code above works. Now, my new dilemma is exporting
the displayed data to excel minus the header and footer.
 
N

NeoFax

Chr(10) is a carriage return...AFAIK, those are meaningless in SQL.  Use a
Space instead.  If you want, you can declare a constant SPACE = " "..  If you
want, you can run all the SQL together in one line and it will still execute.
Start there and tell us what happens.
Click to expand...
NeoFax said:
I am receiving the error 2465, "Cannot find "|"..." error.  Here is my
code:
Private Sub setFormSource()
Dim strSQL_Hdr As String
Dim strSQL As String
Dim strSQL_Ftr As String
Dim strWhere As String
strSQL_Hdr = "SELECT [tblOpen Orders].Vendor, [tblOpen Orders].[Vendor
name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item, [tblOpen
Orders].Type, [tblOpen Orders].MRPCn, [tblOpen Orders].Material,
[tblOpen Orders].[Short text], [tblOpen Orders].[Item Date], [tblOpen
Orders].[Item deliv], [tblOpen Orders].Quantity, [tblOpen Orders].[Exc
Code], [tblOpen Orders].[Open Qty], [tblOpen Orders].[GI Date],
[tblOpen Orders].HAWB, IIf(Date()>[Item deliv],(Date()-[Item deliv]),
0) AS [Days Late], [tblOpen Orders].[Net Price], [tblOpen Orders].[Net
Value], ([Net Price]*[Open Qty]) AS [Open Value]" & Chr(10)
strSQL = strSQL_Hdr & "FROM [tblOpen Orders]" & Chr(10)
strSQL = strSQL & "GROUP BY [tblOpen Orders].Vendor, [tblOpen Orders].
[Vendor name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item,
[tblOpen Orders].Type, [tblOpen Orders].MRPCn, [tblOpen
Orders].Material, [tblOpen Orders].[Short text], [tblOpen Orders].
[Item Date], [tblOpen Orders].[Item deliv], [tblOpen Orders].Quantity,
[tblOpen Orders].[Exc Code], [tblOpen Orders].[Open Qty], [tblOpen
Orders].[GI Date], [tblOpen Orders].HAWB, [tblOpen Orders].[Net
Price], [tblOpen Orders].[Net Value]" & Chr(10)
If Not IsNull(Me.cmbField1) Then
   strWhere = "(([tblOpen Orders].[" & Me.cmbField1 & "])"
End If
guessFieldType (Me.cmbField1)
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator1) Then
   Select Case Me.cmbOperator1
   Case 0
     MsgBox "No operator", vbInformation, "Nothing to do."
   Case 1
     strWhere = strWhere & "="
   Case 2
     strWhere = strWhere & ">"
   Case 3
     strWhere = strWhere & ">="
   Case 4
     strWhere = strWhere & "<"
   Case 5
     strWhere = strWhere & "<="
   Case 6
     strWhere = strWhere & "Is NULL)"
     Me.Filter = strWhere
     Me.FilterOn = True
     Exit Sub
   Case 7
     strWhere = strWhere & "Like"
   Case 8
     strWhere = strWhere & "Not Like"
   End Select
End If
If guessedField = "Num" Then
 'Num field example. Do not add the extra quotes.
 If Not IsNull(Me.txtCriteria1) Then
   strWhere = strWhere & Me.txtCriteria1 & ")"
 End If
ElseIf guessedField = "Date" Then
 'Num field example. Do not add the extra quotes.
 If Not IsNull(Me.txtCriteria1) Then
   strWhere = strWhere & "#" & Me.txtCriteria1 & "#)"
 End If
ElseIf guessedField = "Text" Then
 'Text field example. Use quotes around the value in the string.
 If Not IsNull(Me.txtCriteria1) Then
   strWhere = strWhere & """" & Me.txtCriteria1 & """)"
 End If
End If
'strSQL_Ftr = "HAVING (" & strWhere & " AND(([tblOpen Orders].[Open
Qty])>0));"
strSQL_Ftr = "HAVING (" & strWhere & ");"
strSQL = strSQL & strSQL_Ftr
'Dim rs As New ADODB.Recordset
'rs.Open strSQL, , adOpenStatic, adLockOptimistic
Me.[frmAll_Open_Orders_1].Form.RecordSource = strSQL
End Sub
Any help would be appreciated.  Thanks!
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Originally this is how I had it, and received the error.  So I then
added the Chr(10) to make it look like the SQL view.  If I put in a
break to check the variable strSQL and copy and paste this into a temp
query, the query works.  However, it is the last line where I am
setting the forms recordsource is where I get the error.  The only
other assumption I have is that the form currently does not have a
recordsource set and that could be causing the error.- Hide quoted text -

- Show quoted text -
Click to expand...

OK, well I guess I spoke to soon. I am receiving the error again
after adding the remaining 5 criteria. Here is the new code, but
still the same error:

Private Sub Send_to_Excel_Click()
Dim strSQL_Hdr As String
Dim strSQL As String
Dim strSQL_Ftr As String
Dim strWhere As String

strSQL_Hdr = "SELECT [tblOpen Orders].Vendor, [tblOpen Orders].[Vendor
name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item, [tblOpen
Orders].Type, [tblOpen Orders].MRPCn, [tblOpen Orders].Material,
[tblOpen Orders].[Short text], [tblOpen Orders].[Item Date], [tblOpen
Orders].[Item deliv], [tblOpen Orders].Quantity, [tblOpen Orders].[Exc
Code], [tblOpen Orders].[Open Qty], [tblOpen Orders].[GI Date],
[tblOpen Orders].HAWB, IIf(Date()>[Item deliv],(Date()-[Item deliv]),
0) AS [Days Late], [tblOpen Orders].[Net Price], [tblOpen Orders].[Net
Value], ([Net Price]*[Open Qty]) AS [Open Value]" & Chr(10)
strSQL = strSQL_Hdr & "FROM [tblOpen Orders]" & Chr(10)
strSQL = strSQL & "GROUP BY [tblOpen Orders].Vendor, [tblOpen Orders].
[Vendor name], [tblOpen Orders].[Purch doc], [tblOpen Orders].Item,
[tblOpen Orders].Type, [tblOpen Orders].MRPCn, [tblOpen
Orders].Material, [tblOpen Orders].[Short text], [tblOpen Orders].
[Item Date], [tblOpen Orders].[Item deliv], [tblOpen Orders].Quantity,
[tblOpen Orders].[Exc Code], [tblOpen Orders].[Open Qty], [tblOpen
Orders].[GI Date], [tblOpen Orders].HAWB, [tblOpen Orders].[Net
Price], [tblOpen Orders].[Net Value]" & Chr(10)

If Not IsNull(Me.cmbField1) Then
strWhere = strWhere & "(([tblOpen Orders].[" & Me.cmbField1 & "])"
guessFieldType (Me.cmbField1)
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator1) Then
Select Case Me.cmbOperator1
Case 0
MsgBox "No operator", vbInformation, "Nothing to do."
Case 1
strWhere = strWhere & "="
Case 2
strWhere = strWhere & ">"
Case 3
strWhere = strWhere & ">="
Case 4
strWhere = strWhere & "<"
Case 5
strWhere = strWhere & "<="
Case 6
strWhere = strWhere & "Is NULL)"
Me.Filter = strWhere
Me.FilterOn = True
Exit Sub
Case 7
strWhere = strWhere & "Like"
Case 8
strWhere = strWhere & "Not Like"
End Select
End If

If guessedField = "Num" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria1) Then
strWhere = strWhere & Me.txtCriteria1 & ")"
End If
ElseIf guessedField = "Date" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria1) Then
strWhere = strWhere & "#" & Me.txtCriteria1 & "#)"
End If
ElseIf guessedField = "Text" Then
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtCriteria1) Then
strWhere = strWhere & """" & Me.txtCriteria1 & """)"
End If
End If

'*******************************************
'Filter Row 2
'*******************************************

If Not IsNull(Me.cmbField2) Then
strWhere = strWhere & " AND (([tblOpen Orders].[" & Me.cmbField2 &
"])"
guessFieldType (Me.cmbField2)
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator2) Then
Select Case Me.cmbOperator2
Case 0
MsgBox "No operator", vbInformation, "Nothing to do."
Case 1
strWhere = strWhere & "="
Case 2
strWhere = strWhere & ">"
Case 3
strWhere = strWhere & ">="
Case 4
strWhere = strWhere & "<"
Case 5
strWhere = strWhere & "<="
Case 6
strWhere = strWhere & "Is NULL)"
Me.Filter = strWhere
Me.FilterOn = True
Exit Sub
Case 7
strWhere = strWhere & "Like"
Case 8
strWhere = strWhere & "Not Like"
End Select
End If

If guessedField = "Num" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria2) Then
strWhere = strWhere & Me.txtCriteria2 & ")"
End If
ElseIf guessedField = "Date" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria2) Then
strWhere = strWhere & "#" & Me.txtCriteria2 & "#)"
End If
ElseIf guessedField = "Text" Then
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtCriteria2) Then
strWhere = strWhere & """" & Me.txtCriteria2 & """)"
End If
End If


'*******************************************
'Filter Row 3
'*******************************************

If Not IsNull(Me.cmbField3) Then
strWhere = strWhere & " AND (([tblOpen Orders].[" & Me.cmbField3 &
"])"
guessFieldType (Me.cmbField3)
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator3) Then
Select Case Me.cmbOperator3
Case 0
MsgBox "No operator", vbInformation, "Nothing to do."
Case 1
strWhere = strWhere & "="
Case 2
strWhere = strWhere & ">"
Case 3
strWhere = strWhere & ">="
Case 4
strWhere = strWhere & "<"
Case 5
strWhere = strWhere & "<="
Case 6
strWhere = strWhere & "Is NULL)"
Me.Filter = strWhere
Me.FilterOn = True
Exit Sub
Case 7
strWhere = strWhere & "Like"
Case 8
strWhere = strWhere & "Not Like"
End Select
End If

If guessedField = "Num" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria3) Then
strWhere = strWhere & Me.txtCriteria3 & ")"
End If
ElseIf guessedField = "Date" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria3) Then
strWhere = strWhere & "#" & Me.txtCriteria3 & "#)"
End If
ElseIf guessedField = "Text" Then
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtCriteria3) Then
strWhere = strWhere & """" & Me.txtCriteria3 & """)"
End If
End If

'*******************************************
'Filter Row 4
'*******************************************

If Not IsNull(Me.cmbField4) Then
strWhere = strWhere & " AND (([tblOpen Orders].[" & Me.cmbField4 &
"])"
guessFieldType (Me.cmbField4)
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator4) Then
Select Case Me.cmbOperator4
Case 0
MsgBox "No operator", vbInformation, "Nothing to do."
Case 1
strWhere = strWhere & "="
Case 2
strWhere = strWhere & ">"
Case 3
strWhere = strWhere & ">="
Case 4
strWhere = strWhere & "<"
Case 5
strWhere = strWhere & "<="
Case 6
strWhere = strWhere & "Is NULL)"
Me.Filter = strWhere
Me.FilterOn = True
Exit Sub
Case 7
strWhere = strWhere & "Like"
Case 8
strWhere = strWhere & "Not Like"
End Select
End If

If guessedField = "Num" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria4) Then
strWhere = strWhere & Me.txtCriteria4 & ")"
End If
ElseIf guessedField = "Date" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria4) Then
strWhere = strWhere & "#" & Me.txtCriteria4 & "#)"
End If
ElseIf guessedField = "Text" Then
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtCriteria4) Then
strWhere = strWhere & """" & Me.txtCriteria4 & """)"
End If
End If

'*******************************************
'Filter Row 5
'*******************************************

If Not IsNull(Me.cmbField5) Then
strWhere = strWhere & " AND (([tblOpen Orders].[" & Me.cmbField5 &
"])"
guessFieldType (Me.cmbField5)
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator5) Then
Select Case Me.cmbOperator5
Case 0
MsgBox "No operator", vbInformation, "Nothing to do."
Case 1
strWhere = strWhere & "="
Case 2
strWhere = strWhere & ">"
Case 3
strWhere = strWhere & ">="
Case 4
strWhere = strWhere & "<"
Case 5
strWhere = strWhere & "<="
Case 6
strWhere = strWhere & "Is NULL)"
Me.Filter = strWhere
Me.FilterOn = True
Exit Sub
Case 7
strWhere = strWhere & "Like"
Case 8
strWhere = strWhere & "Not Like"
End Select
End If

If guessedField = "Num" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria5) Then
strWhere = strWhere & Me.txtCriteria5 & ")"
End If
ElseIf guessedField = "Date" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria5) Then
strWhere = strWhere & "#" & Me.txtCriteria5 & "#)"
End If
ElseIf guessedField = "Text" Then
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtCriteria5) Then
strWhere = strWhere & """" & Me.txtCriteria5 & """)"
End If
End If

'*******************************************
'Filter Row 6
'*******************************************

If Not IsNull(Me.cmbField6) Then
strWhere = strWhere & " AND (([tblOpen Orders].[" & Me.cmbField6 &
"])"
guessFieldType (Me.cmbField6)
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbOperator6) Then
Select Case Me.cmbOperator6
Case 0
MsgBox "No operator", vbInformation, "Nothing to do."
Case 1
strWhere = strWhere & "="
Case 2
strWhere = strWhere & ">"
Case 3
strWhere = strWhere & ">="
Case 4
strWhere = strWhere & "<"
Case 5
strWhere = strWhere & "<="
Case 6
strWhere = strWhere & "Is NULL)"
Me.Filter = strWhere
Me.FilterOn = True
Exit Sub
Case 7
strWhere = strWhere & "Like"
Case 8
strWhere = strWhere & "Not Like"
End Select
End If

If guessedField = "Num" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria6) Then
strWhere = strWhere & Me.txtCriteria6 & ")"
End If
ElseIf guessedField = "Date" Then
'Num field example. Do not add the extra quotes.
If Not IsNull(Me.txtCriteria6) Then
strWhere = strWhere & "#" & Me.txtCriteria6 & "#)"
End If
ElseIf guessedField = "Text" Then
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtCriteria6) Then
strWhere = strWhere & """" & Me.txtCriteria6 & """)"
End If
End If

strSQL_Ftr = "HAVING (" & strWhere & " AND (([tblOpen Orders].[Open
Qty])>0));"

strSQL = strSQL & strSQL_Ftr

'Me.FilterOn = False
Me.[frmAll_Open_Orders_1].Form.RecordSource = strSQL

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MultiList Select Box Search Form 7
Filter a continuous form using multi-select list box 8
Using a continuous form's filter to generate a report. 1
Query two subforms from 3 combo boxes 3
Listbox execute SQL error 2
Filter Report by Form 2
Compile error: Block If without If 41
My code seems to hate me.... 2

Top