multiple parameter query statement in the same query Access 2k

[MLHab306]

Hi

I think this is a simple question but I cant find the answer in help.

What I am trying to do is:

Use a parmater query to first sort for the date range then enter in a paramter query if a particluar client is not in the date range.

date range: 1/20 to 1/26
who is not in date range: smith

That works fine what I want is
date range: 1/20 to 1/26
who is not in date range: smith
who is not in date range: jones
who is not in date range: brown

attached is the sql code. I am clueless when it comes to the code I have been using the wizards to get this far, so please, if it is in sql please suggested where to put your suggestions.

Thank you

Mark




SELECT Date_last_done.Date_last_done, Customers.LastName, Date_last_done.CheckNumber, Date_last_done.ABA_number, Date_last_done.Check_Total
FROM Customers INNER JOIN Date_last_done ON Customers.CustomerID = Date_last_done.Customer_ID
WHERE (((Date_last_done.Date_last_done) Between [start date] And [end date]) AND ((Date_last_done.Payment_type)=1)) OR (((Date_last_done.Date_last_done)>#1/1/2003#) AND ((Customers.LastName)=[Which other customer not in Date Range?]) AND ((Date_last_done.Payment_type)=1))
ORDER BY Date_last_done.Date_last_done;

 

[FancyPrairie]

I'm kind of comfused at what you're trying to do. But if I understand you correctly, try something like this:

In the query design view, create a query that returns the first set of results (date range). Then create a new query and when it asks you to select a table, select your first query, select the items you want shown in the 2nd query and build your Criteria for the 2nd query. Sounds confusing. But once you select your 1st query rather than a table, I think a light will come on. Again, if I understand you problem correctly.

 

[MLHab306]

Thank you for responding so quickly.

What I am trying to do is a deposit slip for a small business based on a database that I already created. Sometimes clients send me checks several weeks after the appointment.

When I do a deposit slip I can tell it do a certain range for example 1/20 to 1/26. But if the client or clients was done before the requested date range, I have to do a seperated deposit slip for each check (not in the range). I am able to add 1 "who is not in the requested date range " paramter query. What I would like to be able to do is add 3 or 4 "who is not in the requested range" parameter queries.

So I was looking for a way to add additional client checks to the deposit slip thru multiple paramter query input. To be printed out in addition to the requested date range on 1 deposit slip.

I hope this makes more sense now.


Mark

 

[FancyPrairie]

I'm not sure you can do what you want with a query. However, you could do it programmatically. There are several ways you could do it. One is to create a form where you enter the names. Another way is to use a simple Inputbox. Whichever way you choose you will have to build the SQL statement programmatically and then save it to a query. This is how you do it programmatically. Note that you will need to set a Reference to "Microsoft DAO 3.6 Object Library" via TOOLS|REFERENCES...

Function BuildSQLstring()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Dim strSQL As String
    Dim strWhere As String
    Dim strName As String
    
    strWhere = vbNullString
    strName = vbNullString
    
    strSQL = "PARAMETERS [start date] DateTime, [end date] DateTime;" & vbCrLf
    strSQL = strSQL & "SELECT Date_last_done.Date_last_done, Customers.LastName, Date_last_done.CheckNumber, Date_last_done.ABA_number, Date_last_done.Check_Total "
    strSQL = strSQL & "FROM Customers INNER JOIN Date_last_done ON Customers.CustomerID = Date_last_done.Customer_ID "
    strSQL = strSQL & "WHERE (((Date_last_done.Date_last_done) Between [start date] And [end date]) AND ((Date_last_done.Payment_type)=1)) "
    
10:
    strName = InputBox("Which other customer not in Date Range?")
    
    If (strName <> vbNullString) Then
        strWhere = strWhere & &quot;(((Date_last_done.Date_last_done)>#1/1/2003#) AND ((Customers.LastName)='&quot; & strName & &quot;') AND ((Date_last_done.Payment_type)=1)) OR &quot;
        GoTo 10
    End If
    
    If (strWhere <> vbNullString) Then
        strSQL = strSQL & Left$(strWhere, Len(strWhere) - 3)
        Set dbs = CurrentDb
        dbs.QueryDefs.Delete &quot;YourQueryName&quot;                      'Delete Query
        Set qdf = dbs.CreateQueryDef(&quot;YourQueryName&quot;, strSQL)  'Create Query based on contents of &quot;strSQL&quot;
        strSQL = vbNullString
    End If
    
'***********************
'*  Exit Subprocedure  *
'***********************

ExitProcedure:
    
    Exit Function
    
'****************************
'*  Error Recovery Section  *
'****************************

ErrHandler:

    If (Err.number = 3265) Then         'IFT, tryed to delete a query that did not exist.
        
        Resume Next

    Else
        
        MsgBox Err.number & vbCrLf & Err.Description
        Resume ExitProcedure
    
    End If

End Function