creating formula in access

[nedian]

Hello

I am student and trying to learn access.I am having problem in making a program can any one help me?

i wanted to make 2 tables in access and then create a link.First i am telling u what these tables contain then i will proceed.

(i have also tried this in excel but failed to do so).


table 1 (contains following items)

D T
3/4 0.35
1 0.69
2.3 0.59
2/3 0.76

table 2 (contains following items)
D W
3/4 =4.85*T(D-T)
1 =4.85*T(D-T)
2/3 =4.85*T(D-T)
2.3 =4.85*T(D-T)
i want to create link in these two tables such that when i am going to enter value of D it automatically calculate the value of W (the formula of W is written in table 2) by picking up the value of T from table 1 and then doing it.

now my first problem is that i dont know how to write formula in access.Is this the way, in which i should write formula in access???? as this is the way in which we used to write formula in excel.

And my second question is that should i have to create a querry for calculating value of W.If yes then which type of querry and how.

A little bit of help will also be greatly appreciated.

Thanks.

[comteck]

I'm not quite understanding your formulas. However, the way to do this is simple. You create a form with 2 textboxes (name them textbox1 and textbox2), and a Command button (name it btnCalculate). Right Click on the Command Button, and click "Build Event". Choose "Code Builder", and the code window will open, with the following code:

Private Sub btnCalculate_Cl ick()

End Sub

You then put your fomula in betwwen the "Private" and "End". You also have to declare your variables (using Dim). So, it should look like this when you are done.

Private Sub btnCalculate_Cl ick()
Dim D, W As Integer
D = Me.Textbox1
W = D * 2
Me.Textbox2 = W
End Sub

All I have here is "W=D*2" for a formula. You replace it with whatever formula you want. Remember, you have to declare all variables.

Good Luck.

[nedian]

Thanks for guiding me, but i am still encountering some problem.I will be really greatful if u guide me more.

what u have told me i have tried that but it is not completed without errors.let me tell u what i have done so far then i will tell u where i am having problem.

First of all i have created a table in which i have taken 3 entities.

1) Id ( primary key).(i have taken this enitity because a primary key is must for any table)
2) D
3) T

The next thing which i have done is: i have created a form by using design view.

u told me that
"create a form by using two textboxes( name them textbox1 and textbox2) and a Command button (name it btnCalculate). Right Click on the Command Button, and click "Build Event". Choose "Code Builder", and the code window will open, with the following code:"

Now my question is that when i created the first textbox, by clicking on the textbox button and then clicking on the form there appereared two boxes. the one with a transparent box inside which is written textbox0( i then changed the name, and name it textbox1.) and the other box is a white box inside which it is written "unbound" . In the same manner i have created two other textboxes and named it textbox2 and textbox3.

I have also created a Command button as per your instructions and when the code window is opened. i finished writing the code which appeared as follows.
Private Sub btnCalculate_cl ick( )
Dim D,Dim T,W As Integer
D = Me.Textbox1
T = Me.Textbox2
W = 4.85 * T(D - T)
Me.Textbox3 = W
End Sub

when i runned that it is showing me error on second line

Dim D,Dim T,W As Integer

and this line appears in red.

Now what should i do.i have declared the variables D and T as u told me.but it is not accepting the variable T, i don't know why.

I am simply trying to make a program in which there are two variables D and T and the values of D and T are saved in a table. and this programs helps me to calculate the value of W (formula of W=4.85*T(D-T) ) whenever i give the value of D without giving the value of T with it.

i think "form" is not suitable for my program because in form when i have to calculate the value of W i have to give both the values of D and T and this is the thing which i don't want to do.

I am in dire need of further guidance.

Thanks

[comteck]

First of all, I'm not sure how familiar you are with Access. You might want to go online and do some of the online tutorials (there are many free ones). Here is one: http://www.functionx.com/vbaccess/.

But to get back to your problem. When you created your textbox, you said you got 2 textboxes (a transparent one, and a white box with the word "unbound". The transparent one is not actually a textbox. It is a label for the textbox that you created. You can click on just that label and delete it if you wish. It is the white box that must be renamed. You right click on it, and choose "Properties ". Then put the name into the "Name" field (which is the first field under the "All" tab).

As for your red message that comes up for:
Dim D, Dim T, W, as Integer

Remove the second "Dim" word. It should only read:
Dim D, T, W as Integer

If it still doesn't accept the variables, you might have to assign a value to them initially. To do this, after your "Dim" statement, add this code:
D=0
T=0

And lastly, you said you don't think form is
Good Luck.

comteck

[comteck]

First of all, I'm not sure how familiar you are with Access. You might want to go online and do some of the online tutorials (there are many free ones). Here is one: http://www.functionx.c om/vbaccess/.

But to get back to your problem. When you created your textbox, you said you got 2 textboxes (a transparent one, and a white box with the word "unbound". The transparent one is not actually a textbox. It is a label for the textbox that you created. You can click on just that label and delete it if you wish. It is the white box that must be renamed. You right click on it, and choose "Properties ". Then put the name into the "Name" field (which is the first field under the "All" tab).

As for your red message that comes up for:
Dim D, Dim T, W, as Integer

Remove the second "Dim" word. It should only read:
Dim D, T, W as Integer

If it still doesn't accept the variables, you might have to assign a value to them initially. To do this, after your "Dim" statement, add this code:
D=0
T=0

And lastly, you said you don't think form is suitable because you don't want to enter the values of D and T. First of all, a form is used to make it more user friendly. You can use a form with any table or query in your database. And, a form is the only place you can put the button. So, yes, a form will definitely work. Secondly, where is it you want to get your values from? If you want to get them from pull down menus, then you have to use combo boxes instead of text boxes. Then, the code will be basically the same, but you will have to set up 2 more tables, and source those tables to your combo boxes.

If you were looking to use the pull down menus, let me know and I'll try and walk you through it.


Good Luck.
comteck

[nedian]

Thanks for your help, i am now able to calculate the value of W and my half task is done.

But the problem is that when i opened the form it is showing me the value of D and T both by itself (as it is stored in the table from which i am taking the data).and it is not asking the user to enter the value of D.

but what i want to do is that:

i want myself to enter the value of D in the form and want the form to show me the corresponding value of T (which is stored in the table on which the "form" is based on ) and when it showed me the value of T on the textbox then i proceed my work, of calculating the value of W by clicking on the btnCalculate.

Thanks again.