Solved

Changing strings in a query

Posted on 2003-11-25
7
334 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:tmccar10
7 Comments
 
LVL 23

Expert Comment

by:heer2351
ID: 9820577
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
 

Author Comment

by:tmccar10
ID: 9820772
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
 
LVL 7

Accepted Solution

by:
RemRemRem earned 75 total points
ID: 9820917
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 23

Assisted Solution

by:heer2351
heer2351 earned 25 total points
ID: 9821154
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
 
LVL 2

Assisted Solution

by:glennkerr
glennkerr earned 25 total points
ID: 9821424
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
 

Author Comment

by:tmccar10
ID: 9821444
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
 
LVL 9

Expert Comment

by:ornicar
ID: 10075092
----------------------------------------------------------------------------------------
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question