Selecting Characters for Report Lists

[Taffski]

Hi all,

I've created a query to construct a report (MS Access 2010) based on questions from one table and optional answers from four other tables (one of which contains the correct answer, the other three containing incorrect answers).

I've managed to randomise the order of the optional answers in the report but I cannot get them to list as (a), (b), (c) and (d).

I've set the Running Sum Control Source = 1 (I've also tried alternative characters) and to Over Group but whatever I try in the formatting options I always get numbers returned and not letters.

Confuddled and very appreciative of any assistance.

Taffski

 

[Galaxiom]

Do't know if this will help but numbers can be converted to lower case letters with:

=Chr({numeric expression} + 97)

 

[Taffski]

Thanks for the info but can't seem to get that one working either.

Maybe I'm putting it into the wrong place.

Thanks again.

 

[Taffski]

Could you possibly explain what is meant by {numeric expression} in:

=Chr({numeric expression} + 97)

I've tried everything I can possibly think of; I can't find a solution in any of the on-line services I've tried (although there are probably more), so I am still stuck with:

(1)
(2)
(3)
(4)

,when what I require is:

(a)
(b)
(c)
(d)

This is probably very simple to some learned folk out there but us mere mortals struggle until shown!!!

Thanks for any assistance.

 

[Galaxiom]

A numeric expression is anything that resolves to a number. It can just be a number, a field or control that holds a number or an arithmetic calculation.

Chr(1 + 97) will return "a"

So if you have a field called Answer the expression would be:
Chr([Answer] + 97)

This will return a for 1, b for 2, c for 3 etc

 

[Taffski]

Thanks millions GAH,

But I am obviously sliding down the evolutionary ladder here, rapidly approaching amoeba status!!

Needless to say I cannot get it to do what I want.

A bit more info from me may help.

I'm using MS Access 2010 and have created an Examination Question Database (EQD) for preparing practice exams for students.

EQD comprises a table of Questions and a table of Answers, the One-to-Many relationship set by Question Number, each question having four optional answers.

The exams at this stage are fixed, i.e. by Exam ID Number and Question Number, so a simple Select Query pulls out the 20 questions from Table:Questions for Exam 01 and places them in numerical sequence. It also selects the four optional answers for each question from Table:Answers, the order of which is randomised within the query.

So far so good.

The Report based on that query has the questions ordered by Group based on the Question Number, with the Question in the Group Header section and the Answer in the Details section.

This is where the problem arises.

I need the four optional answers to be listed (a), (b), (c) and (d).

I cannot pre-name each answer within the Table:Answers as the Random Sort in the query alters the order of the four optional answers when the query is run.

I have at the moment set the Text Box that displays the question number before the question text to =1, the Running Sum to Over Group and the Format to (00). This returns (01), (02), (03) and (04). If I could accept numerical values for the optional answers this would be fine but I really need alphabetical values.

I can see what GAH's expression does but where does it fit in?

I created a field [Optional Answer] and gave this a value of 0 within the query but if I enter =Chr([Optional Answer]+97) into the Control Source section of the Text Box in the Report I get 'a' for all answers.

I'm sure the answer is very obvious to the learned folk out there but it would be greatly appreciated if you could provide an answer that would fit in well with Access Solutions for Taffski (Two steps down from Access Solutions for Idiots).

Many thanks in advance!!

 

[Galaxiom]

I am not clear what you have so far but if you can get a textbox or field to just show the numbers you are almost there.

Create a textbox with the controlsource

="(" & Chr([numberfield] + 97) & ")"

You can use either the field in the Recordset of the RecordSource query or the textbox on the form. If there is no textbox by the name that you use in the expresssion, Access will look for a field by that name.

 

[Taffski]

Thanks again GAH.

I have that much.

I've tried creating a field called [Optional Answer] with a Default Value of '0' in Table:Answers.

The Text Box in the report has the Control Source ="(" & Chr([Optional Answer]+97) & ")" as suggested.

The above produces (a). Unfortunately, all four optional answers have (a) at the beginning. This is where the problem lies.

If, instead of the above, I give this Text Box a Control Source of =1 and Running Sum Over Group, I get 1,2,3,4, which would, if push came to shove, have to suffice. It's getting the alphabetical characters in place of numerical values is the issue.

It just isn't happening

 

[Galaxiom]

I have not used running sums but I expect you could apply the Chr function to the running sum.

If you like, post a sample of the database with just the essential tables and report with a few records and I will have a look. Compact it and zip before posting.