help with search - using query

[John]

hi,

i have created a search form, and i want to search for a specific item in a
field.
e.g. i have a field called colour, which has record1 = 'red, blue, green'
and another record2 = 'red'

now, when i do the search, and only type in red, to search for red, it only
brings back record 2, and not record 1 as well.

y is this? because i have used a comma?
how would i make it return record 1 as well, as it contains 'red' as well.

TIA

Jon

[Phil Latio]

Your query is returning the correct record - its matching anything that
exactly matches your criteria (in this case 'red')

To return all records containing 'red', you would need to add a LIKE
clause, which will will return records that contain the word 'red'
within the field.

To do this, open the query in design mode, put the following in the
criteria (below) for the field, colour

LIKE "*red*"

Note the asterisks before and after the word 'red', this tells the
query to search for any instance of 'red' within the field.

Hope this helps.

Cheers

phil

John wrote:

hi,

i have created a search form, and i want to search for a specific item in a
field.
e.g. i have a field called colour, which has record1 = 'red, blue, green'
and another record2 = 'red'

now, when i do the search, and only type in red, to search for red, it only
brings back record 2, and not record 1 as well.

y is this? because i have used a comma?
how would i make it return record 1 as well, as it contains 'red' as well.

TIA

Jon

[John]

but what if i wanted to search for blue, or any other colour?

e.g.
record 1 = red, blue, green
record 2 = red
record 3 = red, blue
record 4 = green

so if i searched for red, it would display record 1,2 and 3.
if i search for green, it would display record 1 and 2.

the colours i have mentioned are not in their own seprate fields, they are
in one field called colour.

this is what i currently have in the query;

[Forms]![frmItem]![txtColour]

which basiclly looks in txtcolour field (for any input), and when the search
button is clicked, it loads a query, containing the required informaiton.

dev

"Phil Latio" <ph********@lyc os.co.uk> wrote in message
news:11******** **************@ j33g2000cwa.goo glegroups.com.. .

Your query is returning the correct record - its matching anything that
exactly matches your criteria (in this case 'red')

To return all records containing 'red', you would need to add a LIKE
clause, which will will return records that contain the word 'red'
within the field.

To do this, open the query in design mode, put the following in the
criteria (below) for the field, colour

LIKE "*red*"

Note the asterisks before and after the word 'red', this tells the
query to search for any instance of 'red' within the field.

Hope this helps.

Cheers

phil

John wrote:

hi,

i have created a search form, and i want to search for a specific item in a field.
e.g. i have a field called colour, which has record1 = 'red, blue, green' and another record2 = 'red'

now, when i do the search, and only type in red, to search for red, it only brings back record 2, and not record 1 as well.

y is this? because i have used a comma?
how would i make it return record 1 as well, as it contains 'red' as well.
TIA

Jon

[Bob Quintal]

"John" <jo*****@hotmai .com> wrote in
news:Fe******** *********@newsf e7-win.ntli.net:

Follow the advice that Phil gave you.
LIKE "*" & [Forms]![frmItem]![txtColour] & "*"

Q

but what if i wanted to search for blue, or any other colour?

e.g.
record 1 = red, blue, green
record 2 = red
record 3 = red, blue
record 4 = green

so if i searched for red, it would display record 1,2 and 3.
if i search for green, it would display record 1 and 2.

the colours i have mentioned are not in their own seprate
fields, they are in one field called colour.

this is what i currently have in the query;

[Forms]![frmItem]![txtColour]

which basiclly looks in txtcolour field (for any input), and
when the search button is clicked, it loads a query,
containing the required informaiton.

dev

"Phil Latio" <ph********@lyc os.co.uk> wrote in message
news:11******** **************@ j33g2000cwa.goo glegroups.com.. .

Your query is returning the correct record - its matching
anything that exactly matches your criteria (in this case
'red')

To return all records containing 'red', you would need to add
a LIKE clause, which will will return records that contain
the word 'red' within the field.

To do this, open the query in design mode, put the following
in the criteria (below) for the field, colour

LIKE "*red*"

Note the asterisks before and after the word 'red', this
tells the query to search for any instance of 'red' within
the field.

Hope this helps.

Cheers

phil

John wrote:

> hi,
>
> i have created a search form, and i want to search for a
> specific item in a > field.
> e.g. i have a field called colour, which has record1 =
> 'red, blue, green' > and another record2 = 'red'
>
> now, when i do the search, and only type in red, to search
> for red, it only > brings back record 2, and not record 1 as well.
>
> y is this? because i have used a comma?
> how would i make it return record 1 as well, as it contains
> 'red' as well. >
> TIA
>
> Jon

--
Bob Quintal

PA is y I've altered my email address.

[John]

Hi,

the code you gave me worked, however, now when i do a search for something
else, all the records load up, instead of the ones i need.

what does the * mean? what does it do, as it seems to have messed up my
other searches. now when i click the search button, all the records are
shown.

thank you

John

"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** **************@ 207.35.177.135. ..

"John" <jo*****@hotmai .com> wrote in
news:Fe******** *********@newsf e7-win.ntli.net:

Follow the advice that Phil gave you.
LIKE "*" & [Forms]![frmItem]![txtColour] & "*"

Q

but what if i wanted to search for blue, or any other colour?

e.g.
record 1 = red, blue, green
record 2 = red
record 3 = red, blue
record 4 = green

so if i searched for red, it would display record 1,2 and 3.
if i search for green, it would display record 1 and 2.

the colours i have mentioned are not in their own seprate
fields, they are in one field called colour.

this is what i currently have in the query;

[Forms]![frmItem]![txtColour]

which basiclly looks in txtcolour field (for any input), and
when the search button is clicked, it loads a query,
containing the required informaiton.

dev

"Phil Latio" <ph********@lyc os.co.uk> wrote in message
news:11******** **************@ j33g2000cwa.goo glegroups.com.. .

Your query is returning the correct record - its matching
anything that exactly matches your criteria (in this case
'red')

To return all records containing 'red', you would need to add
a LIKE clause, which will will return records that contain
the word 'red' within the field.

To do this, open the query in design mode, put the following
in the criteria (below) for the field, colour

LIKE "*red*"

Note the asterisks before and after the word 'red', this
tells the query to search for any instance of 'red' within
the field.

Hope this helps.

Cheers

phil

John wrote:
> hi,
>
> i have created a search form, and i want to search for a
> specific item

in a

> field.
> e.g. i have a field called colour, which has record1 =
> 'red, blue,

green'

> and another record2 = 'red'
>
> now, when i do the search, and only type in red, to search
> for red, it

only

> brings back record 2, and not record 1 as well.
>
> y is this? because i have used a comma?
> how would i make it return record 1 as well, as it contains
> 'red' as

well.

>
> TIA
>
> Jon

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

"John" <jo*****@hotmai .com> wrote in
news:gC******** *********@newsf e3-gui.ntli.net:

Hi,

the code you gave me worked, however, now when i do a search
for something
else, all the records load up, instead of the ones i need.

what does the * mean? what does it do, as it seems to have
messed up my other searches. now when i click the search
button, all the records are shown.

thank you

John
Access has a help system. It will explain wildcard searches in
far greater detail that can be done in a post.

In summary LIKE uses the wild cards to show what can be anything
in the search. What's happening is that, when you leave the
textbox empty it searches for anything, and retuurns all entries
with any colour.
You can keep it from finding all the colours in your table by
entering a non-existing colour, say zzzzz. Another way is to
move the asterisks to the textbox, so you can control whether to
use them or not.

There are far more elegant solutions, but if you have to ask
what the * does, they are above your current ability to program
them.

..

"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** **************@ 207.35.177.135. ..

"John" <jo*****@hotmai .com> wrote in
news:Fe******** *********@newsf e7-win.ntli.net:

Follow the advice that Phil gave you.
LIKE "*" & [Forms]![frmItem]![txtColour] & "*"

Q

--
Bob Quintal

PA is y I've altered my email address.

[John]

hi,

when u say move the asterisks to a text box, what do you mean? do you mean
refer it to another field in the form? e.g. LIKE
"[Forms]![frmItem]![txtSize]" & [Forms]![frmItem]![txtColour] &
"[Forms]![frmItem]![txtWeight]"

or do you mean refer to the fields in the table?
could you explain a bit more please.
what are the other elegant solutions?

thanx

John

"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** **************@ 207.35.177.135. ..

"John" <jo*****@hotmai .com> wrote in
news:gC******** *********@newsf e3-gui.ntli.net:

Hi,

the code you gave me worked, however, now when i do a search
for something
else, all the records load up, instead of the ones i need.

what does the * mean? what does it do, as it seems to have
messed up my other searches. now when i click the search
button, all the records are shown.

thank you

John

Access has a help system. It will explain wildcard searches in
far greater detail that can be done in a post.

In summary LIKE uses the wild cards to show what can be anything
in the search. What's happening is that, when you leave the
textbox empty it searches for anything, and retuurns all entries
with any colour.
You can keep it from finding all the colours in your table by
entering a non-existing colour, say zzzzz. Another way is to
move the asterisks to the textbox, so you can control whether to
use them or not.

There are far more elegant solutions, but if you have to ask
what the * does, they are above your current ability to program
them.

.

"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** **************@ 207.35.177.135. ..

"John" <jo*****@hotmai .com> wrote in
news:Fe******** *********@newsf e7-win.ntli.net:

Follow the advice that Phil gave you.
LIKE "*" & [Forms]![frmItem]![txtColour] & "*"

Q

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

"John" <jo*****@hotmai .com> wrote in
news:Hh******** *********@newsf e5-gui.ntli.net:

hi,

when u say move the asterisks to a text box, what do you
mean? do you mean
refer it to another field in the form? e.g. LIKE
"[Forms]![frmItem]![txtSize]" & [Forms]![frmItem]![txtColour]
& "[Forms]![frmItem]![txtWeight]"

or do you mean refer to the fields in the table?
could you explain a bit more please.
what are the other elegant solutions?

thanx

John
What I meant is let the user put the asterisks in the textbox,
when he wants them.
In the query
LIKE [Forms]![frmItem]![txtColour]

In the textbox: find where red is the only colour RED
find where red is the first colour user types RED*
find where red is the last colour user types *RED
find where red is colour anywhere user types *RED*

the elegant solutions are to write Visual Basic Code that
determines if the colour textbox should have wildcards added, or
to redesign the application so that the colour field has one
entry only.

Q

"Bob Quintal" <rq******@sympa tico.ca> wrote in message
news:Xn******** **************@ 207.35.177.135. ..

"John" <jo*****@hotmai .com> wrote in
news:gC******** *********@newsf e3-gui.ntli.net:

> Hi,
>
> the code you gave me worked, however, now when i do a
> search for something
> else, all the records load up, instead of the ones i need.
>
> what does the * mean? what does it do, as it seems to have
> messed up my other searches. now when i click the search
> button, all the records are shown.
>
> thank you
>
> John
>

Access has a help system. It will explain wildcard searches
in far greater detail that can be done in a post.

In summary LIKE uses the wild cards to show what can be
anything in the search. What's happening is that, when you
leave the textbox empty it searches for anything, and
retuurns all entries with any colour.
You can keep it from finding all the colours in your table by
entering a non-existing colour, say zzzzz. Another way is to
move the asterisks to the textbox, so you can control whether
to use them or not.

There are far more elegant solutions, but if you have to ask
what the * does, they are above your current ability to
program them.

.

> "Bob Quintal" <rq******@sympa tico.ca> wrote in message
> news:Xn******** **************@ 207.35.177.135. ..
>> "John" <jo*****@hotmai .com> wrote in
>> news:Fe******** *********@newsf e7-win.ntli.net:
>>
>> Follow the advice that Phil gave you.
>> LIKE "*" & [Forms]![frmItem]![txtColour] & "*"
>>
>> Q
>>

--
Bob Quintal

PA is y I've altered my email address.

--
Bob Quintal

PA is y I've altered my email address.