Replacing the cell value by another value

[SD]

Hello All,

I have imported a CSV in MS access. However I see that the name field
has some numbers in it. Numbers are random and the field is text type.
How do I create a query which would check for a number pattern and if
found would replace it with a particular string say"it was a number".
Can it be done through Query wizard ? I am new to access and would
really appreciate your help.

thanks
SD

 

[John W. Vinson]

Hello All,

I have imported a CSV in MS access. However I see that the name field
has some numbers in it. Numbers are random and the field is text type.
How do I create a query which would check for a number pattern and if
found would replace it with a particular string say"it was a number".
Can it be done through Query wizard ? I am new to access and would
really appreciate your help.

thanks
SD

You can create a Query based on the table; add a calculated field by typing

ItsANumber: IsNumeric([fieldname])

on the calculated field. This value will be True if the contents of fieldname
are a valid number. Note that there are some non-obvious strings that are
valid numbers, such as 1E6 or 211145- or 1.05D+08.

If you want to search for records where the field contains any digit (e.g.
"FGH3AAB") you can use a query with a criterion of

LIKE "*#*"

* is a wildcard matching any string, # is a wildcard matching any digit.