Solved

Changing strings in a query

Posted on 2003-11-25
7
310 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
microsoft access - xml 10 29
Access 2010 3 19
Trying to open a report with a filter 2 17
Join vs where 2 0
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now