Search for number in memo field

  • Thread starter Thread starter 2Blessed4Stress
  • Start date Start date
2

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?
 
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] & " *"
 
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
 
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.

Ken Snell said:
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/




2Blessed4Stress said:
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?
Click to expand...
Click to expand...
 
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.
Click to expand...

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]*"
 
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.
Click to expand...

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]*"
Click to expand...
 
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]*"
Click to expand...

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.
Click to expand...

Eh? the [!0-9] wildcard specifically excludes digits.
 
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]*"
Click to expand...

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.
Click to expand...

Eh? the [!0-9] wildcard specifically excludes digits.
Click to expand...
 
Thank you. I used:
LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*"


John Spencer said:
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.
Click to expand...

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]*"
Click to expand...
Click to expand...
 
Back
Top