Help On Input Mask

[Wren17]

Hello everyone:

This is my first post on this website.
I am a beginner to intermediate user in Access

And right now I am trying to create a data entry form, however
I need my fields to do Addition.

I want to be able to type +2+6+5 in my field and it show the answer. Just like in excel.
I have played around with the Input Mask a little, but I have not found anything that works yet.

If anyone could help me on this I would greatly appreciate it.

Thanks,
Matt

 

[Alexandre]

An input mask will not solve your problem.
See if the following can help:
Basic spreadsheet feature using Eval

 

[Wren17]

I don't really understand what is being done here with eval.

I mean do I put eval and then an equation in the before update field.

ex. eval(1+1+1)

could you give me a working example that I could go from.

 

[Rich]

Private Sub MyTextBox_AfterUpdate()
Me.MyTextBox = Eval(Nz(Me.MyTextBox, 0))
End Sub

 

[Wren17]

Private Sub MyTextBox_AfterUpdate()
Me.MyTextBox = Eval(Nz(Me.MyTextBox, 0))
End Sub

I do not want to be a pain, but please bare with me.

Okay. In place of "MyTextBox" I would put the name of the Textbox that I wish to do this eval in.

"Me" is the name of the form? or table?
I am unsure

What is "Nz" in all of this? Is there something to be put in place of "Nz"?

Will "Eval(Nz(Me.MyTextBox,0)" this be enough to do what I want it to do. Meaning will this tell it to evaluate my addition. Or do I have to configure it diffrently.

I am sorry that I am asking all of these questions, but It seems to be a very valuble function, and I want to understand it to the fullest.

Thanks for all of your help,
I am getting nothing from the guys at work with me. They have all said that I can't do it. But I believe that these programs can do more than what is on the surface.

Matt

 

[Alexandre]

Well, here is the idea.
In the thread I referenced, the question was how to actually store formulas while displaying only the calculation result (like in spreadsheets).
In such case you should use a text field to store text strings (= your formulas) instead of numbers. I suggested doing the eval test before update so that you can check whether the formula entered by your users is valid at all before storing it. If you don't make sure of that, you will run into problems at a later time.
Then, whenever you need to evaluate your formulas (queries, reports, calculations etc), just wrap the reference to your field (or your control, like in Rich's example) into Eval to get the numeric result instead of the formula.

If you just want to simulate a 'calculator' ie not actually store formulas but just the results, use Eval from the before or after update event of your control, to get the calculation result on the fly, as Sensible Mammouth pointed.
NB in that case, your control should probably not be a bound one.

BTW, Me is a reserved word designating the report or form your module is linked to.
Nz: Have a look in access help

Don't hesitate to re-post if you still have questions

 

[Wren17]

Thanks alot

It took me awhile, but now I get it.
Nz is a function that returns 0 or defined value if is field is null.

I am learning. Now I can show everyone at work, but they get a little thrown back when it comes to VBA

Matt

 

[Wren17]

Okay,
I got a little further and the Eval does what it is supposed to do.

Now I have a couple more problems.

The way I have it set up is that I have two textboxes One will
eventually be hidden. My first textbox is not linked to any field in my table it is named(Line_Item_Cost_Faketxt) my second textbox
is linked to my table and it will be hidden from the form at a later time its name is (Line_Item_Costtxt).

Now. Line_Item_Cost_Faketxt has a format of "".

While Line_Item_Costtxt has a format of "Currency"

My code tells it to evaluate what is in (Line_Item_Cost_Faketxt)
and put the answer in (Line_Item_Costtxt). Then take it back
and put it in (Line_Item_Cost_Faketxt) As "Currency" This
way it will appear on my form with a dollar sign and with decimal places.

The two problems that I am running into is that for One:
The textbox (Line_Item_Costtxt) is not displaying the value as a decimal. It rounds the Number up or down depending on the decimal point. I do not need it to do this. I need whatever I type in (Line_Item_Cost_Faketxt) to be put in the table.

Well that is problem one:
I also have a problem with the format after I update the textbox
it seems to save the format as "Currency" Which is what I told it to do. But If I need to go back into the box to make a correction.
It will not let me put my formula in, because it is no longer set to "".

I have tried doing a Before_update and even a Click()
and a change()

Here is the code that I have so far: That I have described

Private Sub Line_Item_Cost_Faketxt_AfterUpdate()
Dim J As Variant
Dim M As Currency
Form_Matts_Form.Line_Item_Cost_Faketxt = Eval(Nz(Form_Matts_Form.Line_Item_Cost_Faketxt, 0))
J = Form_Matts_Form.Line_Item_Cost_Faketxt
Form_Matts_Form.Line_Item_Costtxt = J
M = J
Form_Matts_Form.Line_Item_Cost_Faketxt = M
Form_Matts_Form.Line_Item_Cost_Faketxt.Format = "Currency"
End Sub

I am sure That there is an easier way to go about coding all this, but Like I said I am a beginner.

So basically the two questions are:
1. How can I get my eval to return a decimal that is not rounded?
2. How can I get my format to change back to its original state
when I reclick on the field?

Thanks
Matt