Parsing Varying Text Data

[Guest]

I have a table with a Length field as text. The data contains numbers and
other text characters. What I need to do is convert the current numbers
considered considered in inches to millimeters, but maintaining the text
characters.

For example.

Current Data Intended Result
10 254
+9 +229
-5 -127

4 >102

3 DEC 76 DEC
1R 25R

Thank you in advance.

 

[strive4peace]

convertInchStringToMmString
---

Hi Martin,

you can write a function to return the desired results

put this into a general module:

'~~~~~~~~~~~~~~
Function convertInchStringToMmString(pInchString as text)

dim mBefore as text _
, mAfter as Text _
, mNumStr as String _
, mBoo as boolean
, i as integer

mBefore = ""
mAfter = ""
mBoo = false
mNumStr = ""

for i = 1 to len(nz(pInchString,""))
if isnumeric(pInchString) then
mBoo = true
mNumStr = mNumStr & mid(pInchString, i, 1)
else
if mBoo then
mAfter = mAfter & mid(pInchString, i, 1)
else
mBefore = mBefore & mid(pInchString, i, 1)
end if
end if
next i

convertInchStringToMmString = mBefore _
& mNumStr\25.4 _
& mAfter

End Function
'~~~~~~~~~~~~~~

then, in a query, you can see the reulsts by creating a claculated column:

ColumnName: convertIncheStringToMmString([fieldname])

where
ColumnName is what you want to call the column (cannot be the same as a
fieldname in the recordsource)
[fieldname] is the name of the field you want to convert


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal,
Thanks for your response. I forgot to check for automatic replies and I
forgot where I posted the question. Your response is masterful for what I
need. However, I am getting a compile error when running the query. I did a
couple of modifications since I believe you forgot some...here's what I have
in my Module which I called it Conversion...

Function ConvertInchStringtoMmString(pinchString As Text) As Text

Dim mBefore As Text, mAfter As Text, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Then 'modified
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
(mNumStr/25.4) & mAfter 'Modified

End Function

In my Query I have the table with the field, and in one column I used
SideAMM:ConvertInchStringtoMmString([SideA])

Thanks for the help!

convertInchStringToMmString
---

Hi Martin,

you can write a function to return the desired results

put this into a general module:

'~~~~~~~~~~~~~~
Function convertInchStringToMmString(pInchString as text)

dim mBefore as text _
, mAfter as Text _
, mNumStr as String _
, mBoo as boolean
, i as integer

mBefore = ""
mAfter = ""
mBoo = false
mNumStr = ""

for i = 1 to len(nz(pInchString,""))
if isnumeric(pInchString) then
mBoo = true
mNumStr = mNumStr & mid(pInchString, i, 1)
else
if mBoo then
mAfter = mAfter & mid(pInchString, i, 1)
else
mBefore = mBefore & mid(pInchString, i, 1)
end if
end if
next i

convertInchStringToMmString = mBefore _
& mNumStr\25.4 _
& mAfter

End Function
'~~~~~~~~~~~~~~

then, in a query, you can see the reulsts by creating a claculated column:

ColumnName: convertIncheStringToMmString([fieldname])

where
ColumnName is what you want to call the column (cannot be the same as a
fieldname in the recordsource)
[fieldname] is the name of the field you want to convert


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have a table with a Length field as text. The data contains numbers and
other text characters. What I need to do is convert the current numbers
considered considered in inches to millimeters, but maintaining the text
characters.

For example.

Current Data Intended Result
10 254
+9 +229
-5 -127
3 DEC 76 DEC
1R 25R

Thank you in advance.

 

[strive4peace]

compiling code
---

Hi Martin,

you're welcome

I see one problem... there may be more, but this one jumped out

ConvertInchStringtoMmString = mBefore &_
needs to be
ConvertInchStringtoMmString = mBefore & _

when using _ to continue a statement across lines, you must have SPACE
UNDERSCORE, not just underscore

when you compile, which line receives an error?

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~

I had used
mNumStr\25.4
instead of
mNumStr/25.4

so you would get the numeric result as a whole number ...

I suppose it could be griping because you are actually dividing a string
instead of a number... try this:

Val(mNumStr) instead of just mNumStr


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,
Thanks for your response. I forgot to check for automatic replies and I
forgot where I posted the question. Your response is masterful for what I
need. However, I am getting a compile error when running the query. I did a
couple of modifications since I believe you forgot some...here's what I have
in my Module which I called it Conversion...

Function ConvertInchStringtoMmString(pinchString As Text) As Text

Dim mBefore As Text, mAfter As Text, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Then 'modified
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
(mNumStr/25.4) & mAfter 'Modified

End Function

In my Query I have the table with the field, and in one column I used
SideAMM:ConvertInchStringtoMmString([SideA])

Thanks for the help!

convertInchStringToMmString
---

Hi Martin,

you can write a function to return the desired results

put this into a general module:

'~~~~~~~~~~~~~~
Function convertInchStringToMmString(pInchString as text)

dim mBefore as text _
, mAfter as Text _
, mNumStr as String _
, mBoo as boolean
, i as integer

mBefore = ""
mAfter = ""
mBoo = false
mNumStr = ""

for i = 1 to len(nz(pInchString,""))
if isnumeric(pInchString) then
mBoo = true
mNumStr = mNumStr & mid(pInchString, i, 1)
else
if mBoo then
mAfter = mAfter & mid(pInchString, i, 1)
else
mBefore = mBefore & mid(pInchString, i, 1)
end if
end if
next i

convertInchStringToMmString = mBefore _
& mNumStr\25.4 _
& mAfter

End Function
'~~~~~~~~~~~~~~

then, in a query, you can see the reulsts by creating a claculated column:

ColumnName: convertIncheStringToMmString([fieldname])

where
ColumnName is what you want to call the column (cannot be the same as a
fieldname in the recordsource)
[fieldname] is the name of the field you want to convert


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have a table with a Length field as text. The data contains numbers and
other text characters. What I need to do is convert the current numbers
considered considered in inches to millimeters, but maintaining the text
characters.

For example.

Current Data Intended Result
10 254
+9 +229
-5 -127
4 >102
3 DEC 76 DEC
1R 25R

Thank you in advance.

 

[Guest]

Hi Crystal,

I made a couple of changes to make the "Compile Error. in Query Expression
ConvertInchStringtoMmString([SideA])" message. It was a pop-up window with
only the OK or Help buttons. I wasn't getting the debbuging window. Here's my
progress but now I am stuck with two items.
1. If the field value is null, the query result is #Error instead of a null
value.
2. The query gets stuck when the field value is "171-173". You will notice
that I included the "." and the "-" as part of the numeric field so values
with the decimal point and/or a negative value are computed. The reason for
this is that I am trying to make a temperature computation in another field.
Therefore the negative value is important (I never realized this before).

Thank you for pointing out the backslash use, I wasn't aware of it.

Function ConvertInchStringtoMmString(pinchString As String) As String

Dim mBefore As String, mAfter As String, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Or Mid(pinchString, i, 1)="." Or _
Mid(pinchString, i, 1)="-" Then
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
CStr(FormatNumber((Val(mNumStr)/25.4),2)) & mAfter

End Function

compiling code
---

Hi Martin,

you're welcome

I see one problem... there may be more, but this one jumped out

ConvertInchStringtoMmString = mBefore &_
needs to be
ConvertInchStringtoMmString = mBefore & _

when using _ to continue a statement across lines, you must have SPACE
UNDERSCORE, not just underscore

when you compile, which line receives an error?

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~

I had used
mNumStr\25.4
instead of
mNumStr/25.4

so you would get the numeric result as a whole number ...

I suppose it could be griping because you are actually dividing a string
instead of a number... try this:

Val(mNumStr) instead of just mNumStr


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,
Thanks for your response. I forgot to check for automatic replies and I
forgot where I posted the question. Your response is masterful for what I
need. However, I am getting a compile error when running the query. I did a
couple of modifications since I believe you forgot some...here's what I have
in my Module which I called it Conversion...

Function ConvertInchStringtoMmString(pinchString As Text) As Text

Dim mBefore As Text, mAfter As Text, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Then 'modified
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
(mNumStr/25.4) & mAfter 'Modified

End Function

In my Query I have the table with the field, and in one column I used
SideAMM:ConvertInchStringtoMmString([SideA])

Thanks for the help!

convertInchStringToMmString
---

Hi Martin,

you can write a function to return the desired results

put this into a general module:

'~~~~~~~~~~~~~~
Function convertInchStringToMmString(pInchString as text)

dim mBefore as text _
, mAfter as Text _
, mNumStr as String _
, mBoo as boolean
, i as integer

mBefore = ""
mAfter = ""
mBoo = false
mNumStr = ""

for i = 1 to len(nz(pInchString,""))
if isnumeric(pInchString) then
mBoo = true
mNumStr = mNumStr & mid(pInchString, i, 1)
else
if mBoo then
mAfter = mAfter & mid(pInchString, i, 1)
else
mBefore = mBefore & mid(pInchString, i, 1)
end if
end if
next i

convertInchStringToMmString = mBefore _
& mNumStr\25.4 _
& mAfter

End Function
'~~~~~~~~~~~~~~

then, in a query, you can see the reulsts by creating a claculated column:

ColumnName: convertIncheStringToMmString([fieldname])

where
ColumnName is what you want to call the column (cannot be the same as a
fieldname in the recordsource)
[fieldname] is the name of the field you want to convert


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Martin wrote:
I have a table with a Length field as text. The data contains numbers and
other text characters. What I need to do is convert the current numbers
considered considered in inches to millimeters, but maintaining the text
characters.

For example.

Current Data Intended Result
10 254
+9 +229
-5 -127
4 >102
3 DEC 76 DEC
1R 25R

Thank you in advance.

 

[strive4peace]

NZ to send null to function
---

Hi Martin,

to avoind the #Error when there is no value...

convertIncheStringToMmString(nz([fieldname],""))

for cases like "171-173", you will have to handle that in code...

mBoo becomes true when a character is determined to be numeric ... "-"
is not numeric (so it would be copied literally) and the resulting
number, in this case, would incorrectly be 171173

what do you desire to do in cases like this?

~~~

from Help

'~~~~~~~~~~~~~~~~~~~~~~~~~~``
Nz Function

You can use the Nz function to return zero, a zero-length string ("
"), or another specified value when a Variant is Null. For example, you
can use this function to convert a Null value to another value and
prevent it from propagating through an expression.

Syntax

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument

variant
A variable of data type Variant.

valueifnull
Optional (unless used in a query).
A Variant that supplies a value to be returned if the variant
argument is Null. This argument enables you to return a value other than
zero or a zero-length string.

Note If you use the Nz function in an expression in a query without
using the valueifnull argument, the results will be a zero-length string
in the fields that contain null values.


'~~~~~~~~~~~~~~~~~~~~~~~~~~``

the thing to keep in mind when using Nz is what it will return if you
don't the specify the second, optional, argument.

If you are using Nz on a field, it will be the data type of that field
-- 0 for numbers (including dates), and an empty string for text or memo

Unbound textbox / combobox / listbox controls on a form are assumed to
have TEXT in them... so an empty string will be returned if nothing is
specified. And, if you specify something, it doesn't have to be 0 or ""

using a generic example...

you could do this:

NZ(..., "no commision is found in the table for this employee")

If you do not specify the optional argument -->
If the expression is bound to a text field, an empty string will be
returned if the field is null. If the expression is bound to a numeric
field, 0 will be returned if the field is null.

it is a good idea to wrap return values from dLookup, etc, in NZ in case
no match was found

.... a good "rule-of-thumb" is to specify the optional arguments (even
though it is not necessary) and do this:

= Nz(DLookup("[Commission]", "Employees", "[EmpID] = " & nz([EmpID],0)),0 )

notice how NZ is used twice -- once to make sure the criteria will be
evaluated, and another time around everything in case dLookup didn't
return a value...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,

I made a couple of changes to make the "Compile Error. in Query Expression
ConvertInchStringtoMmString([SideA])" message. It was a pop-up window with
only the OK or Help buttons. I wasn't getting the debbuging window. Here's my
progress but now I am stuck with two items.
1. If the field value is null, the query result is #Error instead of a null
value.
2. The query gets stuck when the field value is "171-173". You will notice
that I included the "." and the "-" as part of the numeric field so values
with the decimal point and/or a negative value are computed. The reason for
this is that I am trying to make a temperature computation in another field.
Therefore the negative value is important (I never realized this before).

Thank you for pointing out the backslash use, I wasn't aware of it.

Function ConvertInchStringtoMmString(pinchString As String) As String

Dim mBefore As String, mAfter As String, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Or Mid(pinchString, i, 1)="." Or _
Mid(pinchString, i, 1)="-" Then
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
CStr(FormatNumber((Val(mNumStr)/25.4),2)) & mAfter

End Function

compiling code
---

Hi Martin,

you're welcome

I see one problem... there may be more, but this one jumped out

ConvertInchStringtoMmString = mBefore &_
needs to be
ConvertInchStringtoMmString = mBefore & _

when using _ to continue a statement across lines, you must have SPACE
UNDERSCORE, not just underscore

when you compile, which line receives an error?

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~

I had used
mNumStr\25.4
instead of
mNumStr/25.4

so you would get the numeric result as a whole number ...

I suppose it could be griping because you are actually dividing a string
instead of a number... try this:

Val(mNumStr) instead of just mNumStr


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,
Thanks for your response. I forgot to check for automatic replies and I
forgot where I posted the question. Your response is masterful for what I
need. However, I am getting a compile error when running the query. I did a
couple of modifications since I believe you forgot some...here's what I have
in my Module which I called it Conversion...

Function ConvertInchStringtoMmString(pinchString As Text) As Text

Dim mBefore As Text, mAfter As Text, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Then 'modified
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
(mNumStr/25.4) & mAfter 'Modified

End Function

In my Query I have the table with the field, and in one column I used
SideAMM:ConvertInchStringtoMmString([SideA])

Thanks for the help!


:

convertInchStringToMmString
---

Hi Martin,

you can write a function to return the desired results

put this into a general module:

'~~~~~~~~~~~~~~
Function convertInchStringToMmString(pInchString as text)

dim mBefore as text _
, mAfter as Text _
, mNumStr as String _
, mBoo as boolean
, i as integer

mBefore = ""
mAfter = ""
mBoo = false
mNumStr = ""

for i = 1 to len(nz(pInchString,""))
if isnumeric(pInchString) then
mBoo = true
mNumStr = mNumStr & mid(pInchString, i, 1)
else
if mBoo then
mAfter = mAfter & mid(pInchString, i, 1)
else
mBefore = mBefore & mid(pInchString, i, 1)
end if
end if
next i

convertInchStringToMmString = mBefore _
& mNumStr\25.4 _
& mAfter

End Function
'~~~~~~~~~~~~~~

then, in a query, you can see the reulsts by creating a claculated column:

ColumnName: convertIncheStringToMmString([fieldname])

where
ColumnName is what you want to call the column (cannot be the same as a
fieldname in the recordsource)
[fieldname] is the name of the field you want to convert


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Martin wrote:
I have a table with a Length field as text. The data contains numbers and
other text characters. What I need to do is convert the current numbers
considered considered in inches to millimeters, but maintaining the text
characters.

For example.

Current Data Intended Result
10 254
+9 +229
-5 -127
4 >102
3 DEC 76 DEC
1R 25R

Thank you in advance.

 

[Guest]

Hi Crystal,

Thanks for your reply. I was able to resolve all the issues.

On the #Error result value, your suggestion to apply it at the query worked
without a glitch.

For handling the dash as a dash or a minus sign, I generated the code is the
same fashion you elegantly presented the mBoo switches.

Thanks for your help and eloquent ideas. I learned a lot from you.

Best regards.

Martin

NZ to send null to function
---

Hi Martin,

to avoind the #Error when there is no value...

convertIncheStringToMmString(nz([fieldname],""))

for cases like "171-173", you will have to handle that in code...

mBoo becomes true when a character is determined to be numeric ... "-"
is not numeric (so it would be copied literally) and the resulting
number, in this case, would incorrectly be 171173

what do you desire to do in cases like this?

~~~

from Help

'~~~~~~~~~~~~~~~~~~~~~~~~~~``
Nz Function

You can use the Nz function to return zero, a zero-length string ("
"), or another specified value when a Variant is Null. For example, you
can use this function to convert a Null value to another value and
prevent it from propagating through an expression.

Syntax

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument

variant
A variable of data type Variant.

valueifnull
Optional (unless used in a query).
A Variant that supplies a value to be returned if the variant
argument is Null. This argument enables you to return a value other than
zero or a zero-length string.

Note If you use the Nz function in an expression in a query without
using the valueifnull argument, the results will be a zero-length string
in the fields that contain null values.


'~~~~~~~~~~~~~~~~~~~~~~~~~~``

the thing to keep in mind when using Nz is what it will return if you
don't the specify the second, optional, argument.

If you are using Nz on a field, it will be the data type of that field
-- 0 for numbers (including dates), and an empty string for text or memo

Unbound textbox / combobox / listbox controls on a form are assumed to
have TEXT in them... so an empty string will be returned if nothing is
specified. And, if you specify something, it doesn't have to be 0 or ""

using a generic example...

you could do this:

NZ(..., "no commision is found in the table for this employee")

If you do not specify the optional argument -->
If the expression is bound to a text field, an empty string will be
returned if the field is null. If the expression is bound to a numeric
field, 0 will be returned if the field is null.

it is a good idea to wrap return values from dLookup, etc, in NZ in case
no match was found

.... a good "rule-of-thumb" is to specify the optional arguments (even
though it is not necessary) and do this:

= Nz(DLookup("[Commission]", "Employees", "[EmpID] = " & nz([EmpID],0)),0 )

notice how NZ is used twice -- once to make sure the criteria will be
evaluated, and another time around everything in case dLookup didn't
return a value...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,

I made a couple of changes to make the "Compile Error. in Query Expression
ConvertInchStringtoMmString([SideA])" message. It was a pop-up window with
only the OK or Help buttons. I wasn't getting the debbuging window. Here's my
progress but now I am stuck with two items.
1. If the field value is null, the query result is #Error instead of a null
value.
2. The query gets stuck when the field value is "171-173". You will notice
that I included the "." and the "-" as part of the numeric field so values
with the decimal point and/or a negative value are computed. The reason for
this is that I am trying to make a temperature computation in another field.
Therefore the negative value is important (I never realized this before).

Thank you for pointing out the backslash use, I wasn't aware of it.

Function ConvertInchStringtoMmString(pinchString As String) As String

Dim mBefore As String, mAfter As String, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Or Mid(pinchString, i, 1)="." Or _
Mid(pinchString, i, 1)="-" Then
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
CStr(FormatNumber((Val(mNumStr)/25.4),2)) & mAfter

End Function

compiling code
---

Hi Martin,

you're welcome

I see one problem... there may be more, but this one jumped out

ConvertInchStringtoMmString = mBefore &_
needs to be
ConvertInchStringtoMmString = mBefore & _

when using _ to continue a statement across lines, you must have SPACE
UNDERSCORE, not just underscore

when you compile, which line receives an error?

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~

I had used
mNumStr\25.4
instead of
mNumStr/25.4

so you would get the numeric result as a whole number ...

I suppose it could be griping because you are actually dividing a string
instead of a number... try this:

Val(mNumStr) instead of just mNumStr


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Martin wrote:
Hi Crystal,
Thanks for your response. I forgot to check for automatic replies and I
forgot where I posted the question. Your response is masterful for what I
need. However, I am getting a compile error when running the query. I did a
couple of modifications since I believe you forgot some...here's what I have
in my Module which I called it Conversion...

Function ConvertInchStringtoMmString(pinchString As Text) As Text

Dim mBefore As Text, mAfter As Text, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Then 'modified
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
(mNumStr/25.4) & mAfter 'Modified

End Function

In my Query I have the table with the field, and in one column I used
SideAMM:ConvertInchStringtoMmString([SideA])

Thanks for the help!


:

convertInchStringToMmString
---

Hi Martin,

you can write a function to return the desired results

put this into a general module:

'~~~~~~~~~~~~~~
Function convertInchStringToMmString(pInchString as text)

dim mBefore as text _
, mAfter as Text _
, mNumStr as String _
, mBoo as boolean
, i as integer

mBefore = ""
mAfter = ""
mBoo = false
mNumStr = ""

for i = 1 to len(nz(pInchString,""))
if isnumeric(pInchString) then
mBoo = true
mNumStr = mNumStr & mid(pInchString, i, 1)
else
if mBoo then
mAfter = mAfter & mid(pInchString, i, 1)
else
mBefore = mBefore & mid(pInchString, i, 1)
end if
end if
next i

convertInchStringToMmString = mBefore _
& mNumStr\25.4 _
& mAfter

End Function
'~~~~~~~~~~~~~~

then, in a query, you can see the reulsts by creating a claculated column:

ColumnName: convertIncheStringToMmString([fieldname])

where
ColumnName is what you want to call the column (cannot be the same as a
fieldname in the recordsource)
[fieldname] is the name of the field you want to convert

 

[strive4peace]

you're welcome, Martin happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,

Thanks for your reply. I was able to resolve all the issues.

On the #Error result value, your suggestion to apply it at the query worked
without a glitch.

For handling the dash as a dash or a minus sign, I generated the code is the
same fashion you elegantly presented the mBoo switches.

Thanks for your help and eloquent ideas. I learned a lot from you.

Best regards.

Martin

NZ to send null to function
---

Hi Martin,

to avoind the #Error when there is no value...

convertIncheStringToMmString(nz([fieldname],""))

for cases like "171-173", you will have to handle that in code...

mBoo becomes true when a character is determined to be numeric ... "-"
is not numeric (so it would be copied literally) and the resulting
number, in this case, would incorrectly be 171173

what do you desire to do in cases like this?

~~~

from Help

'~~~~~~~~~~~~~~~~~~~~~~~~~~``
Nz Function

You can use the Nz function to return zero, a zero-length string ("
"), or another specified value when a Variant is Null. For example, you
can use this function to convert a Null value to another value and
prevent it from propagating through an expression.

Syntax

Nz(variant[, valueifnull])

The Nz function has the following arguments.

Argument

variant
A variable of data type Variant.

valueifnull
Optional (unless used in a query).
A Variant that supplies a value to be returned if the variant
argument is Null. This argument enables you to return a value other than
zero or a zero-length string.

Note If you use the Nz function in an expression in a query without
using the valueifnull argument, the results will be a zero-length string
in the fields that contain null values.


'~~~~~~~~~~~~~~~~~~~~~~~~~~``

the thing to keep in mind when using Nz is what it will return if you
don't the specify the second, optional, argument.

If you are using Nz on a field, it will be the data type of that field
-- 0 for numbers (including dates), and an empty string for text or memo

Unbound textbox / combobox / listbox controls on a form are assumed to
have TEXT in them... so an empty string will be returned if nothing is
specified. And, if you specify something, it doesn't have to be 0 or ""

using a generic example...

you could do this:

NZ(..., "no commision is found in the table for this employee")

If you do not specify the optional argument -->
If the expression is bound to a text field, an empty string will be
returned if the field is null. If the expression is bound to a numeric
field, 0 will be returned if the field is null.

it is a good idea to wrap return values from dLookup, etc, in NZ in case
no match was found

.... a good "rule-of-thumb" is to specify the optional arguments (even
though it is not necessary) and do this:

= Nz(DLookup("[Commission]", "Employees", "[EmpID] = " & nz([EmpID],0)),0 )

notice how NZ is used twice -- once to make sure the criteria will be
evaluated, and another time around everything in case dLookup didn't
return a value...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal,

I made a couple of changes to make the "Compile Error. in Query Expression
ConvertInchStringtoMmString([SideA])" message. It was a pop-up window with
only the OK or Help buttons. I wasn't getting the debbuging window. Here's my
progress but now I am stuck with two items.
1. If the field value is null, the query result is #Error instead of a null
value.
2. The query gets stuck when the field value is "171-173". You will notice
that I included the "." and the "-" as part of the numeric field so values
with the decimal point and/or a negative value are computed. The reason for
this is that I am trying to make a temperature computation in another field.
Therefore the negative value is important (I never realized this before).

Thank you for pointing out the backslash use, I wasn't aware of it.

Function ConvertInchStringtoMmString(pinchString As String) As String

Dim mBefore As String, mAfter As String, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Or Mid(pinchString, i, 1)="." Or _
Mid(pinchString, i, 1)="-" Then
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
CStr(FormatNumber((Val(mNumStr)/25.4),2)) & mAfter

End Function


:

compiling code
---

Hi Martin,

you're welcome

I see one problem... there may be more, but this one jumped out

ConvertInchStringtoMmString = mBefore &_
needs to be
ConvertInchStringtoMmString = mBefore & _

when using _ to continue a statement across lines, you must have SPACE
UNDERSCORE, not just underscore

when you compile, which line receives an error?

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~

I had used
mNumStr\25.4
instead of
mNumStr/25.4

so you would get the numeric result as a whole number ...

I suppose it could be griping because you are actually dividing a string
instead of a number... try this:

Val(mNumStr) instead of just mNumStr


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Martin wrote:
Hi Crystal,
Thanks for your response. I forgot to check for automatic replies and I
forgot where I posted the question. Your response is masterful for what I
need. However, I am getting a compile error when running the query. I did a
couple of modifications since I believe you forgot some...here's what I have
in my Module which I called it Conversion...

Function ConvertInchStringtoMmString(pinchString As Text) As Text

Dim mBefore As Text, mAfter As Text, _
mNumStr As String, mBoo As Boolean, i As Integer

mBefore = ""
mAfter = ""
mBoo = False
mNumStr = ""

For i = 1 To Len(Nz(pinchString))
If IsNumeric(Mid(pinchString, i, 1)) Then 'modified
mBoo = True
mNumStr = mNumStr & Mid(pinchString, i, 1)
Else
If mBoo = True Then 'modified
mAfter = mAfter & Mid(pinchString, i, 1)
Else
mBefore = mBefore & Mid(pinchString, i, 1)
End If
End If
Next i

ConvertInchStringtoMmString = mBefore &_
(mNumStr/25.4) & mAfter 'Modified

End Function

In my Query I have the table with the field, and in one column I used
SideAMM:ConvertInchStringtoMmString([SideA])

Thanks for the help!


:

convertInchStringToMmString
---

Hi Martin,

you can write a function to return the desired results

put this into a general module:

'~~~~~~~~~~~~~~
Function convertInchStringToMmString(pInchString as text)

dim mBefore as text _
, mAfter as Text _
, mNumStr as String _
, mBoo as boolean
, i as integer

mBefore = ""
mAfter = ""
mBoo = false
mNumStr = ""

for i = 1 to len(nz(pInchString,""))
if isnumeric(pInchString) then
mBoo = true
mNumStr = mNumStr & mid(pInchString, i, 1)
else
if mBoo then
mAfter = mAfter & mid(pInchString, i, 1)
else
mBefore = mBefore & mid(pInchString, i, 1)
end if
end if
next i

convertInchStringToMmString = mBefore _
& mNumStr\25.4 _
& mAfter

End Function
'~~~~~~~~~~~~~~

then, in a query, you can see the reulsts by creating a claculated column:

ColumnName: convertIncheStringToMmString([fieldname])

where
ColumnName is what you want to call the column (cannot be the same as a
fieldname in the recordsource)
[fieldname] is the name of the field you want to convert