Query is too complex - can it be streamlined?

You can streamline it somewhat like this:

WHERE [T-cards].[Date/time call receieved]<=[Enter End Date]
AND dbo_Territory.Territory In ([Enter first Territory, like Terrxxx],[Enter second Territory, like Terrxxx], [Enter third Territory, like Terrxxx], [Enter fourth Territory, like Terrxxx])
AND (
   [Date/time call received]>=[Enter Start Date (first territory)] OR
   [Date/time call received]>=[Enter Start Date (second territory)] OR
   [Date/time call received]>=[Enter Start Date (third territory)] OR
   [Date/time call received]>=[Enter Start Date (fourth territory)])

I think that's the same your existing query. However, are you getting the query is too complex error? If it works as a standalone query, you should be able to use it in VBA. The problem probably comes from the other parameters being null.

mmm - I would love to do this in code - I guess you could then just capture the first date and last date as variables?

You can - but it depends what you want to do with the query. Are you using it as the rowsource of a form/report?

No, just a straight query.
 
The user enters specific territories and dates and then just pastes them into excel to do pivots or whatever.

For a start, make it more readable :)

PARAMETERS
  [Enter first Territory, like Terrxxx] Text,
  [Enter Start Date (first territory)] Date,
  [Enter second Territory, like Terrxxx] Text,
  [Enter Start Date (second territory)] Date,
  [Enter third Territory, like Terrxxx] Text,
  [Enter Start Date (third territory)] Date,
  [Enter fourth Territory, like Terrxxx] Text,
  [Enter Start Date (fourth territory)] Date,
  [Enter End Date] Date
;
SELECT
 TC.[Date/time call received],
 TC.[Date/time job required],
 TC.[Call type],
 TC.Trade,
 TC.[Sales outcome],
 TC.[End activity date/time],
 TC.[Gross value],
 TC.[Turned-round],
 TC.CancReason,
 DT.PostSector,
 DT.Territory
FROM [T-cards] AS TC
 INNER JOIN dbo_Territories
 ON Left$(TC.[Postcode], InStr(TC.[Postcode]," ") + 1) = DT.PostSector
WHERE
 TC.[Date/time call received]
   Between [Enter Start Date (first territory)] And [Enter End Date]
 AND DT.Territory = [Enter first Territory, like Terrxxx])

 OR TC.[Date/time call received]
   Between [Enter Start Date (second territory)] And [Enter End Date]
 AND DT.Territory = [Enter second Territory, like Terrxxx]

 OR TC.[Date/time call received]
   Between [Enter Start Date (third territory)] And [Enter End Date]
 AND DT.Territory = [Enter third Territory, like Terrxxx]

 OR TC.[Date/time call received]
  Between [Enter Start Date (fourth territory)] And [Enter End Date]
 AND DT.Territory = [Enter fourth Territory, like Terrxxx]
;

Shane: you logic is wrong. The dates are linked to territories. In your version, only the earliest date entered is used, for the four territories...

The CDate() function can be replaced by parameters. The advantages are:
* dates can be entered in many ways ("1 jan", "oct 2003", "1-13-02", etc)
* entry is validated immediately and converted to date (the user can retype a wrong date)
* parameters are asked in a predefined order

Basically, that's it.

Good Luck

Oops, slight correction:

FROM [T-cards] AS TC
 INNER JOIN dbo_Territories AS DT    <--- forgot the DT alias :(

Cheers :)

Could not resist :)
>  - I would love to do this in code -

Sub BuildQuery()

    Const cSELECT = "SELECT" _
        & "  TC.[Date/time call received]," _
        & "  TC.[Date/time job required]," _
        & "  TC.[Call type]," _
        & "  TC.Trade," _
        & "  TC.[Sales outcome]," _
        & "  TC.[End activity date/time]," _
        & "  TC.[Gross value]," _
        & "  TC.[Turned-round]," _
        & "  TC.CancReason," _
        & "  DT.PostSector," _
        & "  DT.Territory" _
        & " FROM [T-cards] AS TC" _
        & " INNER JOIN dbo_Territories AS DT" _
        & " ON Left$(TC.[Postcode], InStr(TC.[Postcode],' ') + 1) = DT.PostSector"
       
    Dim strEnd As String
    Dim strWHERE As String
    Dim strOR As String
    Dim strTerr As String
    Dim strFrom As String
    Dim strError As String
    Dim strDone As String

Retry_EndDate:
    strEnd = InputBox("Enter end Date:" & strError)
    If strEnd = "" Then Exit Sub
    If Not IsDate(strEnd) Then
        strError = vbCrLf & "Invalid: " & strEnd
        GoTo Retry_EndDate
    End If
    strEnd = Format(strEnd, "\#m\/d\/yyyy\#")
   
    strOR = " WHERE "
    Do
        strTerr = InputBox("Enter Territory:" & vbCrLf & "(like Terrxxx)" & strDone)
        If strTerr = "" Then Exit Do
        strTerr = "'" & Replace(strTerr, "'", "''") & "'"
        strError = ""
Retry_FromDate:
        strFrom = InputBox("Enter Start Date:" & strError)
        If strFrom = "" Then Exit Do
        If Not IsDate(strFrom) Then
            strError = vbCrLf & "Invalid: " & strFrom
            GoTo Retry_FromDate
        End If
        strWHERE = strWHERE & strOR & "TC.[Date/time call received]" _
            & " Between " & Format(strFrom, "\#m\/d\/yyyy\#") _
            & " And " & strEnd _
            & " AND DT.Territory = " & strTerr
        strOR = " OR "
        strDone = vbCrLf & "Or press [Enter] if done"
    Loop

    If strWHERE = "" Then Exit Sub
    MsgBox cSELECT & strWHERE

End Sub


I stop at the MsbBox as the rest needs to be tested in your database...

Cheers :)

The territories might change each time - the start date and end date however will be constant for each territory.  That should make it easier in code?

harfang - way to go!

Lap

> Shane: you logic is wrong.

I thought it might be :) I didn't look too closely at the bracketing to be quite honest.

However, I would say, given that multiple criteria can be entered - that is to say, one or more territories might be selected, a joining table might make the whole thing much easier. In other words, you'd have a temporary table to enter your required territories and start/end dates and perform a cartesian join on this and the primary tables. This would also give you the added flexibility of being able to enter as many territories as you want within one query. It would need a bit more logic in the form though.

harfang - love the code.  how would the code output the data though?

Something like this:

    CurrentDb.QueryDefs("qselMyQuery").SQL = cSELECT & strWHERE
    DoCmd.OpenQuery "qselMyQuery"

Cheers :)

Thanks.  Using the above I get an error -

run-time error 3265 item not found in this collection

Any suggestions?

Lap

Sorry, I should have mentioned that the query needs to exist before the command is run.
You can also go:

    CurrentDb.CreateQuery("qselMyQuery").SQL = cSELECT & strWHERE

But this works only once. The second time, the query exists and you have another error message :)

Good Luck

Thanks harfang - the query runs very quickly and is excellent.  Just one last thing though (!) - I'd like the Territories to be sorted ascending.  Can you amend the code 'n' query to reflect that?

Ta

Sorry... also, the end date is constant throughout all territories.  I have to enter a start date for each new territory - but the start date is also constant, for all territories.  do i have to enter the start date for every territory, or can i just enter it once, like the end date?

Shane: you are a medium... you were right all along :)

:-)