IIf statement question, for calculated field

[rbrule]

Hello,
I have a calculated field in a query that returns " Check reason" if the [registration number ]field is null. The [registration number]field is a text field that has both alpha numeric and alphabetic entries. Is there a way to tell my query to also return "Check reason" if the [registration number]field contains an alphabetic entry?

 

[sfreeman@co.mer]

Hth

If I understand your question, you want "Check Reason" to appear in the calculated field every time the RegNumber is either null, or does not contain a numeric value.

If that is correct,

Create this query:

SELECT
RegTable.RegNumber
, IIf(fAlphaDetect([RegNumber]),"Check Reason","")
AS
CheckReason
FROM
RegTable;

Create this function:

Public Function fAlphaDetect(strInput As Variant) As Boolean
Dim intStringLength As Integer
Dim i As Integer
Dim strA As String

If IsNull(strInput) Or strInput = "" Then
fAlphaDetect = True
Exit Function
ElseIf Not IsNull(strInput) Then
intStringLength = Len(strInput)

fAlphaDetect = True

For i = 1 To intStringLength
strA = Mid(strInput, i, 1)
If IsNumeric(strA) Then
fAlphaDetect = False
Exit Function
End If
Next i

 

[rbrule]

Thank you, I appreciate your help.