sql query with variable field names (1 Viewer)

exaccess

Registered User.
Local time
Today, 12:58
Joined
Apr 21, 2013
Messages
287
How can I make the field names variable in the query below. Here the fields are called Language and LastName.


Code:
SQL = "SELECT * FROM [" & dest & "] WHERE [Language] Like 'X' ORDER BY [LastName];"
 
Last edited:

plog

Banishment Pending
Local time
Today, 06:58
Joined
May 11, 2011
Messages
11,613
Much much more context is needed. Your question itself is super weird, then the SQL you posted raises so many more questions.

I mean, you have a variable for your table name, so it shouldn't be too far a stretch to Intuit how to make your field names variable as well.

Tangentially, you aren't really using LIKE in your where clause. Without wildcards it's pointless. So, please explain more the situation.
 

exaccess

Registered User.
Local time
Today, 12:58
Joined
Apr 21, 2013
Messages
287
OK. The variable 'dest' represents a table name. The words 'LastName' and 'Language' point to the names of the fields in the corrosponding table 'dest'.
The tables are i n a loop. Example:
Code:
For I = 1 to 5  
     Select Case I  
          Case 1 
                dest = "TranstableTbl"
                Language = "E"
                LastName = "Smith"
                ..........
          Case 2 
                dest = TransSHTbl
                Langage = "F"
                Nom = "Yves"
                ...............
 End Select
OR
Code:
For I = 1 to 5  
     Select Case I  
          Case 1 
                dest = "TranstableTbl
                Language = "E"
                LastName = "Smith
                ..........
          Case 2 
                dest = TransSHTbl
                Language = "F"
                LastName = Yves
                ...............
 End Select
Whichever is possible and shorter. The tables dest have the same design but some of them have aliases for the same field. I want to select the records of table dest that contain a certain value which is also variable. This is what I want to do.


WhicheverNow
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:58
Joined
Jan 20, 2009
Messages
12,849
The tables dest have the same design but some of them have aliases for the same field. I want to select the records of table dest that contain a certain value which is also variable.

The two tables should be one. Then you have no need change tables in the query.
 

exaccess

Registered User.
Local time
Today, 12:58
Joined
Apr 21, 2013
Messages
287
The two tables should be one. Then you have no need change tables in the query.
Well that is not the case. I have 10 of these tables and Each one is handled differently. Of course there may be changes in the design of the system, which may render other solutions. But this is an application which evolved through the years. I am looking for a quick solution.
 

static

Registered User.
Local time
Today, 11:58
Joined
Nov 2, 2015
Messages
823
As plog said. The table is already a variable. Fields are handled exactly the same way.
 

exaccess

Registered User.
Local time
Today, 12:58
Joined
Apr 21, 2013
Messages
287
I cannot get the query working with variable fields and operators I am not trying to discuss the design of the tables.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:58
Joined
Jan 20, 2009
Messages
12,849
I cannot get the query working with variable fields and operators I am not trying to discuss the design of the tables.

It is not clear how you are running the query. Note however that fieldnames, tablenames and operators cannot be query parameters (which is why one does not structure data in the way you have.)

You would need to use VBA to build the query sql. To display on a form you could create a recordset based on the sql, change the Recordsource to the sql or modify the SQL property of the RecordSource query.
 

exaccess

Registered User.
Local time
Today, 12:58
Joined
Apr 21, 2013
Messages
287
This is an update query Run by docmd. I used the tablename and fieldname as SQL parameters. Here the the data of various groups has to stay in separate tables. This is sin qua non. Additionally the number and names of the tables fields in them are different. The vba procedures to do this are known to me.Yes if worse comes to worse I shall use vba to do it. But the point is that it can be done via un sql statement but I am getting syntax errors. This is where I asked for help.
 

exaccess

Registered User.
Local time
Today, 12:58
Joined
Apr 21, 2013
Messages
287
That is the problem. Parameters can only be values, not object names.



That is what you will need to do.
Dear Galaxikom it goes a little bit out of discussion. but I must remind you that table names and field names which I implemented are object names. Isn't there a contradiction in your statement.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:58
Joined
Jan 20, 2009
Messages
12,849
but I must remind you that table names and field names which I implemented are object names. Isn't there a contradiction in your statement.

No contradiction. I pointed out that is the problem.

Galaxiom said:
Parameters can only be values, not object names
 

exaccess

Registered User.
Local time
Today, 12:58
Joined
Apr 21, 2013
Messages
287
No contradiction. I pointed out that is the problem.

Well lets say contradiction between what you can really do and what you say you can do. In any case I have below a working example :

Code:
UPDSQL = "UPDATE [" & dest & "] SET Association = 'ANAR';"
Here 'dest' is a variable holding the table name and 'Association' is a variablel holding the field name. All I want is to replace the value ANAR by a variable holding the value ANAR, put them in a loop so that I can change the three values and you say this is not possible, because sql does not accept object names. Tables are objects. Are'nt they.
 

static

Registered User.
Local time
Today, 11:58
Joined
Nov 2, 2015
Messages
823
Variables are values stored in memory that can change.

Association isn't a variable. dest and UPDSQL are.

Code:
dim dest as string, myval as string

dest = "table1"
myval = "'ANAR'"

currentdb.execute "UPDATE [" & dest & "] SET SOMESTRINGFIELD = " & myval 

dest = "table1"
myval = "#1/1/2017#"

currentdb.execute "UPDATE [" & dest & "] SET SOMEDATEFIELD = " & myval 
dest = "table1"
myval = "1234"

currentdb.execute "UPDATE [" & dest & "] SET SOMENUMBERFIELD = " & myval

String values have quotes, dates have ##
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:58
Joined
Jan 20, 2009
Messages
12,849
Well lets say contradiction between what you can really do and what you say you can do.

It appears you have not encountered "parameters" in the context of a query. Of course you can concatenate anything you like into an SQL command string but they are not "parameters".

BTW You would probably get a lot further if you put as much effort into explaining what you were trying to do as you do rebuking those who are trying to help.
 

exaccess

Registered User.
Local time
Today, 12:58
Joined
Apr 21, 2013
Messages
287
Code:
currentdb.execute "UPDATE [" & dest & "] SET SOMESTRINGFIELD = " & myval

Yes Static this was the solution I was looking for. It works like charm.

Galaxion I did not want to rebuke anyone, nor did I use inappropriate language. Do not worry I put much more time to research on finding new and shorter ways to do things then to just accept what is written in the forum. This solution saved me a lot of coding in VBA.

Thanks a lot Static.
 

Users who are viewing this thread

Top Bottom