Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    28

    Unanswered: Query doesn't order once Where Clause is added

    Hi. I have an SQL problem in Access, so I'm not sure if this is an issue with SQL itself, or the Access version of SQL.

    In my BM_Maps subform, there is a combo box called called Borders. I want the borders to be filtered based on product series, so if the user picks "City Guide", they only get city guide borders, so I have added a table called Product_Series_X_Border which has the product series and border id.

    I have a query which joins two tables, and I've ordered it by the border name, which works fine, until I add a where clause for the product series from the other table. I have attached a screengrab which I hope helps. I have tried joining other tables but it still doesn't work. Is there any other way I can order by border name with the where clause? I've tested with individual product series as the where clause with the same result.

    Thanks
    Attached Thumbnails Attached Thumbnails Screen shot 2012-08-31 at 9.36.58 PM.png  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So when you add your where clause do you also add an order by clause?
    Can we see your SQL?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2012
    Posts
    28
    Hi. Here is the SQL.

    Code:
    SELECT DISTINCT dbo_BM_Borders.BORDER_ID, dbo_BM_Borders.BORDER_NAME, dbo_PRODUCTSERIES_X_BORDER.ProductSeries
    FROM dbo_BM_Borders INNER JOIN dbo_PRODUCTSERIES_X_BORDER ON dbo_BM_Borders.BLING_ID = dbo_PRODUCTSERIES_X_BORDER.BorderID
    WHERE (((dbo_PRODUCTSERIES_X_BORDER.ProductSeries)=[Forms]![BM_Product]![Prod_Series]))
    ORDER BY dbo_BM_Borders.BORDER_NAME;
    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok that should work fine.
    Can we now see the actual Sql before you apply the where clause and after.
    Ie the sql that works and the sql that Doesnt
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2012
    Posts
    28
    Do you mean the results or the queries?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You are reporting that after applying a 'where' clause your sql no longer orders the result set.
    So id like to see the sql tha works and the sql that doesnt work. Ideally id like to see aswell the chunk of vba code that applies the where clause.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2012
    Posts
    28
    The code that does work is

    Code:
    SELECT DISTINCT dbo_BM_Borders.BORDER_ID, dbo_BM_Borders.BORDER_NAME,
          dbo_PRODUCTSERIES_X_BORDER.ProductSeries
    FROM dbo_BM_Borders INNER JOIN dbo_PRODUCTSERIES_X_BORDER ON
          dbo_BM_Borders.BLING_ID = dbo_PRODUCTSERIES_X_BORDER.BorderID
    ORDER BY dbo_BM_Borders.BORDER_NAME;
    which is just the previous query without the where clause. The current where clause is looking at the product series of the main form [Forms]![BM_Product]![Prod_Series]. I tested the where clause with individual values such as 'City Guide' and still had the same problem.

  8. #8
    Join Date
    Mar 2012
    Posts
    28
    OK I got it working by joining two other tables, which I tried before but I needed to move and alter the where clause.
    Attached Thumbnails Attached Thumbnails screengrab.JPG  

Tags for this Thread

Posting Permissions

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