CHALLENGE: Calculate value from within field

[mjseim]

Alright... I'm no programmer. I dabble in Excel and Access VB but that's about it. Recently my users demanded a calculator for my database and found one on MVPS.org (http://www.mvps.org/access/resources/downloads.htm). That works just fine... but it sparks a thought...

Many of you are probably familiar with CSE (CTRL+SHIFT+ENTER) functions in Excel. I was thinking it would be a MIRACLE to have a similar functionality in Access. Yet, I have no idea how to do it... moreover, since it's not a necessity for my work I thought perhaps it would be better to post the idea as a challange and see if anyone wants to take a stab at it.

Here's the callenge:
1) Create the ability to enter a calculation string in a numeric field "(3*4*1.5)/12" (or perhaps in a popup next to the field)
2) After a user types in the string (which would normally be an unacceptable input for a numeric field), the user would then activate the following Hotkey sequence "CTRL+SHIFT+ENTER"
3) The result of the entered equation (1.5) would be entered into the field as the field's value.

To me, this seems possible... if the ability exists to create a calculator I would think that this may be possible as well. But who knows!

Also, I realize no one wants to confuse users into thinking that equations are stored in access... I'm thought a simple popup window or something of the like would solve that.

So... if anyone out there wants a challenge here you go! Unfortunately, there is no prize... other than the shear gratification of solving a long standing annoyance and obstacle new Access users seem to have.

 

[KeithG]

Check out this example, is this what you want? You have to press the button to calculate the value but you could use an AutoKeys macro to assign the function to a set of key strokes

 

[mjseim]

Very interesting... It sure seems like an easy way to accomplish a calculation in Access (err... Excel).

But, the formula only works on a specific field. The challenge was really looking to create a function that would work on ANY active numeric field. It sure sparks some ideas though...

thinking about... Screen.PreviousControl

 

[KeithG]

But, the formula only works on a specific field. The challenge was really looking to create a function that would work on ANY active numeric field. It sure sparks some ideas though...

I only programmed the function behind one control as an example to help guide you to a solution.

 

[ByteMyzer]

It is not possible to program an Autokeys macro for CTRL-SHIFT-ENTER in MS Access, but attached is a sample database that uses CTRL-SHIFT-A to do this. Open the database, open the sample form, and highlight any one of the input fields and press CTRL-SHIFT-A. MS Access should prompt you with an input box: Enter Calculation Expression. Type in the expression and click [OK] or press [Enter]. See if this solution works for you.

 

[mjseim]

ByteMyzer! WOW!! This is awesome! ...and how simple!

I never knew of the Eval() function before! What a lifesaver!

(ok... enough exclamation points.)

But seriously, this is amazing. Thank you!