Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Jan 22, 2025
Solved

Run a Query without Saving it (to reduce # objects)

Hello Experts,   I want to reduce the # of objects I have in my db. One way I can do this is by not saving queries and running the select statement from a button on my form with code instead. I h...
access
  • George_Hepworth's avatar
    George_Hepworth
    Jan 22, 2025

    Okay, so "Like" works, as would other approaches, when working with embedded digits in a larger number. 

    Yes, I missed out on removing the left square bracket. 

     

    To return to the SQL Syntax then ExcelBOA.[Account Number] is a number, not a string of  digits in a text field.

     

    Private Sub cmButtonNameGoesHere_Click()

Dim strSQL As String

strSQL = "SELECT import_Excel_BOA.[As of Date], import_Excel_BOA.Amount," & _
        " import_Excel_BOA.[Account Number], " & _
        " import_Excel_BOA.[Account Name], import_Excel_BOA.Text " & _
        " FROM import_Excel_BOA " & _
        " WHERE (import_Excel_BOA.[Account Number] Like ""*"" & [Enter 5213 for VW1 or 5636 for VWMC] & ""*"")" & _
        " AND (import_Excel_BOA.Text Like ""*"" & [enter part of co name] & ""*"") ;"

Me.ListBoxNameGoesHere.RowSource = strSQL

End Sub

    That's as close as I can get working without data to validate against. I think I got all of the open and close parentheses in the right places, the square brackets where they are needed, and the quotes and double quotes in the right places and numbers. 

     

Tony2021's avatar
Tony2021
Iron Contributor
Jan 22, 2025

HI George, I added a listbox but I am getting the folllowing error.  Let me know what the next step is when you have a sec.  

 

George_Hepworth's avatar
George_Hepworth
Silver Contributor
Jan 22, 2025

One of the values in the where clause is wrong. I copied the SQL posted, without validation.

 

My best guess is the Account Number is the problem. You used Like so I copied that, but it's more likely that the Account Number is a number, and you need to use = not Like with numbers. 

Try this for the line in the original


" WHERE import_ExcelBOA.[Account Number = " & [Enter 5213 for VW1 or 5636 for VWMC " &

 

On the other hand, it's possible I guess, that 5213 and 5636 are part of a larger string of digits. In that case, we'd need to know that detail to know how to tweak the parameter.

 

  • Tony2021's avatar
    Tony2021
    Iron Contributor
    Jan 22, 2025

    Hi George, 

    <it's possible I guess, that 5213 and 5636 are part of a larger string of digits

    Yes, that is the case.  That is why I used like and in the original query, it seemed to work OK with like and the field is a number property.  

    Let me know what the next step is when you get a sec.  

    I am thinking the left bracket ( [ ) in the below might be out of place?

    " WHERE import_ExcelBOA.[Account Number = " & [Enter 5213 for VW1 or 5636 for VWMC " &

    btw:  I removed the where clauses completely and the listbox does populate however I do need the where clauses.  

    • George_Hepworth's avatar
      George_Hepworth
      Silver Contributor
      Jan 22, 2025

      Okay, so "Like" works, as would other approaches, when working with embedded digits in a larger number. 

      Yes, I missed out on removing the left square bracket. 

       

      To return to the SQL Syntax then ExcelBOA.[Account Number] is a number, not a string of  digits in a text field.

       

      Private Sub cmButtonNameGoesHere_Click()

Dim strSQL As String

strSQL = "SELECT import_Excel_BOA.[As of Date], import_Excel_BOA.Amount," & _
        " import_Excel_BOA.[Account Number], " & _
        " import_Excel_BOA.[Account Name], import_Excel_BOA.Text " & _
        " FROM import_Excel_BOA " & _
        " WHERE (import_Excel_BOA.[Account Number] Like ""*"" & [Enter 5213 for VW1 or 5636 for VWMC] & ""*"")" & _
        " AND (import_Excel_BOA.Text Like ""*"" & [enter part of co name] & ""*"") ;"

Me.ListBoxNameGoesHere.RowSource = strSQL

End Sub

      That's as close as I can get working without data to validate against. I think I got all of the open and close parentheses in the right places, the square brackets where they are needed, and the quotes and double quotes in the right places and numbers. 

       

      • Tony2021's avatar
        Tony2021
        Iron Contributor
        Jan 23, 2025

        Perfect!  I like your suggestion of using a listbox.  I have never used one like that before.  It looks clean and easy to read.  Only downside is it doesnt look like you can copy the dataset as easily as one can from a query.  thanks George.  thanks for hanging in with me.  Very nice.  

Resources