Is it possible to compare a string and a field name

[thebrat26]

I have the following tables: Membership, Subscriptions, OfficeUseOnly. The
Office Table contains a Year, Year To and From [financial year] and the other
fields are the 5 membership types (regular, student etc) and these 5 fields
contain the different amount each type of member has to pay.

I want to create a form whereby on entering the financial year and the
membership type the correct amount is automatically inserted into the
'Amount' field:

FinancialYear - in current form = OfficeUseOnly SubsFinYear
and then if MemberType = 'Student' find Student field in that record and
insert the number into 'Amount'
if 'Regular' find Regular field and insert that number into 'Amount' etc.

I'm trying to avoid anyone having to do anything more strenuous than insert
a new record in OfficeUseOnly when subscription amounts change - beginning of
the new financial year or due to general changes in circumstances.

Using Access 2007. I've been searching for help for weeks and read post
after post and I'm even more confused now than when I started so would
appreciate any help.

 

[Jeanette Cunningham]

You could use a combo where after the user chooses the year, and another
combo for the membership type, the amount is automatically put into the
amount control on the form.

Where you say

these 5 fields
contain the different amount each type of member has to pay.

I hope you don't have a separate field for each type of membership.

There needs to be a table called Membership

It will have a primary key field called MembershipID
and another field called Membership.

In the table you list all the different memberships in the membership field.

Post back if you have more questions.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have the following tables: Membership, Subscriptions, OfficeUseOnly. The
Office Table contains a Year, Year To and From [financial year] and the
other
fields are the 5 membership types (regular, student etc) and these 5
fields
contain the different amount each type of member has to pay.

I want to create a form whereby on entering the financial year and the
membership type the correct amount is automatically inserted into the
'Amount' field:

FinancialYear - in current form = OfficeUseOnly SubsFinYear
and then if MemberType = 'Student' find Student field in that record and
insert the number into 'Amount'
if 'Regular' find Regular field and insert that number into 'Amount' etc.

I'm trying to avoid anyone having to do anything more strenuous than
insert
a new record in OfficeUseOnly when subscription amounts change - beginning
of
the new financial year or due to general changes in circumstances.

Using Access 2007. I've been searching for help for weeks and read post
after post and I'm even more confused now than when I started so would
appreciate any help.

 

[John W. Vinson]

I have the following tables: Membership, Subscriptions, OfficeUseOnly. The
Office Table contains a Year, Year To and From [financial year] and the other
fields are the 5 membership types (regular, student etc) and these 5 fields
contain the different amount each type of member has to pay.

Then your table design is incorrect. A proper design would have five RECORDS
for each time period, with an additional field for MembershipType.

I want to create a form whereby on entering the financial year and the
membership type the correct amount is automatically inserted into the
'Amount' field:

FinancialYear - in current form = OfficeUseOnly SubsFinYear
and then if MemberType = 'Student' find Student field in that record and
insert the number into 'Amount'
if 'Regular' find Regular field and insert that number into 'Amount' etc.

I'm trying to avoid anyone having to do anything more strenuous than insert
a new record in OfficeUseOnly when subscription amounts change - beginning of
the new financial year or due to general changes in circumstances.

Perfectly easy if you have a tall-thin table that would let you select a
record; if you have to dynamically look into five different fields you'll need
some VBA code or a complex macro.

Using Access 2007. I've been searching for help for weeks and read post
after post and I'm even more confused now than when I started so would
appreciate any help.

I'd suggest that the Office table have fields:

SubsFinYear (Integer)
MemberType (Text)
Amount

You could then easily look up the specific record using the year and the type
combo boxes on your form as criteria.

 

[thebrat26]

Jeanette hello and thank you for responding.

I have a combo box for the Financial year - picks it up from OfficeUseOnly
no problem.

OfficialUseOnly contains:

FinancialYear, Student, Regular, Volunteer, Exec and Other - each containing
the amount of the subscription.

I originally had a table MemberType which contained 2 fields Type (of
Membership) and its corresponding Amount - the problem is that if
Subscriptions change either during a financial year or at the beginning of
the new financial year (and remember, initially some of the data is being
input retrospectively) it would mean creating tables for each year (I guess)
and 'calling to' the correct table year when inputting member details. This
also means that the User (it won't be me) will have to know how to do this
each subsequent year hence the reasoning behind trying to create only one
table for the User to update when necessary.

You say “ You could use a combo where after the user chooses the year, and
another combo for the membership type, the amount is automatically put into
the amount control on the form.â€

Not sure how this is done ‘automatically’ could you explain further?

Hope that makes sense ....

Thank you.

You could use a combo where after the user chooses the year, and another
combo for the membership type, the amount is automatically put into the
amount control on the form.

Where you say

these 5 fields
contain the different amount each type of member has to pay.

I hope you don't have a separate field for each type of membership.

There needs to be a table called Membership

It will have a primary key field called MembershipID
and another field called Membership.

In the table you list all the different memberships in the membership field.

Post back if you have more questions.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have the following tables: Membership, Subscriptions, OfficeUseOnly. The
Office Table contains a Year, Year To and From [financial year] and the
other
fields are the 5 membership types (regular, student etc) and these 5
fields
contain the different amount each type of member has to pay.

I want to create a form whereby on entering the financial year and the
membership type the correct amount is automatically inserted into the
'Amount' field:

FinancialYear - in current form = OfficeUseOnly SubsFinYear
and then if MemberType = 'Student' find Student field in that record and
insert the number into 'Amount'
if 'Regular' find Regular field and insert that number into 'Amount' etc.

I'm trying to avoid anyone having to do anything more strenuous than
insert
a new record in OfficeUseOnly when subscription amounts change - beginning
of
the new financial year or due to general changes in circumstances.

Using Access 2007. I've been searching for help for weeks and read post
after post and I'm even more confused now than when I started so would
appreciate any help.

.

 

[thebrat26]

Hello John, Thank you very much for responding - apologies to everyone if
this appears twice as my computer crashed just as I hit the Post button and
it was not clear from the resulting screen whether or not my reply had been
sent!

As I explained to Jeanette

I have a combo box for the Financial year - picks it up from OfficeUseOnly
no problem.

OfficialUseOnly fields are:

FinancialYear, Student, Regular, Volunteer, Exec and Other

- the last 5 each contain the amount of their respective memberships.

I originally had a table MemberType which contained 2 fields:

‘Type’ - of Membership - and its corresponding ‘Amount’

The problem is that if subscriptions change either during a financial year
or at the beginning of the new financial year (and remember, initially some
of the data is being input retrospectively so historical data would have to
be available) it would mean creating tables for each year (I guess) and
'calling to' the correct table year when inputting member details –
subscriptions are renewable annually.

Updating this will be a problem for a data input clerk or volunteer each
subsequent year hence the reasoning behind trying to create only one table
with historical subscriptions so that Users can add future ones when
required.

I had tried some experimenting with the DLookUp command but kept getting
error messages. I thought this might be with the syntax so omitted any
criteria:

=DLookUp("[Student]","[OFFICEUSEONLY]")

actually gave me the correct amount in the first Financial year record

=DLookUp("[FinancialYear]","[OFFICEUSEONLY]")

gave me the first year in the first Financial year record

Is it possible to add a criteria to read the input in the form: MemberSubs
field: FinancialYear AND MemberType and then get it to look up the correct
year record AND reading the string in MemberType find the corresponding field
name and using the data in that particular field?

Is there another way?

Thank you.

I have the following tables: Membership, Subscriptions, OfficeUseOnly. The
Office Table contains a Year, Year To and From [financial year] and the other
fields are the 5 membership types (regular, student etc) and these 5 fields
contain the different amount each type of member has to pay.

Then your table design is incorrect. A proper design would have five RECORDS
for each time period, with an additional field for MembershipType.

I want to create a form whereby on entering the financial year and the
membership type the correct amount is automatically inserted into the
'Amount' field:

FinancialYear - in current form = OfficeUseOnly SubsFinYear
and then if MemberType = 'Student' find Student field in that record and
insert the number into 'Amount'
if 'Regular' find Regular field and insert that number into 'Amount' etc.

I'm trying to avoid anyone having to do anything more strenuous than insert
a new record in OfficeUseOnly when subscription amounts change - beginning of
the new financial year or due to general changes in circumstances.

Perfectly easy if you have a tall-thin table that would let you select a
record; if you have to dynamically look into five different fields you'll need
some VBA code or a complex macro.

Using Access 2007. I've been searching for help for weeks and read post
after post and I'm even more confused now than when I started so would
appreciate any help.

I'd suggest that the Office table have fields:

SubsFinYear (Integer)
MemberType (Text)
Amount

You could then easily look up the specific record using the year and the type
combo boxes on your form as criteria.

 

[John W. Vinson]

I originally had a table MemberType which contained 2 fields:

‘Type’ - of Membership - and its corresponding ‘Amount’

The problem is that if subscriptions change either during a financial year
or at the beginning of the new financial year (and remember, initially some
of the data is being input retrospectively so historical data would have to
be available) it would mean creating tables for each year (I guess) and
'calling to' the correct table year when inputting member details –
subscriptions are renewable annually.

Your table should have THREE fields: Type, Amount, and EffectiveDate.

That would let you record the history (one record every time the rate
changes), and you could use a simple Top Values query to retrieve the amount
as of any date.

 

[Jeanette Cunningham]

And yes you will need a data entry form where someone can enter the new fees
for the new year, half year or quarter.
You probably also need a form where someone can enter new membership types
in the future.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hello John, Thank you very much for responding - apologies to everyone if
this appears twice as my computer crashed just as I hit the Post button
and
it was not clear from the resulting screen whether or not my reply had
been
sent!

As I explained to Jeanette

I have a combo box for the Financial year - picks it up from OfficeUseOnly
no problem.

OfficialUseOnly fields are:

FinancialYear, Student, Regular, Volunteer, Exec and Other

- the last 5 each contain the amount of their respective memberships.

I originally had a table MemberType which contained 2 fields:

'Type' - of Membership - and its corresponding 'Amount'

The problem is that if subscriptions change either during a financial year
or at the beginning of the new financial year (and remember, initially
some
of the data is being input retrospectively so historical data would have
to
be available) it would mean creating tables for each year (I guess) and
'calling to' the correct table year when inputting member details -
subscriptions are renewable annually.

Updating this will be a problem for a data input clerk or volunteer each
subsequent year hence the reasoning behind trying to create only one table
with historical subscriptions so that Users can add future ones when
required.

I had tried some experimenting with the DLookUp command but kept getting
error messages. I thought this might be with the syntax so omitted any
criteria:

=DLookUp("[Student]","[OFFICEUSEONLY]")

actually gave me the correct amount in the first Financial year record

=DLookUp("[FinancialYear]","[OFFICEUSEONLY]")

gave me the first year in the first Financial year record

Is it possible to add a criteria to read the input in the form: MemberSubs
field: FinancialYear AND MemberType and then get it to look up the correct
year record AND reading the string in MemberType find the corresponding
field
name and using the data in that particular field?

Is there another way?

Thank you.

I have the following tables: Membership, Subscriptions, OfficeUseOnly.
The
Office Table contains a Year, Year To and From [financial year] and the
other
fields are the 5 membership types (regular, student etc) and these 5
fields
contain the different amount each type of member has to pay.

Then your table design is incorrect. A proper design would have five
RECORDS
for each time period, with an additional field for MembershipType.

I want to create a form whereby on entering the financial year and the
membership type the correct amount is automatically inserted into the
'Amount' field:

FinancialYear - in current form = OfficeUseOnly SubsFinYear
and then if MemberType = 'Student' find Student field in that record
and
insert the number into 'Amount'
if 'Regular' find Regular field and insert that number into 'Amount'
etc.

I'm trying to avoid anyone having to do anything more strenuous than
insert
a new record in OfficeUseOnly when subscription amounts change -
beginning of
the new financial year or due to general changes in circumstances.

Perfectly easy if you have a tall-thin table that would let you select a
record; if you have to dynamically look into five different fields you'll
need
some VBA code or a complex macro.

Using Access 2007. I've been searching for help for weeks and read post
after post and I'm even more confused now than when I started so would
appreciate any help.

I'd suggest that the Office table have fields:

SubsFinYear (Integer)
MemberType (Text)
Amount

You could then easily look up the specific record using the year and the
type
combo boxes on your form as criteria.

 

[Jeanette Cunningham]

John's suggestion of combining the member type, the financial year and
amount will work better for you.

The query for the combo's row source will contain all 3 fields as well as
the Primary key of that table.
After user chooses from the combo, you use code to set the value for amount
and member type.

Something like this
Private Sub ComboName_AfterUpdate
If Not IsNull(Me.ComboName) Then
Me.Amount = Me.ComboName.Column(2)
Me.MemberType = Me.ComboName.Column(1)
End If
End Sub

Replace the names for objects above with the names used on your form.

Combos number their columns starting with 0 for column 1.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette hello and thank you for responding.

I have a combo box for the Financial year - picks it up from OfficeUseOnly
no problem.

OfficialUseOnly contains:

FinancialYear, Student, Regular, Volunteer, Exec and Other - each
containing
the amount of the subscription.

I originally had a table MemberType which contained 2 fields Type (of
Membership) and its corresponding Amount - the problem is that if
Subscriptions change either during a financial year or at the beginning of
the new financial year (and remember, initially some of the data is being
input retrospectively) it would mean creating tables for each year (I
guess)
and 'calling to' the correct table year when inputting member details.
This
also means that the User (it won't be me) will have to know how to do this
each subsequent year hence the reasoning behind trying to create only one
table for the User to update when necessary.

You say " You could use a combo where after the user chooses the year,
and
another combo for the membership type, the amount is automatically put
into
the amount control on the form."

Not sure how this is done 'automatically' could you explain further?

Hope that makes sense ....

Thank you.

You could use a combo where after the user chooses the year, and another
combo for the membership type, the amount is automatically put into the
amount control on the form.

Where you say

these 5 fields
contain the different amount each type of member has to pay.

I hope you don't have a separate field for each type of membership.

There needs to be a table called Membership

It will have a primary key field called MembershipID
and another field called Membership.

In the table you list all the different memberships in the membership
field.

Post back if you have more questions.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have the following tables: Membership, Subscriptions, OfficeUseOnly.
The
Office Table contains a Year, Year To and From [financial year] and the
other
fields are the 5 membership types (regular, student etc) and these 5
fields
contain the different amount each type of member has to pay.

I want to create a form whereby on entering the financial year and the
membership type the correct amount is automatically inserted into the
'Amount' field:

FinancialYear - in current form = OfficeUseOnly SubsFinYear
and then if MemberType = 'Student' find Student field in that record
and
insert the number into 'Amount'
if 'Regular' find Regular field and insert that number into 'Amount'
etc.

I'm trying to avoid anyone having to do anything more strenuous than
insert
a new record in OfficeUseOnly when subscription amounts change -
beginning
of
the new financial year or due to general changes in circumstances.

Using Access 2007. I've been searching for help for weeks and read
post
after post and I'm even more confused now than when I started so would
appreciate any help.

.

 

[thebrat26]

Thank you John.

T

I have the following tables: Membership, Subscriptions, OfficeUseOnly. The
Office Table contains a Year, Year To and From [financial year] and the other
fields are the 5 membership types (regular, student etc) and these 5 fields
contain the different amount each type of member has to pay.

Then your table design is incorrect. A proper design would have five RECORDS
for each time period, with an additional field for MembershipType.

I want to create a form whereby on entering the financial year and the
membership type the correct amount is automatically inserted into the
'Amount' field:

FinancialYear - in current form = OfficeUseOnly SubsFinYear
and then if MemberType = 'Student' find Student field in that record and
insert the number into 'Amount'
if 'Regular' find Regular field and insert that number into 'Amount' etc.

I'm trying to avoid anyone having to do anything more strenuous than insert
a new record in OfficeUseOnly when subscription amounts change - beginning of
the new financial year or due to general changes in circumstances.

Perfectly easy if you have a tall-thin table that would let you select a
record; if you have to dynamically look into five different fields you'll need
some VBA code or a complex macro.

Using Access 2007. I've been searching for help for weeks and read post
after post and I'm even more confused now than when I started so would
appreciate any help.

I'd suggest that the Office table have fields:

SubsFinYear (Integer)
MemberType (Text)
Amount

You could then easily look up the specific record using the year and the type
combo boxes on your form as criteria.

 

[thebrat26]

Thank you Jeanette.

T

And yes you will need a data entry form where someone can enter the new fees
for the new year, half year or quarter.
You probably also need a form where someone can enter new membership types
in the future.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hello John, Thank you very much for responding - apologies to everyone if
this appears twice as my computer crashed just as I hit the Post button
and
it was not clear from the resulting screen whether or not my reply had
been
sent!

As I explained to Jeanette

I have a combo box for the Financial year - picks it up from OfficeUseOnly
no problem.

OfficialUseOnly fields are:

FinancialYear, Student, Regular, Volunteer, Exec and Other

- the last 5 each contain the amount of their respective memberships.

I originally had a table MemberType which contained 2 fields:

'Type' - of Membership - and its corresponding 'Amount'

The problem is that if subscriptions change either during a financial year
or at the beginning of the new financial year (and remember, initially
some
of the data is being input retrospectively so historical data would have
to
be available) it would mean creating tables for each year (I guess) and
'calling to' the correct table year when inputting member details -
subscriptions are renewable annually.

Updating this will be a problem for a data input clerk or volunteer each
subsequent year hence the reasoning behind trying to create only one table
with historical subscriptions so that Users can add future ones when
required.

I had tried some experimenting with the DLookUp command but kept getting
error messages. I thought this might be with the syntax so omitted any
criteria:

=DLookUp("[Student]","[OFFICEUSEONLY]")

actually gave me the correct amount in the first Financial year record

=DLookUp("[FinancialYear]","[OFFICEUSEONLY]")

gave me the first year in the first Financial year record

Is it possible to add a criteria to read the input in the form: MemberSubs
field: FinancialYear AND MemberType and then get it to look up the correct
year record AND reading the string in MemberType find the corresponding
field
name and using the data in that particular field?

Is there another way?

Thank you.

On Thu, 21 Jan 2010 15:08:01 -0800, thebrat26

I have the following tables: Membership, Subscriptions, OfficeUseOnly.
The
Office Table contains a Year, Year To and From [financial year] and the
other
fields are the 5 membership types (regular, student etc) and these 5
fields
contain the different amount each type of member has to pay.

Then your table design is incorrect. A proper design would have five
RECORDS
for each time period, with an additional field for MembershipType.

I want to create a form whereby on entering the financial year and the
membership type the correct amount is automatically inserted into the
'Amount' field:

FinancialYear - in current form = OfficeUseOnly SubsFinYear
and then if MemberType = 'Student' find Student field in that record
and
insert the number into 'Amount'
if 'Regular' find Regular field and insert that number into 'Amount'
etc.

I'm trying to avoid anyone having to do anything more strenuous than
insert
a new record in OfficeUseOnly when subscription amounts change -
beginning of
the new financial year or due to general changes in circumstances.

Perfectly easy if you have a tall-thin table that would let you select a
record; if you have to dynamically look into five different fields you'll
need
some VBA code or a complex macro.

Using Access 2007. I've been searching for help for weeks and read post
after post and I'm even more confused now than when I started so would
appreciate any help.

I'd suggest that the Office table have fields:

SubsFinYear (Integer)
MemberType (Text)
Amount

You could then easily look up the specific record using the year and the
type
combo boxes on your form as criteria.

.