SQL saved queries with parameters

I have the following SQL.  How would i update it so that i can pass in the field name (in this case i have hard coded [Recipient State/Province]) but i want to be able to pass that in instead.

SELECT  *
FROM Staging 
LEFT JOIN States ON Staging.[Recipient State/Province] = States.StateID
WHERE (((States.StateID) Is Null));

Open in new window

vbnetcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You cannot modify the structure of a query on the fly.  If you need a query where you must change anything structural such as columns, tables, relational operators, join types,  you will need to build the SQL in VBA and run it that way.
0
vbnetcoderAuthor Commented:
OK thinking about it some more that might be easier anyway
0
Jeffrey CoachmanMIS LiasonCommented:
It might be simpler to just edit the SQL...

To build a system to:
Ask for the field Name  (though you could build a combobox to list the available fields)
Modify the SQL
Save the query
run the query

...Would require a fair amount of work and validation:
Names with spaces in them
Alert for nulls
Delete the previous saved query
...etc

Query Parameters are typically used for "Criteria", for example:
    SELECT * FROM YourTable WHERE EmpID=[What EmployeeID]
...but Parameters are not designed for the field names.
This is why you "SELECT" the field names...

There is a lot we do not know about this system, or why it is needed,...perhaps you could take a step back and explain exactly what you are trying to accomplish?
Is this a one shot deal?
Are you trying to create separate saved queries for all possible field names?
...etc

Again, (based on what you posted) just editing the SQL would seem to be a lot easier.


JeffCoachman
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
As an example, here is some very basic code that creates a new query from an SQL string, where the Field name must be specified:
    'Delete the previous query (if it exists)
    If DCount("Name", "msysObjects", "Name='qryTemp'") = 1 Then
        DoCmd.DeleteObject acQuery, "qryTemp"
    End If
    
    'Declarations
    Dim qdfNew As QueryDef
    Dim strSQL As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strFieldName As String
    
    '--How the Field name is specified-----
    ' A combob box provied the field Names
    'strFieldName = Me.Combo5
    ' The user types in a field name
    'strFieldName = InputBox("What Field")
    '--------------------------------------
    
    'Build the SQL string
    strSQL1 = "SELECT YourTable.ID, YourTable.YourRequiredField, YourTable.YourStateField,YourTable.["
    strSQL2 = "] FROM YourTable;"
    strSQL = strSQL1 & strFieldName & strSQL2
    
    'Load the SQL into a text box for viewing, or copying to the SQL editor
    Me.txtSQL = strSQL
    
    'Create the saved Query
    Set qdfNew = CurrentDb.CreateQueryDef("qryTemp", strSQL)
    
    'Refresh the database window/task Pane
    Application.RefreshDatabaseWindow

Open in new window


Again, simply editing the SQL directly would seem to me, to be a great deal simpler.

This is why an explanation of "why" this needs to be done is important, ...in case there is a simpler way...

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
...or perhaps I am missing something?
0
vbnetcoderAuthor Commented:
ty
0
Jeffrey CoachmanMIS LiasonCommented:
ok
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.