How can we use FORMULAE in Access ??

davekrunal46 · December 3rd, 2005, 02:51 AM

hello,

I am using Microsoft Access, and trying to creating one FORM, now what i want to do is, when i suppose to enter data in row, after pressing TAB (to move next column), the entered data shoudl multilplied by 0.001 and replace there itself when i first enter data..

Any ideas, tell me in brief, am new to ACCESS..

~ ~ ~ There is no Right, There is no Wrong, KNOWLEDGE is Only the POWER ~ ~ ~

echovue · December 3rd, 2005, 11:32 AM

One way that you could do it, is to code the function, and run it when the text box loses focus. The problem here is that if you click on the textbox, and then leave it again, it could rerun the function, so you would need to put some code in place to prevent this.

If you have a textbox called txtValue, go to the properties, and click the On LostFocus Event. A button will appear with ... on it. Click on that, choose Code builder, and you will be given something like...

Code:

Private Sub txtValue_LostFocus()

End Sub

Between those two line you would enter the following...

Code:

dim dblValue as double

if isnumeric(txtValue.text) then
    dblValue = val(txtValue.text)
    if dblValue > 0.1 then
        dblValue = dblValue * 0.001
        txtValue.text = dblValue
    end if
else
    msgBox "Not a numeric value!"
end if

That should be fairly straight forward, although it may need a little tweaking, and you could take out the check for the numeric value if you need to. Let me know if need any additional explanation.

Mike

Mike
EchoVue.com

davekrunal46 · December 5th, 2005, 01:18 AM

Hello Echovue,

I have got very much intelligent solution from you, but i m using only Excel..

Without using VB, how can we do that..??

~ ~ ~ There is no Right, There is no Wrong, KNOWLEDGE is Only the POWER ~ ~ ~

echovue · December 5th, 2005, 01:27 AM

Forgive me, the original posting specifically said Access. You would typically do something like this is a module attached to an Access Form (it is written in VBA.)

To do this in Excel is a completely different animal, and I would think that you may have more success in the Excel forum.

Sorry I couldn't be more help,

Mike

Mike
EchoVue.com

leehambly · December 6th, 2005, 05:27 AM

dave,

this isnt excel... the "worksheet"/"datasheet" view isnt like Excel, it just looks like Excel. If you want to use a formula to interact with your data, you should be doing that in the form, report or query that is displaying your data and NOT the table where the data is stored. Depending on your required function you may need to code it, and you may not.

So, you have a form you are trying to create and then you say you have a row... a row of what, exactly?