Changing strings in a query

I'm not sure if this is doable in ACCESS and my VB is weak.

I'd like to be able to change a string that's in a query. For example;

SELECT rank4.'AQ4_10' AS Response, rank4.[FACING NAME], Nz(Count([rank4].['AQ4_10]'),0) AS Expr1, Count(rank4.AQ4_10) AS [All Depots], "AQ4_10" AS question
FROM rank4
GROUP BY rank4.'AQ4_10', rank4.[FACING NAME];

I'd like to have a form, or some user friendly method to change the 'AQ4_10' everywhere it appears and then run the query again.

Where do I start, can I do it?

I'm showing the query in SQL...I actually put it together in a Design View

Thanks
tmccar10Asked:
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.

heer2351Commented:
rank4.'AQ4_10' does not make much sense to me. What are you trying to do, select a field named AQ4_10 or do you have a constant with the value 'AQ4_10'?

Could you explain a little more of why you need to change 'AQ4_10'?
0
tmccar10Author Commented:
I'll tr without getting it too confusing.

AQ4_10 is a field. Each substitiution is the name of a filed. This is a survey that I think was build rather badly. I'm really limited as to how to extract the data.  The only way I could figure out how to extract each question was to keep changing the query, adding the next variable (field).

I also needed to create a way to join the text of the question. Since the value of these fields (like AQ4_10) is a number 0 through 5, I couldn't join to the field name. Unfortunately not all the question reflect this structure so they have to be executed out of sequence

Ultimately, I decided to try and make a form into which I could enter the field name and get it to the query and run it as I generated the report for each question.

I know, I know, but I'm pretty much stuck with the way it is.

Thanks

0
RemRemRemCommented:
If you're trying to allow the user to change which field they use to get their "Response" value, try doing the following:

Create a blank query called "qryResults" and save it.

Create a combo box called "cmbChoices" on a form from which they can run the report (either from a table source or by keying in the values) which has every field the users can use to get results.  For example, the list might be 2 columns wide, with a "technical" value in column one (0), and a "human" value in column two (1).

The drop down would provide:

Hidden          Showing
-----------------------------
AQ4_10        Quarter 4
AQ3_10        Quarter 3
AQ2_10        Quarter 2
etc

Then on a button push for button "cmdQueryMaker," do the following code:

Public Sub cmdQueryMaker()
Dim strMySQL As String
Dim strMyChoice As String
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb

DoCmd.DeleteObject acQuery, "qryResults"
strMyChoice = Me.cmbChoices    ' This will grab column 0 of the combo box, grabbing the "AQ4_10" or "AQ3_10" etc

'   Create the new SQL string for your query adjustment
strMySQL = "SELECT rank4." & strMyChoice & " AS Response, rank4.[FACING NAME], " _
    & " Nz(Count([rank4]." & strMyChoice & "),0) AS Expr1, Count(rank4." & strMyChoice & ") " _
    & " AS [All Depots], '" & strMyChoice & "' AS question FROM rank4 GROUP BY rank4." & strMyChoice & ", " _
    & " rank4.[FACING NAME];"

Set qdf = dbs.CreateQueryDef("qryResults", strMySQL)
qdf.SQL = strMySQL

'   Add code below this to run any reports or open forms based on the query you've updated

End Sub



****

This will erase the last run of the query and create a new one with the users' choice.  Be careful that you not let multiple users run this at the same time, or they'll conflict as they override each other.

-Rachel Morris
M.G.C.D. Consulting
owner@mgcd.com
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

heer2351Commented:
Not much to add to Rachel's comment, that is a good way to do it. Two additions though, first of all the code uses DAO so if you use msaccess 2K or above make sure Microsoft DAO 3.6 Object Library is included in your references. Secondly since the field name entered by the user could contain spaces you should add square brackets around the field name.
As a final note you can get rid of all the rank4 references since you only select from one table, might make life easier if you want to use the same string with a different table.

'  Create the new SQL string for your query adjustment
strMySQL = "SELECT [" & strMyChoice & "] AS Response, [FACING NAME], " _
    & " Nz(Count([" & strMyChoice & "]),0) AS Expr1, Count([" & strMyChoice & "]) " _
    & " AS [All Depots], '" & strMyChoice & "' AS question FROM rank4 GROUP BY [" & strMyChoice & "], " _
    & " [FACING NAME];"

0
glennkerrCommented:
The other way you could do it is just do a replace in the sql string.

e.g.

Private Sub cmdChangeToNewField_Click()
   me.RecordSource = Replace(Me.RecordSource,"AQ4_10","NewFieldName")
   'change the controls that were bound to the old field to bind to the new field...
   me.txtDisplayData.ControlSource = "NewFieldName"
End Sub

I'm sure there's more to your problem than that, but if you've already built the query and just want to change fileds, that will do it.  
0
tmccar10Author Commented:
Thanks-
I'll give this a try tomorrow.

There are over 200 questions to deal with and I have a very strict output mask to which to adhere.

It's making me crazy!
0
ornicarCommented:
----------------------------------------------------------------------------------------
This question has been abandoned and needs to be finalized.
 You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ornicar
Cleanup Volunteer

---------------------------------------------------------------------------------------------
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.