Converting inches to millimeters in Access 2

[macdilljohn]

Greetings,

I track data in two formats: imperial and metric, and store it in one table in my database. For this I use two fields: one in inches and one in millimeters. Previously I have been converting the data manually, because it didn’t take much time for the few records I had to enter each day. Now the workload is projected to increase dramatically, and I want to simplify it and save some time.

No matter which field the data is entered in, I want the converted data to automatically appear in the other field and be saved. I’m assuming a macro that would run an expression as the focus leaves the field on my form would do the trick.

Doing it in excel is a snap using the analysis tool kit add-in, but there's no such tool in access that I can find, and access doesn't recognize the =CONVERT function, or I may be doing it wrong.

Any ideas?

 

[lupins46]

Use the afterupdate events of each textbox.

For the Cms textbox (txtCms)
if not isnull(me.txtcms) then
me.txtinches = me.txtcms * 0.xxx
else
' do something else if its empty
end if

and similar code in the Inches textbox afterupdate procedure.

 

[RoyVidar]

To save you future headaches when (not if) these two fields get out of sync, store only in one format (inches or metric), then use the function(s) to calculate when needed.

Roy-Vidar

 

[macdilljohn]

Guys, thanks for the help, but I couldn't get it to work. I'm extremely green at understanding code, so I know I did something wrong.

I ended up writing a query to do the calculations and place the data in an unbound text box. Saved the query as a form that looks the same as the original form. Then I wrote a macro to copy and paste the data from the text box into the appropriate data field in the form.

I know, I know, it's the long way around, but I enjoy a challenge!