Inserting strings into variables - Syntax issue (1 Viewer)

theferd

Registered User.
Local time
Today, 04:52
Joined
Dec 23, 2019
Messages
42
Hi everyone. Being relatively new to VBA, I have had issues correctly constructing a line of code to allow me to quickly select the correct queries in a form.

I have four combo boxes that let you select values 1-100. I want these to transfer to a SELECT command (ex. qry1, qry2... qryN). These values are expressed as strings.

I have been able to accomplish this concept in other languages such as python and C#, but have been unable to produce the same result in VBA. Any help is appreciated.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:52
Joined
Sep 21, 2011
Messages
14,037
strSQL = "SELECT * FROM qry" & Me.cboNumber

from the immediate window

Code:
tt="1"
? "select * from qry" & tt
select * from qry1

HTH
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:52
Joined
Jul 9, 2003
Messages
16,243
Hi everyone. Being relatively new to VBA, I have had issues correctly constructing a line of code to allow me to quickly select the correct queries in a form.

I have four combo boxes that let you select values 1-100. I want these to transfer to a SELECT command (ex. qry1, qry2... qryN). These values are expressed as strings.

I have been able to accomplish this concept in other languages such as python and C#, but have been unable to produce the same result in VBA. Any help is appreciated.

Well the obvious answer would be to put the queries Qry1, Qry2, etc in a single combobox, but you mentioned four comboboxs, so I'm guessing there's a bit more to it than is obvious from your initial question
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:52
Joined
Jan 23, 2006
Messages
15,361
I agree with Tony --- I think there's more to the requirement than stated in post 1.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:52
Joined
Feb 28, 2001
Messages
26,996
The trick for this, particularly for people still struggling with VBA, is "Divide and Conquer."

Break this up into elementary pieces rather than trying to run it all together in a single complex statement.

First, select whatever it is that you wanted to select. Get the value string out of the combo box so that you can use it for other purposes.

Second, start constructing the string via concatenation. Don't stop this until you have the finished string.

Third, insert that finished SQL string into the .RowSource or .RecordSource of the object for which you were doing this exercise and then .Requery that object.

Fourth, learn how to use VBA debugging features such as breakpoints, single-step execution, and the ability to use Debug.Print to examine variables in their intermediate (incompletely built) state. That way you can actually see what you are building.

You mentioned your inability to do what you want in VBA. Unfortunately, that is kind of vague, so all we are going to be able to do is give general advice. If you want to get more specific help, we are going to need to know more specifics. Including what you tried and what it did that was wrong according to your goals.
 

theferd

Registered User.
Local time
Today, 04:52
Joined
Dec 23, 2019
Messages
42
Well the obvious answer would be to put the queries Qry1, Qry2, etc in a single combobox, but you mentioned four comboboxs, so I'm guessing there's a bit more to it than is obvious from your initial question

The end result is to compare the 2-4 queries where certain values match. It would be simpler from a developer standpoint to make the combo boxes the queries themselves, but I am not the intended end user and wish to make it appear as uncluttered as possible on the form.
 

theferd

Registered User.
Local time
Today, 04:52
Joined
Dec 23, 2019
Messages
42
So reviewing the various advice,

I used the basis of Gasman's code and advice from Doc man to create a SQL line. I don't quite understand how to use debug.print so I just assigned the string to a text box.

Code:
Dim cmbo1 As String
Dim cmbo2 As String
Dim cmbo3 As String
Dim cmbo4 As String
Dim Sql As String


cmbo1 = "qry" & Me.cmbo41
cmbo2 = "qry" & Me.cmbo42
cmbo3 = "qry" & Me.cmbo43
cmbo4 = "qry" & Me.cmbo44
Me.Text1 = cmbo1
Sql = "SELECT cmbo1 cmbo2 cmbo3 cmbo4"
Me.Text2 = Sql

The result is of Text1 is qryN which is correct but then Text2 is "SELECT cmbo1 cmbo2 cmbo3 cmbo4" instead of "SELECT qryN, qryN2, qryN3, qryN4"
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:52
Joined
Sep 21, 2011
Messages
14,037
You SELECT fieldnames FROM table/query

So are the combos meant to hold fieldnames or query indices?

So reviewing the various advice,

I used the basis of Gasman's code and advice from Doc man to create a SQL line. I don't quite understand how to use debug.print so I just assigned the string to a text box.

Code:
Dim cmbo1 As String
Dim cmbo2 As String
Dim cmbo3 As String
Dim cmbo4 As String
Dim Sql As String


cmbo1 = "qry" & Me.cmbo41
cmbo2 = "qry" & Me.cmbo42
cmbo3 = "qry" & Me.cmbo43
cmbo4 = "qry" & Me.cmbo44
Me.Text1 = cmbo1
Sql = "SELECT cmbo1 cmbo2 cmbo3 cmbo4"
Me.Text2 = Sql

The result is of Text1 is qryN which is correct but then Text2 is "SELECT cmbo1 cmbo2 cmbo3 cmbo4" instead of "SELECT qryN, qryN2, qryN3, qryN4"
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:52
Joined
Jan 23, 2006
Messages
15,361
?????
The result is of Text1 is qryN which is correct but then Text2 is "SELECT cmbo1 cmbo2 cmbo3 cmbo4" instead of "SELECT qryN, qryN2, qryN3, qryN4"


It might be helpful to you and readers if you could give an example of what you are working with. Making things uncluttered is a well-intended goal, but describing the requirement with clear examples will streamline communications.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:52
Joined
Jul 9, 2003
Messages
16,243
I think you have got your question wrong. From the information you've provided it seems more likely that you want to build an SQL statement that return certain Fields.The fields returned are provided by selections from combo-box's. Is that a better interpretation of your question? If not, please have a go at explaining again...
 

theferd

Registered User.
Local time
Today, 04:52
Joined
Dec 23, 2019
Messages
42
You SELECT fieldnames FROM table/query

So are the combos meant to hold fieldnames or query indices?

I intended to use the combo box values for both SELECT and FROM.

The long form of the code for just two combo boxes would be "SELECT qry1.number qry2.number FROM qry1 INNERJOIN qry2 ON qry1.number = qry2.number"
 

theferd

Registered User.
Local time
Today, 04:52
Joined
Dec 23, 2019
Messages
42
I think you have got your question wrong. From the information you've provided it seems more likely that you want to build an SQL statement that return certain Fields.The fields returned are provided by selections from combo-box's. Is that a better interpretation of your question? If not, please have a go at explaining again...

Haha, I have always been poor at diction especially when it comes it learning a new language. So the long haul is the values selected in the combo boxes will return queries of the same name (prefixed with qry). This will form a SQL for a merged query comparing the queries in question.

These queries relate to tables. Long story short, text values (Serial codes) have been assigned numbers 1-100, some have multiple numbers assigned to them. The goal of the merged query is to show all serial codes which have the value selected assigned to them. If 4, 8, 10, and 26 are selected, it will return these two serial codes as an example. This worked through simple hard coding but I want to make a user interface using combo boxes for variable input.
 

theferd

Registered User.
Local time
Today, 04:52
Joined
Dec 23, 2019
Messages
42
It might be helpful to you and readers if you could give an example of what you are working with. Making things uncluttered is a well-intended goal, but describing the requirement with clear examples will streamline communications.

Ok, let me see if I can clear things up again. A user will select 4 numbers in combo boxes. A button will initiate the code. Using the four numbers, (1,2,3,4) it will assign four variables, lets say var1,var2,var3,var4. These are assigned to a query under the same name as the number only prefixed with qry (qry1,qry2,qry3,qry4). I want to use these variables in a SQL statement that will be used to create a merged query comparing fields in each of these queries. However, simply typing out SQL= "SELECT var1.number var2.number... etc" will not replace var1 with its string value. It spits out the literal text. I accomplished this task of having var1 replaced by its value within a string in a language such as Bash, but I don't know how to do this in VBA.
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:52
Joined
Jan 23, 2006
Messages
15,361
theferd,

I am still confused

If 4, 8, 10, and 26 are selected, it will return these two serial codes as an example
????

I don't know Bash so that reference doesn't help.
 

theferd

Registered User.
Local time
Today, 04:52
Joined
Dec 23, 2019
Messages
42
I have a table with two fields. Serial codes, and a Number ranging from 1-100. Serial codes may appear multiple times with a different number. The goal is for a user to determine which serial codes are associated with all the numbers they selected.

Combo box 1 has the number 4
Combo box 2 has the number 8
Combo box 3 has the number 10
Combo box 4 has the number 26

There is a query for every number 1-100 that shows the respective serial codes with the number in their row. A particular serial code with 4, 8, and 10 will appear in qry4, qry8, and qry10, but not qry26. As such this code will not appear in a merged query as a result of the four combo boxes above.

I stored the values of each combo box into variables, var1,var2,var3,var4.

var1= "qry" & me.combo1 = qry4
var2= "qry" & me.combo2 = qry8
var3= "qry" & me.combo3 = qry10
var4= "qry" & me.combo4 = qry26

I am now trying to construct a query using these variables by writing a SQL statement.

SQL = "SELECT var1.number var2.number var3.number var4.number From etc."
The unintended result is SELECT var1.number var2.number var3.number var4.number
The desired result is SELECT qry4.number qry8.number qry10.number qry26.number

Is there a way to do this or an easier method? Again sorry for all the confusion.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 04:52
Joined
Jan 23, 2006
Messages
15,361
Sorry -it's just getting more confusing.....??

You might try posting 2 scenarios
1: some sort of starting values
2: the expected output based on the given starting values.

I don't mean to be negative, but your description is what I call jargonese. It means something to you, but it isn't the general meaning of query nor sql. It's some representation of HOW you see the logic. It isn't the WHAT you are attempting to achieve. It isn't plain simple English, if anything it's quasi-technical something. But we have to communicate in English to understand the concept/requirement involved.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:52
Joined
Feb 28, 2001
Messages
26,996
Haha, I have always been poor at diction especially when it comes it learning a new language. So the long haul is the values selected in the combo boxes will return queries of the same name (prefixed with qry). This will form a SQL for a merged query comparing the queries in question.

OK, don't worry about this being a language issue. Trust me, we have worked with a lot of people with this language problem before. We can be patient.

This statement of yours contains the fine point that I think everyone is tripping over.

You suggest that a comparison is involved. My understanding is that you have multiple queries that may in some ways be different but perhaps are similar in other ways, and you want to somehow compare them. It is this comparison that is important to you, but we are struggling to decide exactly what it is that is being compared.

Are you looking to compare the recordsets that each of the queries return; that is, to look for differences in the records returned by the queries being compared?

Perhaps we can try this from a different angle. Forget the queries for a moment. They are a means to an end. Tell us, without talking about queries or SQL or strings or ANYTHING that is database-technical, what is the end goal of the BUSINESS process? At the moment, we don't CARE about the theoretical query result. We need to know what is the business goal here. Once we get THAT out in the open, we might have some ideas for you.

If you have trouble with MY question here, don't hesitate to ask for clarification as well.


With that information plus your previous comments, perhaps we can reach a level of understanding that at the moment is evading us.
 

theferd

Registered User.
Local time
Today, 04:52
Joined
Dec 23, 2019
Messages
42
I went into more documentation on general string functions in VBA and finally got the result I wanted.

Somewhere we got too focused on the database and end result of the string and it resulted in all this confusion.

Simply put I wanted to put a variable, into a string, without the string spitting out the literal variable.

EX.
Code:
 Variable = "The string I want"

SQL = "SELECT Variable"

Intended - SELECT The string I want
Unintended - SELECT Variable

I accomplished this with basically what gasman said in the very first reply:

Code:
SQL= "SELECT " & Variable
 

Users who are viewing this thread

Top Bottom