Setting up a query

[Guest]

Hi there

I know this is a basic question, but I'm trying to design a query in a
database so that I can enter one field (for example a music cd name) and have
it display all information about the record that is searched on. So far, I've
manager to build my query but it shows the entire table. Could anybody assist
me?

Many thanks

Gareth Saunders
ex BOFH

 

[Brendan Reynolds]

SELECT * FROM SomeTable WHERE SomeField = [Enter text to match]

.... or ...

SELECT * FROM SomeTable WHERE SomeField LIKE "*" & [Enter text to match] &
"*"

The text that you enter between the square brackets will be displayed as a
prompt to the user when the query is run.

Also take a look at the Sales by Year report in the Northwind sample
database that comes with Access, for a more sophisticated example that uses
a custom form to prompt the user for the parameters.

 

[Tee See]

Hi Brendan ... I tried your two examples below and I get them to function in
the SQL window. However when I view the output and then change to the query
grid the following is in the criteria field and produces the same output as
the SQL.

Like "*" & [Enter text to match] & "*"

Why would I need to go the SQL route?

Thanks

SELECT * FROM SomeTable WHERE SomeField = [Enter text to match]

... or ...

SELECT * FROM SomeTable WHERE SomeField LIKE "*" & [Enter text to match] &
"*"

The text that you enter between the square brackets will be displayed as a
prompt to the user when the query is run.

Also take a look at the Sales by Year report in the Northwind sample
database that comes with Access, for a more sophisticated example that
uses a custom form to prompt the user for the parameters.

--
Brendan Reynolds
Access MVP

Hi there

I know this is a basic question, but I'm trying to design a query in a
database so that I can enter one field (for example a music cd name) and
have
it display all information about the record that is searched on. So far,
I've
manager to build my query but it shows the entire table. Could anybody
assist
me?

Many thanks

Gareth Saunders
ex BOFH

 

[Brendan Reynolds]

You don't. But I can't put the query design grid in a newsgroup post!

It just takes a lot longer to describe how to do something using the design
grid than it does to post the SQL. So I usually post the SQL first. I'm
usually prepared to explain how to do it using the grid if asked, but I
think you know how to do that now?

--
Brendan Reynolds
Access MVP

Hi Brendan ... I tried your two examples below and I get them to function
in the SQL window. However when I view the output and then change to the
query grid the following is in the criteria field and produces the same
output as the SQL.

Like "*" & [Enter text to match] & "*"

Why would I need to go the SQL route?

Thanks

SELECT * FROM SomeTable WHERE SomeField = [Enter text to match]

... or ...

SELECT * FROM SomeTable WHERE SomeField LIKE "*" & [Enter text to match]
& "*"

The text that you enter between the square brackets will be displayed as
a prompt to the user when the query is run.

Also take a look at the Sales by Year report in the Northwind sample
database that comes with Access, for a more sophisticated example that
uses a custom form to prompt the user for the parameters.

--
Brendan Reynolds
Access MVP

Hi there

I know this is a basic question, but I'm trying to design a query in a
database so that I can enter one field (for example a music cd name) and
have
it display all information about the record that is searched on. So far,
I've
manager to build my query but it shows the entire table. Could anybody
assist
me?

Many thanks

Gareth Saunders
ex BOFH

 

[Tee See]

Thank you. I just thought there might be something I was missing.

Regards

You don't. But I can't put the query design grid in a newsgroup post!

It just takes a lot longer to describe how to do something using the
design grid than it does to post the SQL. So I usually post the SQL first.
I'm usually prepared to explain how to do it using the grid if asked, but
I think you know how to do that now?

--
Brendan Reynolds
Access MVP

Hi Brendan ... I tried your two examples below and I get them to function
in the SQL window. However when I view the output and then change to the
query grid the following is in the criteria field and produces the same
output as the SQL.

Like "*" & [Enter text to match] & "*"

Why would I need to go the SQL route?

Thanks

SELECT * FROM SomeTable WHERE SomeField = [Enter text to match]

... or ...

SELECT * FROM SomeTable WHERE SomeField LIKE "*" & [Enter text to match]
& "*"

The text that you enter between the square brackets will be displayed as
a prompt to the user when the query is run.

Also take a look at the Sales by Year report in the Northwind sample
database that comes with Access, for a more sophisticated example that
uses a custom form to prompt the user for the parameters.

--
Brendan Reynolds
Access MVP


message Hi there

I know this is a basic question, but I'm trying to design a query in a
database so that I can enter one field (for example a music cd name)
and have
it display all information about the record that is searched on. So
far, I've
manager to build my query but it shows the entire table. Could anybody
assist
me?

Many thanks

Gareth Saunders
ex BOFH