Hi lightning
i am making a form that has all fields from two tables on it that will allow user to query on multiple criterias. I am using a command button to activate the query. Here is a cut down version of the code::
Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Dim tblname1 As String
Dim tblname2 As String
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query"
On Error GoTo 0
where = Null
tblname1 = tblname1
tblname2 = tblname2
'Evaluate Criteria entered
'parcel no <data type is Text >
If Left(Me![ParcelNo], 1) = "*" Or Right(Me![ParcelNo], 1) = "*" Then
where = where & " AND [tblname1].[ParcelNo] like ' " + Me![ParcelNo] + "'"
Else
where = where & " AND [tblname1].[Parcelno] = ' " + Me![ParcelNo] + "'"
End If
'Range Queries
'LotSize range
If Not IsNull(Me![LotSizeE]) Then
where = where & " AND [tblname1].[LotSize] between " + Me![LotSize] + " AND " & Me![LotSizeE]
Else
where = where & " AND [tblname1].[LotSize] >= " + Me![LotSize]
End If
If tblname1 <> "" Then
Set QD = ("Dynamic_Query" "SELECT * FROM "& tblname1 & " " & where)
Else
Set QD = ("Dynamic_Query", "SELECT * FROM " & tblname1 & " " & where)
End If
DoCmd.OpenQuery "Dynamic_Query"
End Sub
I am not sure how to accomplish this. any ideas are very much appreicated
Raven