Calculated Field on a Form Problem

D

duckster

Registered User.
Local time
Yesterday, 21:20
Joined
Jul 17, 2004
Messages
78
Hi all, I have a Form which calculates how much to charge a client.

I have a "Fee" field, and a few calculated fields based on this fee. The calculated field I'm having problems with is the "Tax" Field.

Prior to July 1, 2006, i had no problems. The tax was basically " =[Fee]*.07 " in the "control source" under the properties of the field i created in the form.

After July 1, the tax just went down (in Canada) to 6%. I can just simply change the control source to =[Fee]*.06 ...however, when I do this, the form also changes all the clients before July 1 to a tax rate of 6% as well...I'm not good with coding, and an not sure how to make the calculated field in the form to change the tax rate to 6% only if the client's contact date is after July 1, 2006. ContactDate is also another field in this form.

Help is much appreciated.
 
Hello:
The following does what you ask. You need to put it under what ever event fires your tax calculation. This assumes that you have a transaction date of some sorts.
'
Regards
Mark
'Private Sub Command1_Click()
Dim TaxRate As Double
If txtTransactionDate < #7/1/2006# Then
TaxRate = 0.07
Else
TaxRate = 0.06
End If
Your code here
'
End Sub
 
Thanks, but it doesn't seem to be working.

Can I give you a few more details? I have a "Subtotal" field On the form, and also a tax field called "GST" (stands for goods and services tax). The GST is the tax that I am having problems with as described above. It is basically an unbound Text Box that I added, and I've simply been inputting into the properties' "control source" of the text box:

=[Subtotal]*.07

The result gives me the Total amount. For example, if I input $1 into the Subtotal field, the GST field will automatically have $0.07. I tried inputting your coding above into the control source, and it gives me an invalid syntax. Am I entering the coding above in the wrong area? Should it be in the control source of the field? Thanks again!
 
Have a look at the attached sample, it this what you want?
 

Attachments

Create a "Tax" table with your Tax Rates, ValidFrom Date plus ValidToDate.

Change your unbound tax field into a bound field, based on a list box.
Set up your list box so that it only shows "valid" taxes.
Valid as in taxes whereas the ValidToDate is null.

You'd have to add a "Tax" column to the underlying table of your form and run a mass update to populate your table with a TaxID representing the appropiate Tax Rate.

RV
 
You should store the Tax rate along with other details at the point of sale
 
Thanks for the input and help guys! I used the "iif" function found in ancentry's example and put it directly in the properties --> control source of the GST field in my form (the GST field is completely a calculated field which is not in any underlying table)...worked perfectly.

Thanks again

Regards,

Duckster
 
GSTCal file

Can you PLEASE do another GSTCal file for 0.05 GST as of 01/jan/08
 
Ansentry...great GSTCal file..can you do another one for latest GST change (sorry not sure how to reply directly to you..tried the quote button and quick reply??

Sherry
 
You must log in or register to reply here.

Similar threads

D
After update
Replies
17
Views
754
Pat Hartman
P
D
Subform requery is starting a new record
Replies
4
Views
527
dullster
D
K
Design/Structure Ideas for Data Entry form with multiple levels
Replies
18
Views
815
LarryE
LarryE
D
Solved Creating a query with 1 table and 4 queries adds a new record
Replies
10
Views
465
Pat Hartman
P
D
Solved Query showing all records instead of just the ones that meet the criteria
Replies
3
Views
312
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom