SQL Query Problem

[ViRi]

Hi

I have to output records on a report based on user inputs.

I have written the code below that populates a table and then creates a query. I was hoping then to base a report on the query created and run it within the same sub, but I get an error 'expression is too complex...'

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click

    
    Dim db As DAO.Database
    Dim rsLookUpClient As DAO.QueryDef
    Dim strSQL As String
    Dim Client As String
    Dim DateStart As Date
    Dim DateEnd As Date
    Dim stDocNameDel As String
    Dim stDocNameCr As String
    Dim stDocNameInv As String
    
    
    Set db = CurrentDb
    stDocNameDel = "qryDel"
    stDocNameCr = "qryCrdS"
    stDocNameInv = "qryInvS"
    
    DoCmd.OpenQuery stDocNameDel, acNormal, acEdit
    DoCmd.OpenQuery stDocNameCr, acNormal, acEdit
    DoCmd.OpenQuery stDocNameInv, acNormal, acEdit
        
    Client = InputBox("Enter a Client")
    DateStart = InputBox("Enter Statement Start Date")
    DateEnd = InputBox("Enter Statement End Date")
    strSQL = "SELECT tblStatement.DateI, tblStatement.InvoiceNo, tblStatement.SumOfTo, tblStatement.DateC, tblStatement.CreditNo, tblStatement.SumOfT, tblStatement.ClientName  FROM tblStatement WHERE tblStatement.DateI = DateStart OR tblStatement.DateI = DateEnd & tblStatement.DateC = DateStart OR tblStatement.DateC = DateEnd & tblStatement.ClientName = Client;"
    Set rsLookUpClient = db.CreateQueryDef("qyStatement", strSQL)
    
    
Exit_Command23_Click:
    Exit Sub

Err_Command23_Click:
    MsgBox Err.Description
    Resume Exit_Command23_Click
    
End Sub

ViRi

 

[raskew]

Hi -

I'm sure this is not the total solution, but the first thing to do in the strSQL is to replace the two ampersands (&) with AND. You'll end up with something like this. Try running this as a query to see if it'll properly pick-up your parameters:

SELECT
    tblStatement.DateI
  , tblStatement.InvoiceNo
  , tblStatement.SumOfTo
  , tblStatement.DateC
  , tblStatement.CreditNo
  , tblStatement.SumOfT
  , tblStatement.ClientName
FROM
   tblStatement
WHERE
   ((([tblStatement].[DateI])=[DateStart])) 
OR
   ((([tblStatement].[DateI])=[DateEnd]) 
AND
   (([tblStatement].[DateC])=[DateStart])) 
OR
   ((([tblStatement].[DateC])=[DateEnd]) 
AND
   (([tblStatement].[ClientName])=[Client]));

Not sure why you're opening the three queries since they don't seem to play any part in subsequent code.

Please post back with your results.

Bob

 

[ViRi]

Hi

Thanks for your interest it certainly fixed the Expression too complex error.

But now when I run the Query created it has a problem with the ClientName in the sense that I only input one clientname in the input box and the query result outputs the correct name and its records plus other name from the list it feels like.

ViRi

 

[raskew]

ViRi-

Glad some progress was made. This could be a whole lot easier if you'd post a small sample of your data.

Bob

 

[ViRi]

Hi

What do you mean post a copy of the Table if so I don't know how to do this.

The all data is contained within tblStatement the fields are those you saw in the SQL statement. The tblStatement at the moment contains 6 records, the ClientName field contains 3 names ie 2 records under each name so should the query created not filter out the names other than the one held by the variable Client as set out in the SQL Statement?

ViRi

 

[raskew]

Here's a cut-down version that may be of assistance. Since you're not providing a sample of your data, don't know how DateI and DateC interact. Nonetheless, this does provide results so perhaps you can take it from here.

SELECT
    tblStatement.DateI
  , tblStatement.InvoiceNo
  , tblStatement.ClientName
FROM
   tblStatement
WHERE
   (((tblStatement.DateI)=[DateStart]) 
AND
   ((tblStatement.ClientName)=[Client])) 
OR
   (((tblStatement.DateI)=[DateEnd]) 
AND
   ((tblStatement.ClientName)=[Client]) 
AND
   (([tblStatement].[DateC])=[DateStart])) 
OR
   (((tblStatement.ClientName)=[Client]) 
AND
   (([tblStatement].[DateC])=[DateEnd]));

HTH - Bob

 

[ViRi]

Hi Bob

I don't know what you mean by a sample of my data? is it the Tables the Queries or both?

I'll give the scenario anyhow

The DateC and DateI just refer to the Date of a credit and the Date of an Invoice on two separate queries that outputs all the credits and their values and all the invoices plus their values. I used this results and appended them to tblStatement as you can see below

1. DoCmd.OpenQuery stDocNameDel, acNormal, acEdit is a Delete Query that deletes the Table tblStatement prior to appending new records
2. DoCmd.OpenQuery stDocNameCr, acNormal, acEdit is an Append Query that appends the credit records to tblStatement
3. DoCmd.OpenQuery stDocNameInv, acNormal, acEdit is an Append Query that appends the invoice records to tblStatement
4. At this point I have tblStatement containing all the records that I need.
5. Client = InputBox("Enter a Client")
DateStart = InputBox("Enter Statement Start Date")
DateEnd = InputBox("Enter Statement End Date")
are the variables that hold the user choice
6. After evaluating the SQL statement I need the results to output on a Report.

What I need is a statement report to output all credits and invoices for a given client for a given period of time as per the SQL statement.

ViRi

 

[raskew]

It's the tables! It'd be helpful to see what you're attempting to query. By the way, you haven't (with your code) created a new table--a new query, yes, but not a new table.

Bob

 

[ViRi]

Hi Bob

I'll post the Tables this afternoon as I'm late for work as it is.

tblStatement table is blank (contains no records) the first time round then subsequent runs will firstly delete all records with the Delete Query and then I use an append Query to populate the Table, prior to filtering with the new query created.

ViRi

 

[raskew]

Did you try the cut-down version from post #6 against tblStatement after it's been populated. Using a dummy version of your table as I understand it, the query returned the desired results.

Bob

 

[ViRi]

Hi

I'm going to try the cut down version today. I'll post this afternoon with the results.

This thing is driving me bananas!

ViRi