Is it possible to compare a string and a field name

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.
0
Utf
1/21/2010 11:08:01 PM
access.gettingstarted 618 articles. 1 followers. Follow

9 Replies
1249 Views

Similar Articles

[PageSpeed] 10

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



"thebrat26" <thebrat26@discussions.microsoft.com> wrote in message 
news:05FCB9E1-23F0-492D-A7CA-7964E5EA1C91@microsoft.com...
>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. 


0
Jeanette
1/21/2010 11:53:11 PM
On Thu, 21 Jan 2010 15:08:01 -0800, thebrat26
<thebrat26@discussions.microsoft.com> wrote:

>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 [MVP]
0
John
1/21/2010 11:55:47 PM
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.


"Jeanette Cunningham" wrote:

> 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
> 
> 
> 
> "thebrat26" <thebrat26@discussions.microsoft.com> wrote in message 
> news:05FCB9E1-23F0-492D-A7CA-7964E5EA1C91@microsoft.com...
> >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. 
> 
> 
> .
> 
0
Utf
1/22/2010 2:52:01 PM
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.



"John W. Vinson" wrote:

> On Thu, 21 Jan 2010 15:08:01 -0800, thebrat26
> <thebrat26@discussions.microsoft.com> wrote:
> 
> >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 [MVP]
> .
> 
0
Utf
1/22/2010 5:20:01 PM
On Fri, 22 Jan 2010 09:20:01 -0800, thebrat26
<thebrat26@discussions.microsoft.com> wrote:

>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.
-- 

             John W. Vinson [MVP]
0
John
1/22/2010 6:23:28 PM
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


"thebrat26" <thebrat26@discussions.microsoft.com> wrote in message 
news:675B0BFD-4D1C-41BE-AC30-42589091A170@microsoft.com...
> 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.
>
>
>
> "John W. Vinson" wrote:
>
>> On Thu, 21 Jan 2010 15:08:01 -0800, thebrat26
>> <thebrat26@discussions.microsoft.com> wrote:
>>
>> >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 [MVP]
>> .
>> 


0
Jeanette
1/22/2010 9:21:00 PM
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


"thebrat26" <thebrat26@discussions.microsoft.com> wrote in message 
news:947AFB8F-219E-483C-B4AD-F376DF85C656@microsoft.com...
> 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.
>
>
> "Jeanette Cunningham" wrote:
>
>> 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
>>
>>
>>
>> "thebrat26" <thebrat26@discussions.microsoft.com> wrote in message
>> news:05FCB9E1-23F0-492D-A7CA-7964E5EA1C91@microsoft.com...
>> >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.
>>
>>
>> .
>> 


0
Jeanette
1/22/2010 9:24:52 PM
Thank you John.

T

"John W. Vinson" wrote:

> On Thu, 21 Jan 2010 15:08:01 -0800, thebrat26
> <thebrat26@discussions.microsoft.com> wrote:
> 
> >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 [MVP]
> .
> 
0
Utf
1/29/2010 4:10:14 PM
Thank you Jeanette.

T

"Jeanette Cunningham" wrote:

> 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
> 
> 
> "thebrat26" <thebrat26@discussions.microsoft.com> wrote in message 
> news:675B0BFD-4D1C-41BE-AC30-42589091A170@microsoft.com...
> > 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.
> >
> >
> >
> > "John W. Vinson" wrote:
> >
> >> On Thu, 21 Jan 2010 15:08:01 -0800, thebrat26
> >> <thebrat26@discussions.microsoft.com> wrote:
> >>
> >> >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 [MVP]
> >> .
> >> 
> 
> 
> .
> 
0
Utf
1/29/2010 4:10:19 PM
Reply:

Similar Artilces:

Help with field translation with Scribe
Does anyone have a uself guide for using scribe to translate fields from Salesforce to the integer codes used in crm. I have a good amount of our data migrated but I'm stuck on theses translation. I used their setup for the user mapping and it either does not work or they left steps out of the guides that they have. If anyone has an alternative to using scribe to get this done I'm open to that as well. Specifically what I need is a way to find value "A" in salesforce and insert value "01" in CRM. Its on the replace step I need halp with I got the field matc...

Files opening with different file names
Good afternoon/morning. Having a strange issue with Excel at present. If a file is sitting on my desktop, called invoices.xls, I click on it to open, and it opens as invoices1 The same happens if I 'explore' to the file. However, if I open excel and then click 'file''open' then click on the file, it opens as invoices.xls. When trying to save invoices1 it will save as invoices1.xls, then if I open this it will open it as invoices11 I'm running Windows 2000 with Excel 2000. Any help/guesswork would be appreciated! It sounds like the default behavior when you ...

Is it possible
I would like to know is it possible to make million dollar in the internet, if you have any suggestions, write on my forumhttp://isitpossible.truemillion.comMark...

Personal Folders in OWA
Hello, I have just enabled OWA on our Exchange 2003 SP2 through ISA Server 2004. Is there any way to access personal folders in OWA? Thanks in advance. If by personal folders you mean .pst files, the answer is NO! Sorry. C C wrote: > Hello, > > I have just enabled OWA on our Exchange 2003 SP2 > through ISA Server 2004. Is there any way to > access personal folders in OWA? > > Thanks in advance. > > "C C" <someone@atsbcglobal.net> wrote: >I have just enabled OWA on our Exchange 2003 SP2 >through ISA Server 2004. Is there any way...

Memo Field 09-25-07
I have a memo field in my table and the user can update this field from a form. The problem is that when they input a lot of data at one time, it turns the last lines to garbage and/or truncates the last lines. I would appreicate any suggestions as to why this is happening. Is there something in the text box property I should set so access knows this is a memo field? ( grasping at straws) Thank you in advance for your help. Post the SQL statement of the form's Record Source. Identify the field that is the memo data type. -- Ken Snell <MS ACCESS MVP> "Sha...

Adding a Secondary Owner Field in Contact form
Hello Everyone!! I would like to know if someone can help me with this... Is there any way to create a copy of the ownerid field to have more than one possible owner to each contact??? I have added a new field called: owner_02 to Contacts entity but it is a nvarchar so the user can add any text and it should be better if it is the same type than ownerid field. Any help will be appreciated!! Thanks and regards, Pablo Hi Pablo, There is no easy way to achieve this. You could add a separate entity which gets filled automatically based on callouts on Systemuser. Then create a lookup t...

How do l get the sheet name from the sheet code name?
I am sure this should be pretty simple but so far the answer has eluded me. I am doing a project which involves retrieving data about the vbe. Thanks to Chip Pearson's excellent code examples so far it has gone fine. However l am having trouble retrieving the worksheet name that appears on the worksheet tabs. So using Chip's code l can retrieve the sheet code names, Sheet1, Sheet2 etc and list them as required. Lets say the the tab name that appears to the users is "A" for Sheet1 and "B" for Sheet2 So lets say that l now have Sheet1 in Cell(1,1) of a sh...

Losing Named Ranges
I manage a workbook that runs about 3.5 MB that has several worksheets and several named ranges. I use the named ranges mainly in VLOOKUP formulas (e.g. =VLOOKUP(A1, NamedRange, 1, FALSE)). A few of these named ranges refer to entire worksheets, and I'm finding that periodically those ranges (that refer to entire worksheets) are disappearing, causing the VLOOKUP formula to generate a #NAME error. This happens most frequently to other associates that receive the latest version of the workbook via email, but I can't seem to find a pattern, nor can I determine the reason this is occurin...

econnect connection string
Hi! I am new to C# and eConnect. I was a dexterity developer before but now we switched to eConnect to integrate with Great Plains. Anyway, with a little knowledge of eConnect and C#, I am having a problem with the requester. I am using eConnect 9 by the way. I need to find a way to know the connection string that I will pass to the econnect_requester method (correct me if im wrong with the name of the method, i am not at the office). Currently, I just hardcoded the connection string to be passed to the requester. But I need to know exactly how to get the connectiong string. I know durin...

Downloading Statements Directly Not Possible
I have Money 2002 installed on an Win XP Pro SP1 machine. Something happened and I probably did it, that causes the statement downloads not to go directly to the account. Rather they want to be saved in a file and then opened into Money. I then have to delete the download. I have tried several things in order to fix this: 1. I verified the file associations of OFC, OFX were correct. They were. 2. Lowered all my security settings in IE. Even put banks into Trusted site zone. No go. 3. Made new accounts. Didn't help. 4. My IE cache is large (500MB) and regularly cleaned out. 5. I made s...

How do I enter a decimal in a string of numbers in Excel? Example.
Please post your question in the BODY of the message, not the subject line, and clarify with an example of the initial data and what you want to end up with. If it's to translate 123 to 1.23, you divide by 100. On Wed, 9 Feb 2005 15:03:07 -0800, Joy <Joy@discussions.microsoft.com> wrote: ...

is this possible? reload a form that is based on a query with new query that is defined in the form.
I have a basic form. The form is based on a query, so when I open the form, a pop comes up where I enter in what I want to query. So instead of 30,0000 records, I get 1-10 records. on the form, I have a text box. I want to be able to enter in what I want to query next and afterupdate have the recordset/form reload with that query instead of my original qeury. I figure this has to be done with VB. I am just starting to learn some VB but I am lost. Any info would be great, thank you. It does. You need to apply the new query or table name, or the sql string to the forms recordsource, then...

Changing Content of mapped Fields
Hello, is there a way to change the content of mapped fields? I have got an custom entitie which is mpped with company and offer. If I creat my entitie from "company" the mapped field (companynumber) is set its value as it should be, in my costom entitie field "customernumber". Creating a new record from my custom entitie and select via mapped lookup field a company the mapped entitie field remains blank. Also having an other field mapped to an entitie. By changing data in the source field the mapped field won't change (update) either. How could the solution loo...

Blank user name for syncronization.
Hey everyone: My palm 505 and my Outlook 2002 are not synchronizing at home. It works just fine at the office. When I push the sync button the compter screen does not come up with a user name. This is getting a bit tiresome, can someone please help? JMAC out ...

Mapping of ID fields
I created an custom entity with parent relationship from Opportunity. I can map Name and other fields from opportunity to this new entity fields, but what is the right way to map the Account (potential customer) from Opportunity to custom field in the new custom entity? Thanks Feek ...

Possible to detect changes to an XmlDocument object?
If I instantiate an XmlDocument object, the load a file through the Load method, is there a way, later on in execution, to easily determine if the XmlDocument object has changed from the original version loaded? I'd like to be able to check a condition to see if the document in memory has been editted (i.e., nodes added, attributes changed, etc.) so that my user can decide whether or not to commit changes to the disk file before moving to another file. Will I have to resort to re-loading the file, and comparing OuterXml properties? Gabe Gabe Covert wrote: > I'd like t...

Blocking fields to avoid use in formulas
Is there any possibility to block the particular field in order to avoid users to use invalid fields in formulas ? regards Peter You can lock and unlock cells (then protect the worksheet) so that users can't change certain cells. But if you allow them to type a formula into a cell, then I can't think of anyway of stopping them from pointing at A1 when A1 shouldn't be used. Is that what you meant? If it is, maybe someone else has a way... If it's not, maybe you should rephrase your question. Piotr wrote: > > Is there any possibility to block the particular field i...

how to extract decimal numbers from alphanumeric strings in Excel
How do I extract decimal numbers from alphanumeric strings to perform calculations on the number, eg 25.1 km or 100 m. Try... =LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) ....where A1 contains your alpha-numeric string. Hope this helps! In article <2D34CDFB-7DD3-4678-AF5B-73A030526028@microsoft.com>, "Old Tone" <Old Tone@discussions.microsoft.com> wrote: > How do I extract decimal numbers from alphanumeric strings to perform > calculations on the number, eg 25.1 km or 100 m. Hi, Use the formula, =1*LEFT(A1,FIND(&quo...

Is it possible to compare a string and a field name
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 ...

creating join with only the first 4 characters of a field
Anyone can help in creating this join in designer? the best way to join on just 4 characters is to do it in the SQL view. If you want to do it in the query design view (the grid). You can build a query on the first table with the calculated field Field: JoinThis: Left(SomeField,4) Save that query Now build a query on the second table doing the same thing Now build a query with the two saved queries as the sources and join on the two calculated fields. Alternative Build the query on the two tables and join on the fields that you need. Switch to SQL view and find the clause TableA INN...

how to stop my name apearing in text when editing forward emails
I am using Outlook 2003 My full name appears (like Auto-Correct) in the text of an email whenever I edit it prior to forwarding. This means that I have to go back and delete it (Alt + backspace). How can I stop this happening? "Judge_julz" <Judge_julz@discussions.microsoft.com> wrote in message news:E7B0CF69-5760-41EE-88C9-3DB9FB6D33E1@microsoft.com... >I am using Outlook 2003 > My full name appears (like Auto-Correct) in the text of an email whenever > I > edit it prior to forwarding. This means that I have to go back and delete > it > (Alt + backspac...

Not all required fields have been entered
I am getting this error everytime I am trying to enter a new sales order or purchase order in a new data base. Is there any way I can find which fields are not being populated? I have gone over the installation at least 4 times and I can't find anything missing. The funny thing is that the sample database has the same error. Is there a trace facility in GP? -- Gino S. GPK Computers Yes, there is a trace facility in GP. The name of the file is called DEXSQL.LOG. In install folder, in Dex.ini file modify the following from FALSE to TRUE: SQLLOGSQLSTMT=TRUE SQLLOGODBCMESSAGES=TRUE SQ...

pictures in various shapes
Is it possible to insert a picture and make it into a circle or an oval or various other shapes than just squares and rectangles? Thank you ahead of time. with a graphics editor -- Rob Giordano Microsoft MVP - FrontPage "Stephanie" <Stephanie@discussions.microsoft.com> wrote in message news:014D0D06-15F9-4D92-8D93-64FBD3A9CA32@microsoft.com... | Is it possible to insert a picture and make it into a circle or an oval or | various other shapes than just squares and rectangles? Thank you ahead of | time. | | Stephanie wrote: > Is it possible to insert a pict...

Is this possible
I want to make a list of the files in several different folders. Is it possible to somehow "Copy and paste" just the filenames into a list, in either Word or Excel or Wordperfect. I am just trying to sort the files in several different folders to weed out duplicates. There has got to be an easier way then typing them all or screen printing the lists and manually weeding them out. Any suggestions. Frustrated... Hans, Here is some code that will start in a directory and list all files in that and any sub directories. It uses recursion to go through all the subs. Just ch...

Madatory Field
Dear All, Is it possibel to make a field mandatory for a user? If through javascript is there a generic format available It is a property of the attribute (field) itself. Go to the attributes and change it there. Regards, Robert "Faiz Amir" wrote: > Dear All, > Is it possibel to make a field mandatory for a user? If through javascript > is there a generic format available You have mentioned it to make it mandatory for all users. I want to make it madatory for only one user. "Robert Rybaric" wrote: > It is a property of the attribute (field) itself...