Help with Query By Form to produce Reports

[Roe]

I'm having difficulty in knowing where to start.

I want to design a query by form to selecting several or some items from Combo box's: Client, JobID, Ops Controller, Nominal Code and Sales (all from seperate list tables),

and use toggle boxs for the Status selection which needs to include a toggle box for "ALL"

Any help would be greatly received, I have tried to read up on this but find myself punch drunk and still none the wiser.
:-(
Roe

 

[BryanHughes]

How can I run a query using two tables and insure I don't get duplicated addresses

 

[rafe]

Bryan,

Without particulars it's kinda hard to tell just yet but you should look into "Left & Right Joins" and "Select Distinct". If you post details I'll try & help more. Sorry.


Roe,

Sky's the limit here. Translation = there are many way to do this just pick the one that works for you. What I did, one method among many, was:

1) Created a "Blank Query" that got all records my form could possibly want.

2) Made this my record source for the form

c) Did a string build up of the filter clause for the form & then requery the form.

Here is the code that I use (edited somewhat)...

Option Compare Database
Option Explicit

Private Sub Form_Load()
    Me.PayeeID = Null
    Me.Function = Null
    Me.Index = Null
    filterHist
End Sub

Private Sub Function_AfterUpdate()
    filterHist
End Sub

Private Sub PayeeID_AfterUpdate()
    filterHist
End Sub

Private Sub Index_AfterUpdate()
    filterHist
End Sub

Private Sub Account_AfterUpdate()
    filterHist
End Sub

Private Sub FromDate_AfterUpdate()
    filterHist
End Sub

Private Sub ToDate_AfterUpdate()
    filterHist
End Sub

Private Sub filterHist()
    Dim filterStr As String
    
    filterStr = ""
    If Nz(Me.PayeeID, 0) Then filterStr = filterStr & "[PayeeID]=" & Me.PayeeID
    If Nz(Me.Function, &quot;&quot;) <> &quot;&quot; Then
        If filterStr <> &quot;&quot; Then filterStr = filterStr & &quot; AND &quot;
        filterStr = filterStr & &quot;[Func]='&quot; & Me.Function.Column(2) & &quot;'&quot;
    End If
    If Nz(Me.Index, 0) Then
        If filterStr <> &quot;&quot; Then filterStr = filterStr & &quot; AND &quot;
        filterStr = filterStr & &quot;[Index]=&quot; & Me.Index
    End If
    If Nz(Me.Account, 0) Then
        If filterStr <> &quot;&quot; Then filterStr = filterStr & &quot; AND &quot;
        filterStr = filterStr & &quot;[Account]=&quot; & Me.Account
    End If
    If Nz(Me.FromDate, 0) Then
        If filterStr <> &quot;&quot; Then filterStr = filterStr & &quot; AND &quot;
        filterStr = filterStr & &quot;[FromDate]>= #&quot; & Me.FromDate & &quot;#&quot;
    End If
    If Nz(Me.ToDate, 0) Then
        If filterStr <> &quot;&quot; Then filterStr = filterStr & &quot; AND &quot;
        filterStr = filterStr & &quot;[ToDate]<= #&quot; & Me.ToDate & &quot;#&quot;
    End If

    Me.Filter = filterStr
    Me.FilterOn = True
End Sub

This isn't the zenith of Access coding but it gets the job done. Hope this helps.

rafe