compair 3 fields

  • Thread starter Thread starter Jason Frazer
  • Start date Start date
J

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
 
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)?
 
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
 
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>


Jason Frazer said:
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>




.
Click to expand...
Click to expand...
 
Back
Top