converting text to a function

[adwalleye]

I am trying to convert a text field into a function within VBA. For example, I have four fields:
UOM1 = Hours
UOM2 = Seconds
Conversion Calculation = "*60"
Units = 1

I am trying to programatically make the conversion by using the calculation in the field. So, I am trying to make the code say something like:

newUnits = [Units] & [Conversion Calculation] which would translate to:
newUnits = 1*60 which would equal 60

I am sure I could create the correct SQL to do it, but I am trying to do it in VBA code since I may have to do a calculation many times.

Any ideas?

Thanks

[Delerna]

I believe you will need to perform some sort of parsing on the value in ConversionCalcu lation

For the example you provide ...something like
[code =vba]

Operator=left(C onversionCalcul ation,1) = "*60"
Numerator=cint( mid(ConversionC alculation,2,10 ))
select case
case "*"
newUnits = Units * Numerator
case "/"
newUnits = Units / Numerator
'etc ....
end select
[/code]

as far as I am aware there is no way to say
newUnits = [Units] & [Conversion Calculation]
in VBA that will be interpreted correctly.

You could do it in MS SQL Server using dynamic queries
Actually, on second thoughts....... ...
Let me go test a theory before I post it.

[Delerna]

I thought about how a dynamic query could be done in VBA.
Which is easy, because all queries in VBA are dynamic.

To test I created a form and put a textbox on it and then wrote the following code in the Form_Load event

[code=vba]
Option Compare Database
Option Explicit
Private Sub Form_Load()
Dim db As Database, rst As DAO.Recordset
Dim SQL As String, ConversionCalcu lation As String, units As Integer
Set db = CurrentDb
ConversionCalcu lation = "*60"
units = 1
SQL = "SELECT " & units & ConversionCalcu lation
Set rst = db.OpenRecordse t(SQL)
Text0 = rst.Fields(0)
Set rst = Nothing
Set db = Nothing
End Sub
[/code]

So, in effect, I am using DAO not to access database records but as a function that takes a mathematical expression, stored as a string, as its argument and returns the result in field(0) of a recordset object.

This makes me think of some interresting possibilites actually :)

Note
Make sure you have the "Microsoft DAO 3.6 object library" selected in your references