Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: Nicer way to do this with SQL instead of VBA?

    Hi guys,

    Just need a bit of advice really, I can fix this through VBA, but as I'm forever trying to increase my knowledge of SQL, then I was wondering if there was a better way to achieve the same results, using SQL, exclusively.

    Currently I have:

    Code:
    strMLSQL = "SELECT ref_products.[Product vName] AS [Product Description], " & _
               "COUNT(1) AS [Quantity] " & _
               "FROM main LEFT JOIN ref_products " & _
                   "ON ref_products.[Product Code] = main.[Product Code] " & _
               "WHERE [Order Number] = " & duplicateONum(pageNum) & " " & _
               "AND main.[Delete] = FALSE " & _
               "GROUP BY [Product vName]"
    
    Me.mainList.RowSource = strMLSQL
    Which basically finds all the Product Descriptions ([Product vName]) for a given order, counting them, and populating a two column listbox with its user friendly description in the first, and the number of that product listed for that order in the second.

    This does, for the most part, exactly what I want... Except, for the rare occasions that a [Product vName] doesn't exist for the given [Product Code].

    What I'd really like to happen, is that if [Product vName] is NULL, then write its [Product Code] instead, or simply "Unknown Product Description" - or something similar.

    I can do this with VBA, but its a bit long winded, and as I said, I'm always looking to learn more. So can this be done purely with SQL? And if so, can anyone provide an example or some text to read up on?


    Thanks guys!
    Looking for the perfect beer...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Though it's not a "true" SQL function (CASE...WHEN... does not exist in SQL Access), you could use:
    Code:
    "SELECT Nz(ref_products.[Product vName], ref_products.[Product Code]) AS [Product Description]...
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Ahh, I grew impatient and decided to spend an hour researching and found that I could use an IIF statement in SQL. Along with an ISNULL (which is supported by both Access and MS SQL, as opposed to IFNULL for mySQL), to check it out.

    I did encounter one problem though, in that it wouldn't allow me to write the [Product Code] in its place, using a 'Test', or even 'Test' + '2' would work fine, but simply using attempting to recover its [Product Code] wouldn't work...

    I'm at home now (finished for another day, hurrah!), and had to resolve some networking conflicts that were occurring towards the end of my shift, but I'll have a look at it tomorrow when I get a spare hour and try and resolve it.

    I also found that if two different [Product vName]s couldn't be found for the same order, that it would group them together. I believe I can resolve that by altering the GROUP BY statement though, but as I said, until I'm at my desk tomorrow, its difficult to know.

    I'll be sure to update this thread as I make (or don't make) progress.


    Thanks for the input Sinn.
    Looking for the perfect beer...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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