Field populated from derived equation. Help?

[Melon via AccessMonster.com]

If anyone could take a look at this, I could do with the help.

Im running into 2 problems with my db.

1) I have a members section in my db. If a member is selected as senior, all
fields apply to him, if a member is selected as junior, other fields don't
apply and I need to be able to hide them.
2) I have a date joined field, there is an expiry date field, which is date
joined + 365. On the form, I have a combo box looking up a table which stores
the values Active/Pending/Expired. If the expiry date is <365 I need it to
automatically show Active, If the expiry is > I need it to automatically show
Expired.

I realise this is a simple enough situation, but I can't seem to figure it
out. If anyone has a chance could they offer advice? Thanks all...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200606/1

[salad]

Melon via AccessMonster.com wrote:

If anyone could take a look at this, I could do with the help.

Im running into 2 problems with my db.

1) I have a members section in my db. If a member is selected as senior, all
fields apply to him, if a member is selected as junior, other fields don't
apply and I need to be able to hide them.
In your property sheet for a form, under Other, there's a row called
TAG. In it, you could put some sort of flag. Ex:
Sub SetCtl()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag > "" Then
ctl.Visible = (ctl.Tag = "Senior" And MemberType = "S")
End If
End If
Next
End Sub

Let's say MemberType is "J" or "S". If you leave all Tag fields that
need to be displayed for both j/s blank, they'll display. Put the word
Senior in the tag field for those that need to be visible/hidden.

You can call this in the OnCurrent event and the AfterUpdate event of
the field that sets the j/s status.


2) I have a date joined field, there is an expiry date field, which is date
joined + 365. On the form, I have a combo box looking up a table which stores
the values Active/Pending/Expired. If the expiry date is <365 I need it to
automatically show Active, If the expiry is > I need it to automatically show
Expired.
I suppose DateJoined is null if Pending. In the query, create a column
like this:
Status : IIF(IsNull(DateJoined),"Pending",_
IIF(DateJoined + 365 > date(),"Active",Expired"))

This will create a column called status. If joined is null it is
pending. If the expired date is greater than today, active else inactive.

I realise this is a simple enough situation, but I can't seem to figure it
out. If anyone has a chance could they offer advice? Thanks all...

[Melon via AccessMonster.com]

Hi Salad, Thanks for taking a look, Seems to be just what I need.

Status : IIF(IsNull(DateJoined),"Pending",_
IIF(DateJoined + 365 > date(),"Active",Expired"))

Not very experienced in Access... Are you suggesting that I base my 'Status'
field on a query? Can you elaborate a little more for me? Still very new to
all this,.... :(

--
Message posted via http://www.accessmonster.com

[salad]

Melon via AccessMonster.com wrote:

Hi Salad, Thanks for taking a look, Seems to be just what I need.

Status : IIF(IsNull(DateJoined),"Pending",_
IIF(DateJoined + 365 > date(),"Active",Expired"))

Not very experienced in Access... Are you suggesting that I base my 'Status'
field on a query? Can you elaborate a little more for me? Still very new to
all this,.... :(

When you create a query in the query builder you can create a calculated
field. It is 2 parts; column name separated by a colon then the
formula. Ex:
Test: IIF(1 = 2,"Equal","Not Equal")
If you click on View/SQL from the menu you'll see something like
IIF(1 = 2,"Equal","Not Equal") As Test, ...
in the SQL statement. You now have a column called Test along with the
rest of the columns. Create a query and use the example line I just
gave to get a feel for creating a calc'd column.