Solved

Changing strings in a query

Posted on 2003-11-25
7
342 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

792 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