Singling out fields by text primary key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all;

How do I do the following? Single out rows in my table by using my primary key as my criteria? My primary key are numbers, but are defined as being text (as I sometimes have letters tagged on the end of the numbers, eg. 8b). How do I tell access to single out all rows which are, for example, between 7 and 16b? I tried using the following criteria: Between "7" And "16b" -- but access says this syntax is incorrect.

What do I do?

Thanks for your help!
 
How do I do the following? Single out rows in my table by using my primary key as
Click to expand...
my criteria? My primary key are numbers, but are defined as being text (as I
sometimes have letters tagged on the end of the numbers, eg. 8b). How do I tell
access to single out all rows which are, for example, between 7 and 16b? I tried
using the following criteria: Between "7" And "16b" -- but access says this syntax
is incorrect.Hi Casa,

I might just write a general function
that accepts a text field value and an
upper and lower text range, then returns
true or false.

But...for your specific instance, try

SELECT yourtable.*
FROM yourtable
WHERE
(((Val([yourfield]))>=7
And
(Val([yourfield]))<=16)
AND
((IIf(Val([yourfield])=16,Right("000" & [yourfield],1)<="b",True))=-1))
 
this may be overkill
(maybe I've overlooked some "trick"), but....
here be a specific function that
you can save in a module and use
in your query:

Public Function fInTextRange(pField As Variant, _
pLower As String, _
pUpper As String) As Boolean
On Error GoTo Err_fInTextRange
'input:
' pField: string that starts with number and may
' or may not end with single char
' pLower: string that starts with number and may
' or may not end with single char
' representing lower test range
' pUpper: string that starts with number and may
' or may not end with single char
' representing upper test range
'output: True if pField falls between "human-recognizable"
' lower and upper range
Dim lngField As Long
Dim strField As String
Dim lngUpper As Long
Dim strUpper As String
Dim lngLower As Long
Dim strLower As String

'if pfield is null, zls, or all spaces, return false
If Len(Trim(pField & "")) = 0 Then
fInTextRange = False
Exit Function
Else
If pField = pUpper _
Or pField = pLower _
Or (Val(pField) > Val(pLower) And Val(pField) < Val(pUpper)) Then
fInTextRange = True
Exit Function
Else
'only test cases left are where
'1)Val(pField)= Val(pLower), pField <> pLower
'or
'2)Val(pField) = Val(pUpper), pField <> pUpper
If Val(pField) = Val(pLower) Or Val(pField) = Val(pUpper) Then
lngField = Val(pField)
strField = Right("0" & pField, 1)
lngUpper = Val(pUpper)
strUpper = Right("0" & pUpper, 1)
lngLower = Val(pLower)
strLower = Right("0" & pLower, 1)
'case 1
If lngField = lngLower Then
If strField > strLower Then
fInTextRange = True
Else
fInTextRange = False
End If
Else
'case 2
If lngField = lngUpper Then
If strField < strUpper Then
fInTextRange = True
Else
fInTextRange = False
End If
Else
fInTextRange = False
End If
End If
Else
fInTextRange = False
End If
End If
End If



Exit_fInTextRange:
Exit Function

Err_fInTextRange:
MsgBox Err.Description
Resume Exit_fInTextRange
End Function

So....in query,

SELECT yourtable.*
FROM yourtable
WHERE
(((fInTextRange([yourfield],"7","16b"))=-1))

or

SELECT yourtable.*
FROM yourtable
WHERE
(((fInTextRange([yourfield],"7c","16"))=-1))

Good luck,

Gary Walter


Gary Walter said:
How do I do the following? Single out rows in my table by using my primary key
Click to expand...
as
my criteria? My primary key are numbers, but are defined as being text (as I
sometimes have letters tagged on the end of the numbers, eg. 8b). How do I tell
access to single out all rows which are, for example, between 7 and 16b? I tried
using the following criteria: Between "7" And "16b" -- but access says this syntax
is incorrect.Hi Casa,

I might just write a general function
that accepts a text field value and an
upper and lower text range, then returns
true or false.

But...for your specific instance, try

SELECT yourtable.*
FROM yourtable
WHERE
(((Val([yourfield]))>=7
And
(Val([yourfield]))<=16)
AND
((IIf(Val([yourfield])=16,Right("000" & [yourfield],1)<="b",True))=-1))
Click to expand...
 
w/o spending too much time on it,
this may be more efficient:

Public Function fInTextRange(pField As Variant, _
pLower As String, _
pUpper As String) As Boolean
On Error GoTo Err_fInTextRange
'input:
' pField: string that starts with number and may
' or may not end with *single char*
' pLower: string that starts with number and may
' or may not end with *single char*
' representing lower test range
' pUpper: string that starts with number and may
' or may not end with *single char*
' representing upper test range
'output: True if pField falls between "human-recognizable"
' lower and upper range
Dim lngField As Long
Dim strField As String
Dim strUpper As String
Dim strLower As String

'if pfield is null, zls, or all spaces, return false
If Len(Trim(pField & "")) = 0 Then
fInTextRange = False
Exit Function
Else
If pField = pUpper _
Or pField = pLower _
Or (Val(pField) > Val(pLower) And Val(pField) < Val(pUpper)) Then
fInTextRange = True
Exit Function
Else
'only test cases left are where
'1)Val(pField)= Val(pLower), pField <> pLower
'or
'2)Val(pField) = Val(pUpper), pField <> pUpper

lngField = Val(pField)
strField = Right(pField, 1)
'case 1
If lngField = Val(pLower) Then
strLower = Right(pLower, 1)
If strField > strLower Then
fInTextRange = True
Else
fInTextRange = False
End If
Else
'case 2
If lngField = Val(pUpper) Then
strUpper = Right(pUpper, 1)
If strField < strUpper Then
fInTextRange = True
Else
fInTextRange = False
End If
Else
fInTextRange = False
End If
End If
End If
End If

Exit_fInTextRange:
Exit Function

Err_fInTextRange:
MsgBox Err.Description
Resume Exit_fInTextRange
End Function
 
What you need to realize is that in a text field "16b" comes before "7" and that
a between operator requires the two values to be in ascending order.

So there are NO values that exist in the range you have specified (as far as the
computer is concerned).

Where Val(PrimaryKey) Between 7 and 16

Field: NumberValue: Val(PrimaryKey)
Criteria: Between 7 and 16

The problem here is that you will get things like 16c and 16d along with 16a and
16b so you need more complex query criteria if you want to limit the letter
portion. If that is acceptable, then our work is done. If it is NOT
acceptable, post back or try using the function posted else-thread.
 
Back
Top