Query is too complex - can it be streamlined?

Can I use this query in vba?  At the moment it's too complex! :

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

Incidentally, the second date and territory parameters may be null...

Thanks
Lapchien
LapchienDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shanesuebsahakarnCommented:
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.
0
LapchienDirectorAuthor Commented:
mmm - I would love to do this in code - I guess you could then just capture the first date and last date as variables?
0
shanesuebsahakarnCommented:
You can - but it depends what you want to do with the query. Are you using it as the rowsource of a form/report?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

0
harfangCommented:
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]
;
0
harfangCommented:
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
0
harfangCommented:
Oops, slight correction:

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

Cheers :)
0
harfangCommented:
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 :)
0
LapchienDirectorAuthor Commented:
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
0
shanesuebsahakarnCommented:
> 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.
0
LapchienDirectorAuthor Commented:
harfang - love the code.  how would the code output the data though?
0
harfangCommented:
Something like this:

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

Cheers :)
0
LapchienDirectorAuthor Commented:
Thanks.  Using the above I get an error -

run-time error 3265 item not found in this collection

Any suggestions?

Lap
0
harfangCommented:
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
0
LapchienDirectorAuthor Commented:
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
0
LapchienDirectorAuthor Commented:
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?
0
harfangCommented:
OK, one more time :)

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"
       
    Const cORDERBY = " ORDER BY DT.Territory"
       
    Dim strEnd As String
    Dim strWHERE As String
    Dim strSep As String
    Dim strTerr As String
    Dim strFrom As String
    Dim strError As String
    Dim strDone As String

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

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

End Sub

Cheers!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
Shane: you are a medium... you were right all along :)
0
shanesuebsahakarnCommented:
:-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.