Select specific text from field in query

[Clay Hilton]

I need to query or filter on a field that has various characters and I am
unsure how to do this. I am not skilled in VBA so i am trying to use the
criteria builder in Access on that field. Here is what I am trying to do.
The data in the fields look something like this:

FIELD1
22N 3 33N
4 5 22N
3
3N
4 3
5 4N

I need to query records that have a "3". The three could be in any
position. So here are the possibilties:
1. 3 will be alone in the field
2. 3 will be inbetween other values (space on each side)
3. 3 will begin the field but other data after it.
4. 3 will be last in the field with other data before it.
5. Other?
Any insight is much appreciated.

thanks,
Clay

 

[KARL DEWEY]

Use this criteria --
Like "*3*"
or
Like "*" & [Enter criteria] & "*"

 

[Clay Hilton]

Thanks much Karl. Like "*" & 3 & "*" seems to return all rows with a 3
anywhere in this field. But some of these have a "3N" or a "33N" in them
also. I need to filter out all that do not have a stand alone 3. I tried
adding a "not like" to the criteria but I the problem with that is that a
field could look like this:
Field1
33N 4 3 22 (Row1)
4 33N (Row2)
I would want to return row 1 but not row 2.

thanks,
Clay

Use this criteria --
Like "*3*"
or
Like "*" & [Enter criteria] & "*"

--
KARL DEWEY
Build a little - Test a little

I need to query or filter on a field that has various characters and I am
unsure how to do this. I am not skilled in VBA so i am trying to use the
criteria builder in Access on that field. Here is what I am trying to
do.
The data in the fields look something like this:

FIELD1
22N 3 33N
4 5 22N
3
3N
4 3
5 4N

I need to query records that have a "3". The three could be in any
position. So here are the possibilties:
1. 3 will be alone in the field
2. 3 will be inbetween other values (space on each side)
3. 3 will begin the field but other data after it.
4. 3 will be last in the field with other data before it.
5. Other?
Any insight is much appreciated.

thanks,
Clay

 

[Clay Hilton]

With Karl's help I think I got it. I found out that 33N was needed but not
3N - so here is what I am using:
[FIELD1] Like "*3 *" Or Like "*33N*" Or Like "* 3*" And Not Like "* 3N*"

I believe this is returning any row with a stand alone 3 or a 33N in it (I
also found out that these would be exclusive - a row would not have both).

thanks,
Clay

Thanks much Karl. Like "*" & 3 & "*" seems to return all rows with a 3
anywhere in this field. But some of these have a "3N" or a "33N" in them
also. I need to filter out all that do not have a stand alone 3. I tried
adding a "not like" to the criteria but I the problem with that is that a
field could look like this:
Field1
33N 4 3 22 (Row1)
4 33N (Row2)
I would want to return row 1 but not row 2.

thanks,
Clay

Use this criteria --
Like "*3*"
or
Like "*" & [Enter criteria] & "*"

--
KARL DEWEY
Build a little - Test a little

I need to query or filter on a field that has various characters and I
am
unsure how to do this. I am not skilled in VBA so i am trying to use
the
criteria builder in Access on that field. Here is what I am trying to
do.
The data in the fields look something like this:

FIELD1
22N 3 33N
4 5 22N
3
3N
4 3
5 4N

I need to query records that have a "3". The three could be in any
position. So here are the possibilties:
1. 3 will be alone in the field
2. 3 will be inbetween other values (space on each side)
3. 3 will begin the field but other data after it.
4. 3 will be last in the field with other data before it.
5. Other?
Any insight is much appreciated.

thanks,
Clay

 

[Clay Hilton]

I am having an issue with entering this criteria. When I put this on the
criteria line for the appropriate field,

Like "*3 *" Or Like "*33N*" Or Like "* 3*" And Not Like "* 3N*"

and then test, it returns the correct data. But after I save the query and
go back into query design the criteria is copied to multiple lines (and
split apart in some cases). Should I enter each like statement on a
separate line. Are all of the lines below considered "or" criteria? If so
how would I handle an "And" statement. It is doing the same thing with my
other criteria which is:
"B" Or "C" Or "D" Or "M"

Can anyone tell me why this is happening?

thanks,
Clay

With Karl's help I think I got it. I found out that 33N was needed but
not 3N - so here is what I am using:
[FIELD1] Like "*3 *" Or Like "*33N*" Or Like "* 3*" And Not Like "* 3N*"

I believe this is returning any row with a stand alone 3 or a 33N in it (I
also found out that these would be exclusive - a row would not have both).

thanks,
Clay

Thanks much Karl. Like "*" & 3 & "*" seems to return all rows with a 3
anywhere in this field. But some of these have a "3N" or a "33N" in them
also. I need to filter out all that do not have a stand alone 3. I
tried adding a "not like" to the criteria but I the problem with that is
that a field could look like this:
Field1
33N 4 3 22 (Row1)
4 33N (Row2)
I would want to return row 1 but not row 2.

thanks,
Clay

Use this criteria --
Like "*3*"
or
Like "*" & [Enter criteria] & "*"

--
KARL DEWEY
Build a little - Test a little


:

I need to query or filter on a field that has various characters and I
am
unsure how to do this. I am not skilled in VBA so i am trying to use
the
criteria builder in Access on that field. Here is what I am trying to
do.
The data in the fields look something like this:

FIELD1
22N 3 33N
4 5 22N
3
3N
4 3
5 4N

I need to query records that have a "3". The three could be in any
position. So here are the possibilties:
1. 3 will be alone in the field
2. 3 will be inbetween other values (space on each side)
3. 3 will begin the field but other data after it.
4. 3 will be last in the field with other data before it.
5. Other?
Any insight is much appreciated.

thanks,
Clay

 

[KARL DEWEY]

Should I enter each like statement on a separate line. Are all of the
lines below considered "or" criteria?
Do not change it after Access re-does it.
Separate line are 'OR' statements. Same line is 'AND' statements.