If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Search for number in memo field
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? |
#2
|
|||
|
|||
Search for number in memo field
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" wrote in message ... 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? |
#3
|
|||
|
|||
Search for number in memo field
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" wrote in message ... 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? |
#4
|
|||
|
|||
Search for number in memo field
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 [MVP]" wrote: 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" wrote in message ... 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? |
#5
|
|||
|
|||
Search for number in memo field
On Wed, 24 Jun 2009 14:02:01 -0700, 2Blessed4Stress
wrote: 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 [MVP] |
#6
|
|||
|
|||
Search for number in memo field
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 '================================================= === John W. Vinson wrote: On Wed, 24 Jun 2009 14:02:01 -0700, 2Blessed4Stress wrote: 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]*" |
#7
|
|||
|
|||
Search for number in memo field
On Wed, 24 Jun 2009 20:33:25 -0400, John Spencer wrote:
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 W. Vinson [MVP] |
#8
|
|||
|
|||
Search for number in memo field
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 John W. Vinson wrote: On Wed, 24 Jun 2009 20:33:25 -0400, John Spencer wrote: 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. |
#9
|
|||
|
|||
Search for number in memo field
Thank you. I used:
LIKE "*[!0-9]" & [Forms]![frm_SrchDescForPart]![PartNumber] & "[!0-9]*" "John Spencer" wrote: 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 '================================================= === John W. Vinson wrote: On Wed, 24 Jun 2009 14:02:01 -0700, 2Blessed4Stress wrote: 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]*" |
Thread Tools | |
Display Modes | |
|
|