Check if text (any text) or number

[a]

Hi

I have column in query or in a table

Ok

I want to know if this column (all data (rows) value) = text

Or

I want to know if this column (all data (rows) value) = Number

Is there any function or properties in access 2003 can do that

Data already in the data base table or query

 

[Douglas J. Steele]

If I'm understanding you correctly (you want to know for a particular field
in your table whether or not every single row is a numeric value or if one
or more of the rows has text in it), the answer is "no", there's no function
or property built into Access.

However, the following function should give you what you want:

Function DIsNumeric(FieldName As String, TableName As String) As Boolean
Dim rsCurr As DAO.Recordset
Dim booNumeric As Boolean
Dim strSQL As String

booNumeric = True
strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]"

Set rsCurr = CurrentDb.OpenRecordset(strSQL)
Do While rsCurr.EOF = False
If IsNumeric(rsCurr.Fields(0)) = False Then
booNumeric = False
Exit Do
End If
rsCurr.MoveNext
Loop
rsCurr.Close
Set rsCurr = Nothing

DIsNumeric = booNumeric

End Function

(Note, though, that the IsNumeric function may give results you consider to
be incorrect: it will call strings like "10D4" or "2356E6" numeric)

 

[a]

Thank you for your help

but need some help and some comments

How to display message box if there is a text in the table column name
(myColumnisNumberonly)



After check the column

The message appear:

There is a text in your column

In Row number 9

Text is sdfkasdjfl



Ok some notes:

Why use code like this

strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]"



the easy one is:

strsql = "SELECT myColumnisNumberonly from TBlNumber;"

as I know

If I'm understanding you correctly (you want to know for a particular
field in your table whether or not every single row is a numeric value or
if one or more of the rows has text in it), the answer is "no", there's no
function or property built into Access.

However, the following function should give you what you want:

Function DIsNumeric(FieldName As String, TableName As String) As Boolean
Dim rsCurr As DAO.Recordset
Dim booNumeric As Boolean
Dim strSQL As String

booNumeric = True
strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]"

Set rsCurr = CurrentDb.OpenRecordset(strSQL)
Do While rsCurr.EOF = False
If IsNumeric(rsCurr.Fields(0)) = False Then
booNumeric = False
Exit Do
End If
rsCurr.MoveNext
Loop
rsCurr.Close
Set rsCurr = Nothing

DIsNumeric = booNumeric

End Function

(Note, though, that the IsNumeric function may give results you consider
to be incorrect: it will call strings like "10D4" or "2356E6" numeric)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

I have column in query or in a table

Ok

I want to know if this column (all data (rows) value) = text

Or

I want to know if this column (all data (rows) value) = Number

Is there any function or properties in access 2003 can do that

Data already in the data base table or query

 

[Douglas J. Steele]

strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]"

allows for cases where FieldName or TableName contains special characters
(including spaces)

You can change the function to return a message such as what your example
states. Rather than have the function returns True or False, have it return
a string containing the details if text is found (or a zero-length string if
all fields are numeric)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you for your help

but need some help and some comments

How to display message box if there is a text in the table column name
(myColumnisNumberonly)



After check the column

The message appear:

There is a text in your column

In Row number 9

Text is sdfkasdjfl



Ok some notes:

Why use code like this

strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]"



the easy one is:

strsql = "SELECT myColumnisNumberonly from TBlNumber;"

as I know

If I'm understanding you correctly (you want to know for a particular
field in your table whether or not every single row is a numeric value or
if one or more of the rows has text in it), the answer is "no", there's
no function or property built into Access.

However, the following function should give you what you want:

Function DIsNumeric(FieldName As String, TableName As String) As Boolean
Dim rsCurr As DAO.Recordset
Dim booNumeric As Boolean
Dim strSQL As String

booNumeric = True
strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]"

Set rsCurr = CurrentDb.OpenRecordset(strSQL)
Do While rsCurr.EOF = False
If IsNumeric(rsCurr.Fields(0)) = False Then
booNumeric = False
Exit Do
End If
rsCurr.MoveNext
Loop
rsCurr.Close
Set rsCurr = Nothing

DIsNumeric = booNumeric

End Function

(Note, though, that the IsNumeric function may give results you consider
to be incorrect: it will call strings like "10D4" or "2356E6" numeric)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

I have column in query or in a table

Ok

I want to know if this column (all data (rows) value) = text

Or

I want to know if this column (all data (rows) value) = Number

Is there any function or properties in access 2003 can do that

Data already in the data base table or query

 

[Mike Painter]

strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]"

allows for cases where FieldName or TableName contains special
characters (including spaces)

You can change the function to return a message such as what your
example states. Rather than have the function returns True or False,
have it return a string containing the details if text is found (or a
zero-length string if all fields are numeric)

If you just want to know rather than change it, returning a message is
fine.
If you want to actually correct the values using teh function in a query
with criteria set to false will return only those rows you are interested
in.