Excel Function in Access

[Witchity]

I want to have fields in an Access tables behave like they would in an Excel spreadsheet. i.e. if someone enters the number 42 into a field called 'units' I would like the next field (called stock) to multiply it by 2 and display 84 automatically. This is needed for approximately 3/4 fields.

Where would I put the command and would it be the same as the command in Excel (which I know quite well).

Please help, and please help me in words of one syllable, I'm an Access virgin!

Many thanks

Witchity

 

[yippie_ky_yay]

Hey Witchity,

I'm not positive, but I don't think you can do what you're asking (directly that is). It is a common database practice to create things called views instead which give the user the appearance of working with a table.

For this example, let's say that the column which you want to take the value from is called NumValue. NumValue must be in your table!

Now create a new query (select your table). Click on the asterix (*) to select all your fields. Now go to the first empty column in the "Field:" row type:
Result: [NumValue]*2

Now run the query. Your new column (called Result) will automatically double NumValue and place it in itself. It's important to note that this new value will not be stored anywhere, rather, it recalculates each time. If you do need it stored, then an easy way would be to add a new column in the table and then cut and paste.

Hope that helps and good luck!

-Sean

 

[WayneRyan]

Witchity,

Sean is right, you should not store values that can be
calculated. It will cause you trouble in the long run.

However, in addition to the query concept, you can do the
following.

If you have a form, with all of your fields directly related to
a table. You can make a new "unbound" control, call it
txtStock. This new control is not related to your table at
all.

Now, use the BeforeUpdate event on your units control and
enter:

Me.txtStock = 2 * Me.Units

Whenever you change the value of Units, that code will
execute and display the new value for Stock. Remember that
Stock is not in your table.

good luck,
Wayne

 

[casey]

You could display your fields on a form in Datasheet View. Which will look much like an Excel spreadsheet.
Assumming you've created a table that has the fields you want...

1.)Create a form and then add the fields from your table to the form's Recordsource property (View>>Properties on the menu bar). At the right edge of the RecordSource property, there will be three dots(...) click it to bring up QueryBuilder which will allow you to add the fields to your form. When done, close QueryBuilder and save the changes.
2.)On the form, display the Field List (View>>Field List on the menu bar) which allows you to drag and drop the fields from your table on the form.
3.)Set the form's Default View property to Datasheet. Then, if you select View>>Form, the fields from your table will be displayed on the form. You should be able to add/delete records as you wish.
4.)If you go back into Design View, you can then add an additional textboxe to your form. In this textbox you can calculate a total from the fields. Say you have a field named [Units] on your form. Calculate [Units]*2 in the textboxes' ControlSource property by adding...
=[Units]*2
5.)Go back to Form View and your total should be displayed in that field you created.

This may take some practice, but give it try and let me know if you have problems.

Also, by using Search on this Site, you can find answers to questions that you might have as well.

Good luck.

 

[Witchity]

Hi, gang, thanks for the tips. I've started with Casey's suggestion, which was going quite well, until I tried to enter data into some empty fields for the UnitPrice field and couldn't! Data which had previously been entered had been calculated correctly, but I couldn't add data? I had changed the default view for the form to datasheet, but had changed it back to single form after making the amendments as my users need a nice front end to use. Would this have caused the problem?

I'll try the other suggestions as time allows, but gee am I having fun learning all this Access stuff! Thanks again for letting me pick your brains - I certainly need it!!

 

[casey]

That's that attitude I like to see. I'm glad you're having fun!

I forgot. If you have a calculated textbox ([Units]*2) on your form, that might be causing problem you're having when you edit the data. I can't quite think of the solution right now(I've got a cramp), but maybe someone else can help us out or if I think of something, I'll let you know.

Also, when you get going comfortably, take some time to look over the other suggestions more thoroughly. While these might be a little hard to grasp if you're just beginning, they are very important points to keep in mind and will save you time over the long-haul.

Keep us informed as to your progress or otherwise.

HTH

 

[casey]

Also, check the form's AllowAdditions, AllowDeletions, etc. on the properties Data tab(design view>>click on the form's upper, left-hand corner). Set them to "Yes" if any of the Allows... are set to No.

That might be it.

 

[Witchity]

Back again! Have tried both Casey and Wayne Ryan's solutions now, and keep getting the #name! error message. The field I'm using is called UnitPrice (at least it's a textbox called UnitPrice) and the fields name in the table design view is the same. It's format is currency (for obvious reasons!) Does the box the info is going into have to be the same format?

I don't know what I did for the first form Casey, I just deleted it and tried again, and fingers crossed it appears to be working normally now - thanx!

I admire your patience guys, and thanks for all the help.

 

[casey]

What exactly is it that's giving you the #Name error? If it's a calculated textbox, then check the actual names of the textboxes that you're trying to calculate the value from. Make sure that the Names you're referencing in the formula match the actual Names of the textboxes. Look at the properties sheet>>Other tab.

HTH

 

[Witchity]

Well, whadda you know! Opened the database this morning, and it's all working just Dandy!! Maybe it just needed a rest (or more likely the operator did - it was quite late last night whilst all this was going on!) Brilliant!! Many thanks for all your help - I'm off now to play a bit more and tweak the form so it looks even prettier!