Autofill text box

[katana6434]

Hi

I am creating a small membership database for a club, and I am just getting to grips with access 2007.

I have a control on the form that displays the age (this is calculated automatically from the date of birth field). I have another control on the form that I want display the level of the member. This is to be filled in automatically, depending on the age, with the following:

below 10 = primary
10 - 15 = junior
16 + = senior

I don't want this to be stored in a table as it can easily be calculated on the fly (if I knew how...).

Is this possible.

Cheers

 

[rainman89]

somethin lik ethis would go in the control source of your text box

=IIF(Me.age<10, "primary",(IIF(Me.age>10 AND Me.age<15, "Junior", (IIF(Me.age>15, "Senior",)))

wrote it quick so it may need some tweaking

 

[Sprawl]

absolutely is!

this is where you would build your queries. Once the data is entered, when you start using it, you'll be amazed at the options you can do.

for example, in this case, you would build a new query with a column that opens a switch statement that says if it's a certain age, it assigns a certain name

However, I would actually create another table, with a list of ages and what membership they'd have

 

[boblarson]

You're close Ray, but you excluded age 10 and 15 by not including them.

The fix:

=IIF(Me.age<10, "primary",(IIF(Me.age>[B][Color=Red]=[/Color][/B]10 AND Me.age<15, "Junior", (IIF(Me.age>[B][Color=Red]=[/Color][/B]15, "Senior",)))

 

[rainman89]

ah yes, but u were close too. its says 16 + for senior....

=IIF(Me.age<10, "primary",(IIF([COLOR="Red"]Me.age>=10 AND Me.age<=15,[/COLOR] "Junior", (IIF([COLOR="Red"]Me.age>15[/COLOR], "Senior",)))

 

[boblarson]

guess we both need a vacation (let's go... )

 

[rainman89]

haha Deal!

 

[katana6434]

Whoa, now they were quick replies. Cheers for that.

When i put the expression in the box I get an error message saying

The expression you entered is missing a closing parenthesis, bracket (]), or vertical bar (¦).

The only way i have it accept the expression is if I put two more brackets at the end. The problem with this is that the box will then display #Name? when viewing data.

Any ideas.

I appreciate your help so far guys.


Cheers

 

[boblarson]

Make sure your text boxes are not named the same as your fields. So, if your field is named age, then name the text box bound to that field txtAge.

Then, change this so that it refers to the fields and not the form:
=IIF([age]<10, "primary",IIF([age]>=10 AND [age]<=15, "Junior", IIF([age]>15, "Senior",)))

 

[rainman89]

sorry there are 2 )) missing at the end.
you need to change the me.age to whatever field you have on there that figures out the age

 

[katana6434]

Of course, stupid me....

That is now working. Excellent. Really appreciate the help here.

Now to understand how that works so I don't have to ask next time....

Thanks a lot.

 

[rainman89]

Generally it works like this

=IIF(condition to check, if its true do this, if its false do this)

 

[boblarson]

So, just a quick explanation for you:

The immediate IF (IIF) has three parts.
1. The expression (what are you checking for true/false)
2. The True part (what do you want returned if the expression evaluates to true)
3. The False part (what do you want returned if the expression is false)

In your case, you needed nested IIF's because you want it to evaluate your first case - if the age is less than 10. Then, if that comes back false, then you want to check again to see if it falls between 10 and 15 (inclusively). That is why the >=10 And <=15 are needed. Also, when doing multiple parts you must restate what you are checking, so that is why you need to include [age] for both: [age]<=10 And [age]>=15. So, if that returns a true, then you have your value given, but then you needed a second "nested IIF" to check whether the age was >15.

Just remember, you have to nest the IIF's in the right order so that it will evaluate properly.

 

[Munkyjay]

Following on from the original query...

...how would you actually store the value in the table, rather than just having on show in the form? I've tried entering into the Validation Rule section and the Default Value section of the table properties, but it won't accept it.

Thanks in advance!

 

[boblarson]

You would need a separate, hidden text box on the form and bind its recordsource to the table and then in the AfterUpdate event of the other text box put:

Me.YourHiddenTextBoxName = Me.YourNotHiddenTextBoxName

 

[Munkyjay]

Still stuck

Thanks for such a quick reply Bob! I've tried the hidden text box as you suggested, but for some reason, when I go look in the table, the field is still blank.

 

[boblarson]

Did you make sure to set the controlsource of the hidden text box to the field you wanted saved? Also, did you put the code in the AfterUpdate of the correct text box?

 

[Munkyjay]

Still stuck...

Hi Bob

Yup, I put the after update code on the text box that is not hidden and I set the control source on the hidden text box. Would it make a difference that these two texts fields are in a subform?

 

[boblarson]

Hi Bob

Yup, I put the after update code on the text box that is not hidden and I set the control source on the hidden text box. Would it make a difference that these two texts fields are in a subform?

If both are on the same form, it shouldn't matter. I guess the other question would be can you post your database so we can see what's happening.

 

[Munkyjay]

File uploaded

Hi Bob

I've uploaded the file to show you what I've done.

From the main pages, if you click into the Offender Groups/Psychometrics section, then click on the tab for Pscychometrics you will see what I'm on about. In design view you will see that there is a text box set up to display HIGH RISK or LOW RISK depending on the score that it entered into the cell just before it. The hidden text box is called Text42 and is a little isolated from the other boxes (when viewed in design view).

Thanks again for your time on this and the really fast responses!