Query Trouble!

[Chunz]

Hi, am trying to create a Query where Only the Persons who are Married would be showed? ANY IDEAS?

[June7]

Seems like a very simple requirement. What is the difficulty you are having? I presume you are using the Access query builder. Set filter parameters on the Criteria row.

Maybe:

SELECT * FROM [Clients] WHERE [Marital Status] = "M";

Might find this of interest http://www.allenbrowne.com/ser-62.html


Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

[Chunz]

Thanks June 7, i got the first part with the parameters thing, but how do you get it to not prompt you for the values and just display the results?

[newbieaccess203]

Chunz,

Make sure the M is in single quotes. 'M' instead of "M"

let me know if it worked

[JoeM]

What exactly is it prompting you for?

Often times, if you have set up a query that is not a Parameter Query (which prompts the user for an entry), but you are getting a prompt anyway, it is usually sign of an error in your query. Most of the time it is a typo, or you are trying to reference a field name which does not exist in your dataset.

So if it is prompting you for [Marital Status], it is telling you that it cannot find a field with that name. Double-check to make sure that you have the field name typed exactly correctly in your query.

[JoeM]

Make sure the M is in single quotes. 'M' instead of "M"

That is really a SQL requirement.

Access queries can use either single or double quotes.

[newbieaccess203]

Right after checking all that try if single quote works. [Marital Status]='M'

[JoeM]

Right after checking all that try if single quote works. [Marital Status]='M'

As I mentioned, it doesn't make a difference in Access. Try it and see for yourself.

Even if that was an issue, it probably wouldn't prompt you, it just wouldn't return the results you are expecting.
Prompts usually happen when you set up your query as a Parameter Query, or when you use a field name (not field value) it cannot find.
If you enter in a non-existent value for a particular field name, it just won't return any results.

[newbieaccess203]

You are right, but surprisingly I've seen sometimes it prompts in double quotes, which was weird

[JoeM]

You are right, but surprisingly I've seen sometimes it prompts in double quotes, which was weird

It may have just been coincidence, as there might have been other issues (like unrecognized field names or from SQL instead of Access).

[newbieaccess203]

you might be correct

[June7]

I just noticed your table name is spelled CIENTS, not CLIENTS. I presume you used CIENTS.

Is the field name actually spelled MARITAL STATUS or is that an alias?

Did you use the Access query builder to construct or did you type into SQL View?


Did you post real names and phone numbers?

[Chunz]

Yeah Sorry That was a Typo, Thnks For Pointing that out! i am kinda just running through here. and i used the builder as i am not so fancy with SQL

[Chunz]

Thnks newbieaccess203 BUT THAT DOESNT SEEM TO BE THE PROBLEM

[June7]

Have to review your actual code so post the SQL statement of query you built or provide db. Follow instructions at bottom of my post.