Need Help with Query by Form Code (1 Viewer)

[kamil]

Working with this page:
http://support.microsoft.com/defaul...b;EN-US;q210242

I have modified it to use the OR statement so any criteria matched is outputted:

Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()

' Delete the existing dynamic query; trap the error if the query does
' not exist.
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

' Note the single quotation marks surrounding the text fields [Ship
' Country] and [CustomerID].
' Note that there are no type-casting characters surrounding the
' numeric field [EmployeeID].

where = Null
where = where & " AND [ShipCountry]= '" + Me![Ship Country] + "'"
where = where & " AND [CustomerID]= '" + Me![Customer Id] + "'"
where = where & " AND [EmployeeID]= " + Me![Employee Id]

'NOTE: In Microsoft Access, when you use the plus sign (+) in an
'expression in which you are concatenating a variable of the numeric
'data type, you must use parenthesis around the syntax, as in the
'following example:
'
'  where = where & (" AND [EmployeeID]= " + Me![Employee Id])
'
'You must also use a conversion function to make sure that the proper
'conversion (to either NULL or String) takes place.

' The following section evaluates the ShipCity criteria you enter.
' If the first or last character of the criteria is the wildcard
' character (*), then the function uses the "LIKE" operator in the
' SQL statement instead of "=". Also note the single quotation
' marks surrounding the text field [ShipCity].
If Left(Me![Ship City], 1) = "*" Or Right(Me![Ship City], 1) = "*" Then
   where = where & " OR [ShipCity] like '" + Me![Ship City] + "'"
Else
   where = where & " OR [ShipCity] = '" + Me![Ship City] + "'"
End If

' Note the number signs (#) surrounding the date field [Order Date].
If Not IsNull(Me![Order End Date]) Then
   where = where & " OR [OrderDate] between #" + Me![Order Start Date] + "# AND #" & Me![Order End Date] & "#"
Else
   where = where & " OR [OrderDate] >= #" + Me![Order Start Date] + " #"
End If

' Remove the following MsgBox line if you do not want to display the
' SQL statement.
' NOTE: The Mid function is used in the following MsgBox function to
' remove the word AND that follows the first Where clause. If you do
' not use the Mid function, the SQL statement contains the word AND
' at the beginning of the WHERE clause, for example:
'
'   Select * from Orders where AND [CustomerID] = 'CACTU'
MsgBox "Select * from Orders " & (" where " + Mid(where, 5) & ";")
Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from orders " & (" where " + Mid(where, 5) & ";"))
DoCmd.OpenQuery "Dynamic_Query"

End Sub

This code however does not cover everything I need the query to do.

For one, I need to have the form search more than the table its based on, I have over 60 tables that need to be searched.

Second, when the tables are searched for the criteria I need to have the query return the results "by table" so I know where the results came from (which table).

Anyone kind enough to take a stab at it?

This originally started in the general discussion forum here.

 

[kamil]

anybody? TTT

 

[cogent1]

As I replied in my last answer to you, more information is needed about the structure of your database and the fields you want to search for matches. I am not sure how the code you posted applies to your situation. It might not be appropriate at all.