MS ACCESS 2003 Question

M

Mack

Big Geek
Hi,
I am trying to make a project for school which requires the use of access 2003. I have made the tables in the database but don't know how to do calculate fields.
Here is an example. The table is called invoices and has the following fields

Invoice_Number, Amount_Paid, Amound_Due.

i want a person to enter Amount_Paid and the Amount_Due to be calculated automatically. For example lets say

Amount_Paid = 0
Amount_due = 100

When the person enters 50 in Amount_Paid the fields become

Amount_Paid = 50
Amount_Due = 50

Also later when he enters 50 again in Amount_Paid the fields Become

Amount_Paid = 100
Amount_Due = 0

How can such a procedure be implemented in Access?

I would really appreciate if somebody could help me in this,...

Thank u
 
Can't tell you right away, but for my school Access project, a custom function I wrote was held in an Access module. but vb programming knowledge is required, and I'm not all too familiar with Access after all these months.
 
having never used Access 2003 before i could be wrong, but in Access 95 / 97 you could either use VB or create a query (to create a custom view of the data of manipulate the data on a from, you could also use a macro, but with Access 2003 i could be wrong, cos my memory is getting worse as i get older, so this could be wrong, in query enter the source field
Code: 
Amount_Due
then in the expresion field i think you enter [Amount_Due] - [Amount_Paid] then you need a destitation field, so i think you would need the expresion
Code: 
 [Amount_Due] - [Amount_Paid] = [Amount_Owed]

but don't take my word for it, cos i'm 5 - 10 years out of date, but it's something like that in Access 95 / 97
 
ok I will try that and give u guys the feedback,.. In the mean while any other ideas are also welcome,..
thanks regards
Mack
 
Where as I have assumed TotalAmount = 100 and AmountPaid = (Value you will enter). The coming answer will be your AmountOwed. You can Hide TotalInvoice textbox and set the AmountDue = AmountOwed. Myself seems little confused as I didnt understand the scenario properly but hopefully with a little bit fiddling with these things you will sort out your answer.....
=[TotalInvoice]-[InvoicePaid]
Click to expand...
 
The scenario is quite simple, I just need to enter the invoice amount paid and it gets subtracted from the total invoice amount due, thus showing the remaning balance.
 
what you need to do is create a update query in access e.g.

Code: 
Update table_name SET total_value = total_value - [Please Enter Amount Paid]

So when run it should ask the user to enter the amount paid and update the total.
 
Yeah I can make an Update Query but the problem is that i want to use the web to enter data and with an update query I can't make Reports, Forms or Data Access Pages,...
 
ah I see but I think you still need to create a update query but instead of asking for user input from within the query you refer to the form name and field to be used, assuming you are using access forums as the interface? since you don't mention which language you are using to connect to the database? I think the bellow is correct for access forms

e.g.
Code: 
Update table_name SET total_value = total_value - form_name.field_name
 
Tried it but its not working, perhaps i am doing something wrong,....
Just to add to the burden,.. one more thing,.. How and where does one write triggers and check statements in access,.... I checked Access help but all it said was that inorder to create a trigger, one's database should be in an SQL server and access should only be used to connect and query that data. Its called a MS Access project,...
So, any ideas on how to but constraints using triggers and check statements if one's database is in access as well.

thanks
 
You must log in or register to reply here.
Back
Top