By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 2,960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

converting text to a function

P: 1
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
Feb 24 '10 #1
Share this Question
Share on Google+
2 Replies


Delerna
Expert 100+
P: 1,134
I believe you will need to perform some sort of parsing on the value in ConversionCalculation

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

Operator=left(ConversionCalculation,1) = "*60"
Numerator=cint(mid(ConversionCalculation,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.
Feb 24 '10 #2

Delerna
Expert 100+
P: 1,134
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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Form_Load()
  4.    Dim db As Database, rst As DAO.Recordset
  5.    Dim SQL As String, ConversionCalculation As String, units As Integer
  6.    Set db = CurrentDb
  7.    ConversionCalculation = "*60"
  8.    units = 1
  9.    SQL = "SELECT " & units & ConversionCalculation
  10.    Set rst = db.OpenRecordset(SQL)
  11.    Text0 = rst.Fields(0)
  12.    Set rst = Nothing
  13.    Set db = Nothing
  14. End Sub
  15.  
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
Feb 24 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.