expression built sting / Parsing text field

  • Thread starter Thread starter Andre
  • Start date Start date
A

Andre

I am needing to Parse some text fields, where a 3rd party application i
use stores data from a user created expression.



the user creates and expression such as this:

ActualSizeYN =Y? SubassyL : BackDadoYN = Y ?(BaseRailYN =Y ? SubassyL
+DadoBack+ RailM : SubassyL + 2* DadoBack) : SubassyL - 2*
BackClearance- TbH


The data is stored in a text field like this.

;389 =Y? ;10 : ;160 = Y ?(;105 =Y ? ;10 +;111+ ;100 : ;10 + 2* ;111) :
;10 - 2* ;161- ;72


where each number precceded by a ; is the ID number of the parameter in
the paramater table ex:

ID |ParamaterName
-----------------------
389 |ActualSizeYN
10 |SubassyL
160 |BackDadoYN


Each Parameter may contain another expression, a Quantity, or a
Dimension as its value. The application prevents

I need to be able to answer the question, and output to a table or
query, What are all the parameters which are used in an expression
(including the ones nested bellow in other parameters)



I would appreciate any suggestions


Andre
 
Andre said:
I am needing to Parse some text fields, where a 3rd party application i
use stores data from a user created expression.



the user creates and expression such as this:

ActualSizeYN =Y? SubassyL : BackDadoYN = Y ?(BaseRailYN =Y ? SubassyL
+DadoBack+ RailM : SubassyL + 2* DadoBack) : SubassyL - 2*
BackClearance- TbH


The data is stored in a text field like this.

;389 =Y? ;10 : ;160 = Y ?(;105 =Y ? ;10 +;111+ ;100 : ;10 + 2* ;111) :
;10 - 2* ;161- ;72


where each number precceded by a ; is the ID number of the parameter in
the paramater table ex:

ID |ParamaterName
-----------------------
389 |ActualSizeYN
10 |SubassyL
160 |BackDadoYN


Each Parameter may contain another expression, a Quantity, or a
Dimension as its value. The application prevents

I need to be able to answer the question, and output to a table or
query, What are all the parameters which are used in an expression
(including the ones nested bellow in other parameters)
Click to expand...

So your base problem is parsing the parameter codes out of the
expression?

Here's an idea for the start of a solution. This function takes an
string argument in the form of the type of expression you posted
and returns a comma delimited string of the parameters names
associated with them.

<input>
;389 =Y? ;10 : ;160 = Y ?(;105 =Y ? ;10 +;111+ ;100 :
;10 + 2* ;111) :;10 - 2* ;161- ;72
</input>

<output>
ActualSizeYN,SubassyL,BackDadoYN,BaseRailYN,SubassyL,
DadoBack,RailM,SubassyL,DadoBack,SubassyL,BackClearance,TbH
</output>

<code>
Function ParseParameters(exp As String) As String
Dim i As Integer
Dim s As String
Dim nip As String
Dim temp As String

Dim hit As Boolean

For i = 1 To Len(exp)
s = Mid$(exp, i, 1)
Select Case s
Case ";"
hit = True
Case "0" To "9"
If hit Then
nip = nip & s
End If
Case Else
If hit Then
temp = temp & DLookup("[ParameterName]", _
"tblParameters", _
"[Id] =" & nip) & ","
nip = ""
hit = False
End If
End Select
Next

If Len(nip) Then
temp = temp & DLookup("[ParameterName]", "tblParameters", "[Id] =" &
nip)
Else
temp = Left$(temp, Len(temp) - 1)
End If

ParseParameters = temp

End Function

</code>
 
There are many ways to solve this problem. I think I would do it by passing the complete string to a module. Then I would parse the string with the instr(), Len(), left(), right(). Then assign all of the parts of the string you need to a variable and then do a select case statement to run what ever you need to do. If I understand your question it looks like these pieces of the string need to be a parameter for a query def. I have done this very thing using macros and queries but have found that the code modules work faster and can be made to work with many different variations of the string. There are some good examples of this type of code in access help.

Keith

rkc said:
Andre said:
I am needing to Parse some text fields, where a 3rd party application i
use stores data from a user created expression.



the user creates and expression such as this:

ActualSizeYN =Y? SubassyL : BackDadoYN = Y ?(BaseRailYN =Y ? SubassyL
+DadoBack+ RailM : SubassyL + 2* DadoBack) : SubassyL - 2*
BackClearance- TbH


The data is stored in a text field like this.

;389 =Y? ;10 : ;160 = Y ?(;105 =Y ? ;10 +;111+ ;100 : ;10 + 2* ;111) :
;10 - 2* ;161- ;72


where each number precceded by a ; is the ID number of the parameter in
the paramater table ex:

ID |ParamaterName
-----------------------
389 |ActualSizeYN
10 |SubassyL
160 |BackDadoYN


Each Parameter may contain another expression, a Quantity, or a
Dimension as its value. The application prevents

I need to be able to answer the question, and output to a table or
query, What are all the parameters which are used in an expression
(including the ones nested bellow in other parameters)
Click to expand...

So your base problem is parsing the parameter codes out of the
expression?

Here's an idea for the start of a solution. This function takes an
string argument in the form of the type of expression you posted
and returns a comma delimited string of the parameters names
associated with them.

<input>
;389 =Y? ;10 : ;160 = Y ?(;105 =Y ? ;10 +;111+ ;100 :
;10 + 2* ;111) :;10 - 2* ;161- ;72
</input>

<output>
ActualSizeYN,SubassyL,BackDadoYN,BaseRailYN,SubassyL,
DadoBack,RailM,SubassyL,DadoBack,SubassyL,BackClearance,TbH
</output>

<code>
Function ParseParameters(exp As String) As String
Dim i As Integer
Dim s As String
Dim nip As String
Dim temp As String

Dim hit As Boolean

For i = 1 To Len(exp)
s = Mid$(exp, i, 1)
Select Case s
Case ";"
hit = True
Case "0" To "9"
If hit Then
nip = nip & s
End If
Case Else
If hit Then
temp = temp & DLookup("[ParameterName]", _
"tblParameters", _
"[Id] =" & nip) & ","
nip = ""
hit = False
End If
End Select
Next

If Len(nip) Then
temp = temp & DLookup("[ParameterName]", "tblParameters", "[Id] =" &
nip)
Else
temp = Left$(temp, Len(temp) - 1)
End If

ParseParameters = temp

End Function

</code>
Click to expand...
 
Back
Top