Using variables in SQL statements

[Jeff31592]

Need to use a variable (that takes its value from combo box) in SQL statement in VBA. Not sure how this is done. Neophyte so give me simple step wise instructions without programmer speak. Example is good as long as it is annotated.

SQL looks like this:
propSQL = "SELECT * FROM prices " & "WHERE(((prices.Plant_ID) = 'GA02') AND ......

so instead of the hard-coded string GA02, I want the SQL statement to take a variable whose value is driven from selection in combo box, i.e.

Case 5
locID = "GA02"

Thanks,

jeff
San Diego

 

[pbaldy]

Along the lines of:

propSQL = "SELECT * FROM prices " & "WHERE(((prices.Plant_ID) = '" & Me.ComboName & "') AND ......"

 

[Jeff31592]

I will give it a try.

BTW, our distribution center in New Jersey is on John Galt Way (some civic planner with a taste for Ayn Rand I suppose)
Jeff

 

[pbaldy]

Post back if you get stuck Jeff, and welcome to the site by the way!

Interesting about the street. I wonder if the book inspired the name or a local person who had the same name.

 

[Jeff31592]

Paul,

I can't get the syntax right - either get an error message or the code executes but skips over my Do Loop. Here is what I have for code that runs but skips the Do Loop:

strSQL = "SELECT" * FROM prices " & "WHERE(prices.Plant_ID) = '& Me.Combo1.Selected'"

qrydf.SQL = strSQL

Set recrdset = qrydf.OpenRecordset(dbOpenDynaset)

Do Until recrdset.EOF
Debug.Print recrdset!Part_Id & " " & recrdset!price
recrdset.MoveNext
Loop
____________________________________________________________
when you run in debug programgets to the Do statement and then skips over the loop and executes the next line after Loop. Eliminated the AND operator in the SQL to try and simplify things and get syntax right but evan with stripped down SQL am not getting what I need.

I can do this whole process using just recordset and nested IF statements but the action that I am performing is really just a query so I should be able to use querydef. Let me know if this is making sense.

Thanks,

Jeff

 

[pbaldy]

You've changed the quotes and ampersands from what I posted, and ending up with invalid SQL.

 

[Jeff31592]

if I use the syntax you provided I get a compile error: Expected end of statement.

since I have dropped the AND in the SQL statement I don't need the 3rd set of parens.

Let's make it even simpler, drop the comboBox and just get the value directly from the user:

plantLoc = InputBox("enter location")

so what would my SQL statement look like? Querying all records from tableprices, with the filter on field Plant_ID that only returns records where this field is equal to the value that the user has input in plantLoc variable.

There is some basic understanding of variables in SQL that I lack. Help me to understand. I have the same problem in Visual Basic when I try to use a variable in the SQL statement when working with either Access or Oracle tables.


Jeff

 

[pbaldy]

propSQL = "SELECT * FROM prices WHERE prices.Plant_ID = '" & plantLoc & "'"

 

[Jeff31592]

Bada Bing!

works perfect with inputbox or combo.

Can you explain how the (') and (") were selected i.e. how to syntax the variable like you did to make it work in the sql statement. explain the quotes (") and single notches (')

thanks,

jeff

 

[pbaldy]

Here's a tutorial

http://www.baldyweb.com/BuildSQL.htm

 

[Jeff31592]

Paul

Tutorial was interesting but it raised 3 issues:

1. in your example in the tutorial on strings your syntax is different from that which you sent me. What you sent works but if I mimic what you have in the tutorial then it throws an error

2. If I want to add in my AND (see my original post) clause to my SQL statement not sure what the syntax looks like in terms of &'s and apostrophe's and quotes. Tried a bunch of different combinations and none worked.

3. Not sure what SQL statement in item one above would look like if I were querying dataset from Access in Vis Basic. You can't just take what worked in VBA and slide it over to VB; I tried and it doesn't work. Not sure why this is.

I think I need to read about basic SQL code writing; too much time using Oracle and Access where there are UI's that do the SQL statements behind the scenes based on how you select and link tables. any suggestions for basic SQL ?

Jeff

 

[pbaldy]

1) It should work either way. What I gave you here is a shortcut to the method using Chr(39). If you post your effort with that syntax, we can figure out what went wrong.

2) Again, if you posted an effort we can fix it. The specific syntax would depend on the data types. If both were text:

propSQL = "SELECT * FROM prices WHERE prices.Plant_ID = '" & plantLoc & "' AND OtherField = '" & OtherVariable & "'"

3) The SQL has to be appropriate to the database engine, so I think the SQL would be the same, at least for VB6 (presuming you still have data in Access). It's the connection method that would differ. I've no experience with .NET, so I can't answer to that.

 

[Jeff31592]

Paul,

I will look again at #1.

#2 - That's a win! as soon as I used your syntax and cleaned up my type mismatch, query ran fine. Just need to study it and learn the logic behind the syntax.

#3 - i will noodle around with VB this weekend and see if i can get it to run. you can configure your SQL statement in the Dataset and it looks like pretty standard SQL but when you pull out the constant and drop in the variable it goes kaboom. Here is what the data looks like in Table Adapter Query Configuration Wizzard:

SELECT Loc_number, Loc, Part_ID, Description, Cost, Price
FROM Resi_Pricing
WHERE Part_ID = "207725"

If I wanted to use the variable model in place of the constant 207725, what would show on the right side of the equal sign?

Jeff

 

[pbaldy]

Sorry, I'm unfamiliar with the "Table Adapter Query Configuration Wizzard". In VB6 I've often built a string like in your earlier code (with variables) and executed it or opened recordset on it. Sounds like you're in something other than VB6.

 

[Jeff31592]

Paul

Thanks for all the help. With your help and tutorial I was able to accomplish what I needed in Access 2010. Program is now executable and returns the correct data based upon user supplied values for variables that are loaded into the SQL statements.

I was just wondering if it wouldn't be more elegant to create this whole creature as an app that I could load on my users desktop, so I was looking at recreating this functionality in VB. I was using Visual Studio VB and working with ADO.NET to query an Access or Oracle database. Realized I was spending way too much time designing a simple business application that I could have written in Excel in half the time, so I'm pulling up anchor and moving on. I'll fool around with it on the weekends and maybe learn something of ADO.NET along the way. If I find something of interest I will pass it along to you.

Jeff

 

[pbaldy]

No problem Jeff. Good luck!