473,327 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

help with search - using query

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
Mar 14 '06 #1
7 1679
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


Mar 14 '06 #2
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********@lycos.co.uk> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.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

Mar 14 '06 #3
"John" <jo*****@hotmai.com> wrote in
news:Fe*****************@newsfe7-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********@lycos.co.uk> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.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.
Mar 14 '06 #4
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******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"John" <jo*****@hotmai.com> wrote in
news:Fe*****************@newsfe7-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********@lycos.co.uk> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.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.

Mar 18 '06 #5
"John" <jo*****@hotmai.com> wrote in
news:gC*****************@newsfe3-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******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"John" <jo*****@hotmai.com> wrote in
news:Fe*****************@newsfe7-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.
Mar 18 '06 #6
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******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"John" <jo*****@hotmai.com> wrote in
news:gC*****************@newsfe3-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******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"John" <jo*****@hotmai.com> wrote in
news:Fe*****************@newsfe7-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.

Mar 18 '06 #7
"John" <jo*****@hotmai.com> wrote in
news:Hh*****************@newsfe5-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******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"John" <jo*****@hotmai.com> wrote in
news:gC*****************@newsfe3-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******@sympatico.ca> wrote in message
> news:Xn**********************@207.35.177.135...
>> "John" <jo*****@hotmai.com> wrote in
>> news:Fe*****************@newsfe7-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.
Mar 18 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Melissa Kay Beeline | last post by:
OK, here's the sitch : we have an access control system at work that registers ever entry/exit of every employee. I recently made some queries in Access so the ppl in HR could make reports (who...
3
by: ZafT | last post by:
Thanks for any help in advance. I am using this exact same piece of code on a server running PHP4, but for some reason when I run it on dreamhost using PHP5, the search does not register and it...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
3
by: cuties | last post by:
Hi all.... i'm very new to this programming language. i'm required to fulfill this task in the company i'm doing my practical. i hope i can get guide for my problem... Here is the script i...
5
by: deaconj999 | last post by:
Hi, I have nearly finished my database and I would like to add a query that uses a combo box to get the results, not the usual paramater style input. I suppose it would need a form and a query...
5
by: kanley | last post by:
I have a main table with a text description field. In this field, its populated with a string of data. I need to identify from this string of data the name of the vendor using some keywords. I...
8
by: Sham | last post by:
I am trying to perform the following query on a table that has been indexed using Full Text Search. The table contains multiple columns than have been indexed. (Below, all xml columns are...
7
by: microsoftboy | last post by:
Hi, Using the following selection criteria, I am able to list the data from mysql db, but I would like to provide the following options for users: Select All Data, Select None. I am not sure if...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.