Use value of a variable, as the field name.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sz - 17 Jul 2007 16:26 GMT

Hi All,

Thanks so much to anyone who might be able and willing to help me:)

Can i somehow pass data, from a form to a query, that represents the field
name,
that the query will use to retrieve specific data, from a table?

A value (which represents a question number) is set in my form. Let's say
that the value is "Q2".
The string variable to which this value is assigned is: strQuestionNum and
it is passed to a query.

tblSurveyResponses, is a table, used in the query. This table is populated
with data from an imported text file.
One of the FIELDS in tblSurveyResponses is named Q2, and that field contains
text representing a user's response to question number 2.

I know that the query is receiving the strQuestionNum - I've tested that
much. But I cannot figure out how to tell the query that i want the value of
strQuestionNum to be used as the field name.

I tried getting the data, in the query, by using the eval statement - but
access doesn't like that.
(query grid field expression being set to: ShowThis:
tblSurveyResponses.eval("Forms!frmSurvey!strQuestionNum")
I tried using various brackets and it doesn't help.

Thanks for taking the time to view my problem!

Reply to this Message

AuldMannie - 17 Jul 2007 16:48 GMT

One way of getting a query to respond to a value you have on an open form is
to use some thing like the following in the criteria of the query:-

>=[forms]![Menu Reports].[dt_from] And <=[forms]![Menu Reports].[dt_to]

Where the data is stored in text box(es) on the form (I was looking at date
fields this time). I'm sure others have more brilliant ideas but this works,
and if you keep the form open while testing you can fool around with the
query to your hearts content. Keep the square brackets as they will cover for
things like spaces in the names you have used (like "Menu Reports" above).
This passes the form data to the query.

AuldMannie

> Hi All,
>
[quoted text clipped - 25 lines]
>
> Thanks for taking the time to view my problem!

Reply to this Message

sz - 17 Jul 2007 17:34 GMT

Hi A.Manni,

Thanks so much for your quick and kind response. I'm saving it, as well as
the syntax for the form fields.

Since I'm normalizing my survey data, I am actually using my form field, in
the query's criteria - pulling in only "Q2" records from another table,
however, I actually need to look at a field, that is actually named "Q2"
(there are many fields that will match the various possible entries), on a
table - and I am only able to pass it as the contents of a field, and I can't
use it as a field name.

I know that i can normalize the survey data, using excel (copy pasting and
transposing), but due to the vast amount of data expected, this won't be
practical in the long run:(

Any further thoughts are welcomed!,
sz

> One way of getting a query to respond to a value you have on an open form is
> to use some thing like the following in the criteria of the query:-
[quoted text clipped - 39 lines]
> >
> > Thanks for taking the time to view my problem!

Reply to this Message

John W. Vinson - 17 Jul 2007 19:14 GMT

>I know that i can normalize the survey data, using excel (copy pasting and
>transposing), but due to the vast amount of data expected, this won't be
>practical in the long run:(

You can and should normalize the data, and should NOT have fields like Q2 or
Q257! However, it is not necessary to use Excel, nor is it necessary to use
copy and paste. A "Normalizing Union Query" can migrate data from a wide-flat
spreadsheet design into a properly normalized table. If you'ld like to do so,
post some details about the current structure of your table.

To get a variable into a fieldname you will need to write VBA code to actually
construct a SQL string, incorporating the fieldname into the string as you go.

John W. Vinson [MVP]

Reply to this Message

sz - 17 Jul 2007 20:16 GMT

Hello and thanks very much for getting involved with this issue.

I apologize for not being clear enough. I am indeed attempting to construct
a normalized database - I have inherited a flat file with all of the survey
responses and survey questions, and survey question numbers - one huge record
per survey response.

I can happily report that i have already created an access header table
without much difficulty and also a table of all questions and the
corresponding numbers and data types (memo/text...) (which correspond to the
numbers on this inherited flat text file). I've even constructed a detail
table which contains the user's ID and the question number, and an empty (for
now) field for the actual survey response data, for THAT question (one per
record). I am having great difficulty populating the actual responses on the
detail file. I can make 19 differrent queries (one for each survey question),
but i'd really rather do it semi-programatically. I will be working from
similar flat files and some with many more than 19 questions.

I have been pouring over examples using the recordset field name - so that i
can construct sql statements - only having to execute the sql once and simply
changing the value of the field name 19 times and calling the sub procedure.

ok - I'll see if i can supply you with the detail that you have requested.
Yes i would like to write VBA code to actually construct an SQL string,
incorporating the fieldname into the string as I go. Once upon a time (many
many years ago).

I would also love an example of the "Normalizing Union Query" - I'll try to
do some research as well. The inherited data is in a table that i generated,
during the import. Since the first row of the data, contained (already
assigned) field names - those are the names of the fields.
Imported table (both data and field names are inherited): tblSurveyResponses
One record per responder
A sampling of the Imported fields (all as String):
Field Name Field Data Type Field Contents
Q1        Text    Up to 255 characters of typed comments, entered by the survery
responder.
Q2        Text    Up to 255 characters of typed comments, entered by the survery
responder.
Q3        Text    Up to 255 characters of typed comments, entered by the survery
responder.
Q4        Text    Up to 255 characters of typed comments, entered by the survery
responder.
UID        Text    Contains the survery responder's (eMail) unique Identification

There's some more misc header information and many more questions, but I
have all of the header information,
and i would treat questions 5 through 19 similary to 1 through 4. Hopefully
resulting in records that look
like this, in the detail table (one record for every question, per responder):

UID        Text    Contains the survery responder's (eMail) unique Identification
QuestionID    Text    Contains data such as "Q1" or "Q2" or "Q3"
SurveyResponse    Text    Contains Up to 255 characters of typed comments, entered
by the survery responder, for
            the question number corresponding to the QuestionID field, in this record.

Thanks for any advice.

> >I know that i can normalize the survey data, using excel (copy pasting and
> >transposing), but due to the vast amount of data expected, this won't be
[quoted text clipped - 10 lines]
>
> John W. Vinson [MVP]

Reply to this Message

sz - 17 Jul 2007 20:24 GMT

Hello again,

I did some search and saw other advice that you had given, regarding the
Union query. I think that this is what I will do. 19 statements and i can
arrange the data, for my detail table that way.

Please don't feel as if you must post more detail as I will read through the
similar posts.

Thanks very much!

> Hi All,
>
[quoted text clipped - 25 lines]
>
> Thanks for taking the time to view my problem!

Reply to this Message

John W. Vinson - 18 Jul 2007 17:12 GMT

>Hello again,
>
[quoted text clipped - 4 lines]
>Please don't feel as if you must post more detail as I will read through the
>similar posts.

Thanks for doing the digging! If you have any problems please feel free to
post back.

John W. Vinson [MVP]

Reply to this Message

sz - 18 Jul 2007 18:08 GMT

You did the work - the least I could do was a little digging! Thanks so much
for the information AND for the invitation - it is working beautifully. UNION
query - very helpful, especially with this kind of inherited data. This has
been my first experience on 'here'!

All the best,
sz

> >Hello again,
> >
[quoted text clipped - 9 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 18 Jul 2007 20:34 GMT

>You did the work - the least I could do was a little digging! Thanks so much
>for the information AND for the invitation - it is working beautifully. UNION
>query - very helpful, especially with this kind of inherited data. This has
>been my first experience on 'here'!

Glad to be able to help.

Oh, you can't scare me, I'm sticking to the union,
I'm sticking to the union, I'm sticking to the union.
Oh, you can't scare me, I'm sticking to the union,
I'm sticking to the union 'til the day I die.

<Woody Guthrie... betcha didn't know he was a great Access developer!>

John W. Vinson [MVP]

Reply to this Message

sz - 18 Jul 2007 21:22 GMT

Well......I love Woody G. But I'd bet (if i had, if i had, if i had one
million dollars) it's the OTHER Woody with the Access Watch!

Got interrupted from my Union Query to teach someone how to sort and filter
records, in a table. So I was the expert for a moment:)

> >You did the work - the least I could do was a little digging! Thanks so much
> >for the information AND for the invitation - it is working beautifully. UNION
[quoted text clipped - 11 lines]
>
> John W. Vinson [MVP]

Reply to this Message