Single query, multiple table..........?!

[bapprill]

Hello,
I need some help with adding to an existing database. Our database is searchable by two basic criteria, Projects or Units. These are two seperate tables, each with their respective variables. The search can run according to some variables, but not all (ie. size, but not date). I wish to add the ability to search by date. Below is the code for the Units search so far.....

Private Sub Command17_Click()
Dim strSQL As String
strSQL = "SELECT * FROM Units WHERE "
strSQL = strSQL & "SquareFootage BETWEEN " & SqFootMin & " AND " & SqFootMax
strSQL = strSQL & &quot; AND Width < &quot; & Me.WidthMax
strSQL = strSQL & &quot; AND Depth < &quot; & Me.DepthMax

If Me.Floors > &quot;&quot; Then
strSQL = strSQL & &quot; AND Floors = &quot; & Me.Floors
End If

If Me.Master > &quot;&quot; Then
strSQL = strSQL & &quot; AND Master = &quot; & Me.Master
End If

If Me.Baths > &quot;&quot; Then
strSQL = strSQL & &quot; AND Baths = &quot; & Me.Baths
End If

If Me.Bedrooms > &quot;&quot; Then
strSQL = strSQL & &quot; AND Bedrooms = &quot; & Me.Bedrooms
End If

If Me.GarageStalls > &quot;&quot; Then
strSQL = strSQL & &quot; AND GarageStalls = &quot; & Me.GarageStalls
End If

If Me.UnitDescription > &quot;&quot; Then
strSQL = strSQL & &quot; AND UnitDescription LIKE &quot; & Me.UnitDescription
End If

DoCmd.OpenForm &quot;Unit Results&quot;, acFormDS
'MsgBox strSQL
Forms![Unit Results].RecordSource = strSQL

End Sub

Any help would be GREAT.
Thank you

 

[Darrylles]

Hi Bappril,

Try this....

Open up the query in the query builder and add the date field to the query from whichever of the 2 tables it resides in.

In your code, before the DoCmd.Openform statement add this line:

[tt]
strSQL = strSQL & &quot; AND mydate BETWEEN [Start date] AND [End date]&quot;
[/tt]

Where 'mydate' is the name of the date field that you added to the query.

This will prompt the user for a start date and then an end date to add to the criteria - if you only want a particular day then add the same date to both date prompts.

If you definitely only ever want a particular date then insert this statement instead...

[tt]
strSQL = strSQL & &quot; AND mydate = [Date]&quot;
[/tt]

Hope it helps.

Regards,

Darrylle &quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot;