Query using two tables

[nhtraven]

I have a form that i want the users to be able to make a query depending on the information they enter from two different tables. THese tables have a one to one relationship. Using the Parcelno as primary key in table a, and foriegn key in table b.
I cannot get the code right to except both tables in the select statement.

This is the code on the command button to run the query.

Private Sub cmdRunQuery_Click()

Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant


Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query&quot

On Error GoTo 0

where = Null
'Evaluate Criteria entered
'parcel no
If Left(Me![ParcelNo], 1) = "*" Or Right(Me![ParcelNo], 1) = "*" Then

where = where & " AND [tblInputData].[ParcelNo] like ' " + Me![ParcelNo] + "'"

Else
where = where & " AND [tblInputData].[Parcelno] = ' " + Me![ParcelNo] + "'"
End If

'lots more fields in two tables.

'Range Queries

'LotSize range
If Not IsNull(Me![LotSizeE]) Then
where = where & " AND [tblInputData].[LotSize] between " + Me![LotSize] + " AND " & Me![LotSizeE]

Else
where = where & " AND [tblInputData].[LotSize] >= " + Me![LotSize]
End If

'lots more range query from two tables

If tblInputData <> &quot;&quot; Then
Set QD = db.CreateQueryDef(&quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & [tblInputData] & &quot;INNER JOIN&quot; & [tblBldgOver] & where)


Else
Set QD = db.CreateQueryDef(&quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblBldgOver & &quot; &quot; & where)

End If

DoCmd.OpenQuery &quot;Dynamic_Query&quot;

End Sub

Is there ONE select statement that will work for this? SHould i be using an inner join? i am not sure how to get this to work.
thanks
raven

 

[nhtraven]

I still havent gotten this to work but have used this slect statement instead of if statement::

Set QD = db.CreateQueryDef(&quot;Dynamic_Query&quot;, &quot;SELECT * FROM tblInputData, tblBldgOver INNER JOIN tblBldgOver ON [tblInputData].[ParcelNo] = [tblBldgOver].[ParcelNo] WHERE&quot; & strWhere & &quot;;&quot

i have tried a wide variety of select statements all of which dont work, or does not return records.

Any help with this would be appreciated.
Raven

 

[Spellman]

I think the problem is in your where statement. From what I can see when your building your where statement theres always an AND in it but the first clause in your where statement should not be preceeded by And. Hope this ias the problem and helps you out somewhat.

 

[nhtraven]

Thanks spellman,
How do i fix this? should i parse the first and off? or put the and at end of statement, then parse last one off?
i am not sure how to go about fixing this.
THanks
Raven

 

[nhtraven]

OK this is what i have so far for the parse. It kinda works, but doesnt return any records. ANy ideas??? i have a message box displaying the sql statement, and it doesnt have a and at beginning. just where it is suppose to be

Here is parse statement:

Dim IntLength As Integer

If Left(strWhere, 3) = &quot;AND&quot; Then
strWhere = Right(strWhere, IntLength - 3)
End If


Any and all help would be appreciated
raven

 

[Spellman]

In your select statement take out tblbuilding over from the first part

Set QD = db.CreateQueryDef(&quot;Dynamic_Query&quot;, &quot;SELECT * FROM tblInputData, tblBldgOver INNER JOIN tblBldgOver ON [tblInputData].[ParcelNo] = [tblBldgOver].[ParcelNo] WHERE&quot; & strWhere & &quot;;&quot


so make it

Set QD = db.CreateQueryDef(&quot;Dynamic_Query&quot;, &quot;SELECT * FROM tblInputData, INNER JOIN tblBldgOver ON [tblInputData].[ParcelNo] = [tblBldgOver].[ParcelNo] WHERE&quot; & strWhere & &quot;;&quot