Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too complex query (Access2000-03)

    Happy New Year!
    I bet no one missed me here but anyway here I am because I missed YOU!

    Perfectly worked and now cusses at me says it is too complex to evaluate!

    SELECT Replace([Region],"All ","ZZZ ") AS SortRegion, Replace([Office name],"All ","ZZZ ") AS SortOfficeName, Active_Job_Opening.Region, Active_Job_Opening.[Office name]
    FROM Active_Job_Opening
    WHERE (((Active_Job_Opening.Region)=[Forms]![Recruiting_Reports_Switchboard]![cmb_Region])) OR ((([Forms]![Recruiting_Reports_Switchboard]![cmb_Region]) Is Null))
    ORDER BY Replace([Region],"All ","ZZZ "), Replace([Office name],"All ","ZZZ ");

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too complex query (Access2000-03)

    Try this:

    1) Create a query that calculates SortRegion and SortOfficeName but doesn't sort on them:

    SELECT Replace([Region],"All ","ZZZ ") AS SortRegion, Replace([Office name],"All ","ZZZ ") AS SortOfficeName, Active_Job_Opening.Region, Active_Job_Opening.[Office name]
    FROM Active_Job_Opening
    WHERE (((Active_Job_Opening.Region)=[Forms]![Recruiting_Reports_Switchboard]![cmb_Region])) OR ((([Forms]![Recruiting_Reports_Switchboard]![cmb_Region]) Is Null))

    Save this query.

    2) Create a query based on the query you created in step 1.
    Add all fields to the query grid.
    Specify the sort order on SortRegion and SortOfficeName here.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too complex query (Access2000-03)

    Same message! :-(

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too complex query (Access2000-03)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Too complex query (Access2000-03)

    Edited later
    Sorry, this is nonsense. I misread the original SQL.


    I wonder if the problem is having a table called Replace, when Replace is an Access function.

    Trying renaming the table to tblReplace.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •