compair 3 fields

[Jason Frazer]

How can I compair 3 fields and grab the smallest value.
These fields my have letters in them. The fields with
letters will need to be ignored.

Thanks for your time and help

Jason

 

[Ken Snell]

You'll need to provide more specific info:

what do the values in the fields look like?

what do you mean by "smallest value" (numeric? text comparison)?

 

[Jason Frazer]

the field will have number or letters. All numbers are
integers (no decimals) I want to find the lowest number
valvue and ignore the letters. for example if [field1] =
2 and [field2] = 5 and [field3]=N; the expression need to
return 2 and set that to [field4]

Hope this helps

Jason

 

[Ken Snell]

This may be a possible solution for you.

First, put the following function in a regular module (name the module
basMod):

Public Function ExtractNumbers(strFieldValue As String) As String
Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = strHold
End Function


Then create an select query and put a calculated field in the query grid:
NumVal: Eval(ExtractNumbers([NameOfField])

Then do your sort based on this calculated field.

--
Ken Snell
<MS ACCESS MVP>

the field will have number or letters. All numbers are
integers (no decimals) I want to find the lowest number
valvue and ignore the letters. for example if [field1] =
2 and [field2] = 5 and [field3]=N; the expression need to
return 2 and set that to [field4]

Hope this helps

Jason

-----Original Message-----
You'll need to provide more specific info:

what do the values in the fields look like?

what do you mean by "smallest value" (numeric? text comparison)?

--
Ken Snell
<MS ACCESS MVP>




.