>> IsText(mycontrolvalue)

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

Guest

Hi, using Access 2002.

Is there a simple method to test whether a control has a text value?
I want to create a sql string using a value in a control and so the string
needs to have the control value suitable deliminated.

The value could be a date, numeric, text, null.

Just wondering...

Many thanks
Jonathan
 
Dear Jonathan:

In one sense, the value in a text box is always, well, text. "17" is text,
as is "1/2/2006". I'm pretty sure you mean to ask whether the value can be
interpreted as numeric or date. Perhaps you also want to consider "" as
NULL.

For this, you would use testing functions either in the query or in a
separate VBA function. I threw this together to illustrate:

Function TestMe(strValue As String) As Variant
If strValue = "" Then
TestMe = Null
Exit Function
End If
If IsDate(strValue) Then
TestMe = CDate(strValue)
Exit Function
End If
If IsNumeric(strValue) Then
TestMe = CDbl(strValue)
Exit Function
End If
TestMe = strValue
End Function

If you don't want the numeric values as doubles all the time, you could
chose some other type, or types. Hopefully, this will at least introduce
you to the functions to perform what you want.

Tom Ellison
 
Jonathan said:
Hi, using Access 2002.

Is there a simple method to test whether a control has a text value?
I want to create a sql string using a value in a control and so the
string needs to have the control value suitable deliminated.

The value could be a date, numeric, text, null.
Click to expand...

You may find this function useful:

'----- start of code -----
Public Function fncSQLLiteral( _
ArgValue As Variant, ValType As Integer) _
As String

Select Case ValType

Case dbDate
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = Format(ArgValue, "\#mm/dd/yyyy\#")
End If

Case dbText, dbMemo
If IsNull(ArgValue) Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = _
Chr(34) & _
Replace( _
ArgValue, """", """""", , , vbBinaryCompare _
) & _
Chr(34)
End If

Case Else
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = ArgValue
End If

End Select

End Function
'----- end of code -----

I don't think it will work as intended for unbound controls, though, as
then all values will have the text data type.
 
Will numbers always be handled as numeric and not text? If yes, perhaps
something like this:

If IsNull(Me.ControlName.Value) =True Then
' value is Null
ElseIf IsDate(Me.ControlName.Value) = True Then
' value contains a recognized date value/string
ElseIf IsNumeric(Me.ControlName.Value) = True Then
' value is numeric
Else
' value is text
End If
 
Thanks friends, the responses that you each have made are great thanks.

I guess the short answer is that there is no simple test for text. It is
merely a case of elimination. That is, if not (isdate or isnumeric or isnull)
then value is text.

I like Dirk's function to handle formating of dates and of text strings...

Many thanks,
Jonathan
 
Back
Top