Solved

Changing strings in a query

Posted on 2003-11-25
7
369 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

724 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