Search for number in memo field

[2Blessed4Stress]

I have a qry that uses a form as it's criteria. The user will enter a number
(ex. 945) and the qry should search in a memo field for 945 exactly. Their
may be a space before and/or after it but their may also be other symbols.
i.e /945 or &945 or 945, etc. I've tried:

Like "* " & " & [Forms]![frm_SrchDescForPart]![PartNumber] & " & " *"

but this only works if their is a space before and after the number. Any
suggestions?

 

[Ken Snell [MVP]]

You want to find 945 only if it's got spaces on both sides, or has a space
on right side and possibly the other characters on the left side?

Like "*[ /&]" & [Forms]![frm_SrchDescForPart]![PartNumber] & " *"

 

[Jeff Boyce]

Count 8 characters in from the left. Isn't that a 'space'?

Look at the 3rd character from the end ... also a space.

What happens if you leave out those two?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[2Blessed4Stress]

I want to find a number anywhere in the field whether it has a space or not.
I just don't want another number to preceed it or proceed it. As I
mentioned, it may have symbols in it because it's a memo field with text,
symbols and numbers.

You want to find 945 only if it's got spaces on both sides, or has a space
on right side and possibly the other characters on the left side?

Like "*[ /&]" & [Forms]![frm_SrchDescForPart]![PartNumber] & " *"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have a qry that uses a form as it's criteria. The user will enter a
number
(ex. 945) and the qry should search in a memo field for 945 exactly.
Their
may be a space before and/or after it but their may also be other symbols.
i.e /945 or &945 or 945, etc. I've tried:

Like "* " & " & [Forms]![frm_SrchDescForPart]![PartNumber] & " & " *"

but this only works if their is a space before and after the number. Any
suggestions?

 

[John W. Vinson]

I want to find a number anywhere in the field whether it has a space or not.
I just don't want another number to preceed it or proceed it. As I
mentioned, it may have symbols in it because it's a memo field with text,
symbols and numbers.

Try a criterion of

LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

This will not find records where the partnumber is the very first or very last
element in the memo field; to catch those too you need three criteria:

LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"
OR LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber]"
OR LIKE [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

 

[John Spencer]

Or you can modify the field value to add a space before and a space after

WHERE " " & [Some Field] & " " LIKE "*[!0-9]" &
[Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

That could still give you erroneous results if you were searching for
897 and there was a string in the memo field of 289756.

If you know the exact characters to not allow on either side of the
number then you can use a like string to exclude the characters.

WHERE " " & [Some Field] & " " LIKE "[ !@#$%^&*() ,.?0123456789a-z]" &
[Forms]![frm_SrchDescForPart]![PartNumber] &
"[ !@#$%^&*() ,.?0123456789a-z]"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I want to find a number anywhere in the field whether it has a space or not.
I just don't want another number to preceed it or proceed it. As I
mentioned, it may have symbols in it because it's a memo field with text,
symbols and numbers.

Try a criterion of

LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

This will not find records where the partnumber is the very first or very last
element in the memo field; to catch those too you need three criteria:

LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"
OR LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber]"
OR LIKE [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

 

[John W. Vinson]

Or you can modify the field value to add a space before and a space after

WHERE " " & [Some Field] & " " LIKE "*[!0-9]" &
[Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

Ah... clever. That would work.

That could still give you erroneous results if you were searching for
897 and there was a string in the memo field of 289756.

Eh? the [!0-9] wildcard specifically excludes digits.

 

[John Spencer MVP]

Sorry John, bad example on my part.

If the field contained " A897-24 " then the record would be returned - Since
there is no number preceding 897 and no number succeeding the 897. My
understanding was that the user wanted whole "word" matches. Where word was
defined as text delimited by having one of multiple different characters
before and after the word. So the delimiter (before or after) would be
defined by a space, a line feed, or any of the following characters:
!?/().,-:; (and perhaps others).

In this case, besides the characters mentioned the poster also wanted to
exclude any letters.

I attempted to do that using the negation operator on the list of characters,
but I'm not sure that would work correctly without testing.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Or you can modify the field value to add a space before and a space after

WHERE " " & [Some Field] & " " LIKE "*[!0-9]" &
[Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

Ah... clever. That would work.

That could still give you erroneous results if you were searching for
897 and there was a string in the memo field of 289756.

Eh? the [!0-9] wildcard specifically excludes digits.

 

[2Blessed4Stress]

Thank you. I used:
LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

Or you can modify the field value to add a space before and a space after

WHERE " " & [Some Field] & " " LIKE "*[!0-9]" &
[Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

That could still give you erroneous results if you were searching for
897 and there was a string in the memo field of 289756.

If you know the exact characters to not allow on either side of the
number then you can use a like string to exclude the characters.

WHERE " " & [Some Field] & " " LIKE "[ !@#$%^&*() ,.?0123456789a-z]" &
[Forms]![frm_SrchDescForPart]![PartNumber] &
"[ !@#$%^&*() ,.?0123456789a-z]"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I want to find a number anywhere in the field whether it has a space or not.
I just don't want another number to preceed it or proceed it. As I
mentioned, it may have symbols in it because it's a memo field with text,
symbols and numbers.

Try a criterion of

LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"

This will not find records where the partnumber is the very first or very last
element in the memo field; to catch those too you need three criteria:

LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"
OR LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber]"
OR LIKE [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"