Sorry to Repost Search question

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

Sorry to repost this again but still can't get it to work. Here is my
original post with a few amends to make it a little clearer I hope!!
This is a document index system in Access 2000. I have a form based on a
query that is used as a prompt for parameters on a number of controls. I am
using the Like function in my query on one of the controls which is a text
box giving me the title of documents. I am also suggesting the users use an
* as a wildcard so if they want to find all the document titles which
include "reg" they input *reg* So they can get documents whose titles
include words like "regulations" However there are occasions when they may
want to search for document titles which include initials eg "EC" (we are
in the UK and this is an abbreviation for European Community) eg "EC paper
on Credit Insurance" However if they use EC we get every record which
includes ec in that control eg words like "directive","elective" etc How can
I use the Like function with wild cards to just look for documents whose
title includes the initials "EC"?
Hope this makes sense!
Radnar Midstkogen suggested I needed to have two queries for this to work
one using "like" and one using "where" but I found this was a little
complicated for my prompt form.
Any help greatly appreciated.
Tony Williams
 
One idea if the letters are always separated by a space.

WHERE DocumentTitle Like [Find what] & " *"
OR DocumentTitle Like "* " & [Find What]
OR DocumentTitle Like "* " & [Find What] & " *"

Line 1: Starts with "EC" plus a space
Line 2: Ends with a space and "EC"
Line 3: Has the string space, "EC", space

If you need to get cases such as "EC: Credit Insurance Paper", this gets more
complicated but it can be done

LIKE [Find What] & "[!),-.:;=] *"

The values inside the brackets will have to be in ascii order and I don't
remember that exact sequence right now. So I guess the correct order is up to
the student. Also, you can add any other characters you think you need.

You could also do this using the not operator

LIKE [FindWhat] & "[!0-9a-z]*"

That should give you EC followed by any character that is not 1 to 9 and is not
a to z. Expand the other two lines above.
 
Thanks a lot John some great pointers there. I'll try them all and get back
in a day or if I may if I have a problem'
Thanks again
Tony
John Spencer (MVP) said:
One idea if the letters are always separated by a space.

WHERE DocumentTitle Like [Find what] & " *"
OR DocumentTitle Like "* " & [Find What]
OR DocumentTitle Like "* " & [Find What] & " *"

Line 1: Starts with "EC" plus a space
Line 2: Ends with a space and "EC"
Line 3: Has the string space, "EC", space

If you need to get cases such as "EC: Credit Insurance Paper", this gets more
complicated but it can be done

LIKE [Find What] & "[!),-.:;=] *"

The values inside the brackets will have to be in ascii order and I don't
remember that exact sequence right now. So I guess the correct order is up to
the student. Also, you can add any other characters you think you need.

You could also do this using the not operator

LIKE [FindWhat] & "[!0-9a-z]*"

That should give you EC followed by any character that is not 1 to 9 and is not
a to z. Expand the other two lines above.

Tony said:
Sorry to repost this again but still can't get it to work. Here is my
original post with a few amends to make it a little clearer I hope!!
This is a document index system in Access 2000. I have a form based on a
query that is used as a prompt for parameters on a number of controls. I am
using the Like function in my query on one of the controls which is a text
box giving me the title of documents. I am also suggesting the users use an
* as a wildcard so if they want to find all the document titles which
include "reg" they input *reg* So they can get documents whose titles
include words like "regulations" However there are occasions when they may
want to search for document titles which include initials eg "EC" (we are
in the UK and this is an abbreviation for European Community) eg "EC paper
on Credit Insurance" However if they use EC we get every record which
includes ec in that control eg words like "directive","elective" etc How can
I use the Like function with wild cards to just look for documents whose
title includes the initials "EC"?
Hope this makes sense!
Radnar Midstkogen suggested I needed to have two queries for this to work
one using "like" and one using "where" but I found this was a little
complicated for my prompt form.
Any help greatly appreciated.
Tony Williams
Click to expand...
Click to expand...
 
Thanks John that works just fine!
Tony
John Spencer (MVP) said:
One idea if the letters are always separated by a space.

WHERE DocumentTitle Like [Find what] & " *"
OR DocumentTitle Like "* " & [Find What]
OR DocumentTitle Like "* " & [Find What] & " *"

Line 1: Starts with "EC" plus a space
Line 2: Ends with a space and "EC"
Line 3: Has the string space, "EC", space

If you need to get cases such as "EC: Credit Insurance Paper", this gets more
complicated but it can be done

LIKE [Find What] & "[!),-.:;=] *"

The values inside the brackets will have to be in ascii order and I don't
remember that exact sequence right now. So I guess the correct order is up to
the student. Also, you can add any other characters you think you need.

You could also do this using the not operator

LIKE [FindWhat] & "[!0-9a-z]*"

That should give you EC followed by any character that is not 1 to 9 and is not
a to z. Expand the other two lines above.

Tony said:
Sorry to repost this again but still can't get it to work. Here is my
original post with a few amends to make it a little clearer I hope!!
This is a document index system in Access 2000. I have a form based on a
query that is used as a prompt for parameters on a number of controls. I am
using the Like function in my query on one of the controls which is a text
box giving me the title of documents. I am also suggesting the users use an
* as a wildcard so if they want to find all the document titles which
include "reg" they input *reg* So they can get documents whose titles
include words like "regulations" However there are occasions when they may
want to search for document titles which include initials eg "EC" (we are
in the UK and this is an abbreviation for European Community) eg "EC paper
on Credit Insurance" However if they use EC we get every record which
includes ec in that control eg words like "directive","elective" etc How can
I use the Like function with wild cards to just look for documents whose
title includes the initials "EC"?
Hope this makes sense!
Radnar Midstkogen suggested I needed to have two queries for this to work
one using "like" and one using "where" but I found this was a little
complicated for my prompt form.
Any help greatly appreciated.
Tony Williams
Click to expand...
Click to expand...
 
Back
Top