Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2011
    Posts
    5

    Unanswered: Access Query by State

    I have an Access 2007 Database that I am trying to query the following. Here are the tables
    Name Address City State Zip

    I have a bunch of data in different states but I want the ability to search by state. This part is easy and I know how to do this already just by putting this into the Criteria but what I am trying to accomplish is if I don't put anything in for State that it will just show me all of the data.

    I have tried this by putting in = State in the criteria then run the report and when prompted to put state I put CA and it works perfect but when I don't put anything in this prompt it will not give me any data and I want it to show me every state.

    Please help

    Thank you,

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try: Like '*'
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    Try: Like '*'
    This didn't work. I mean it did give me all the states but I need it to prompt me for the state and if I don't put it in the field then it would give me "*' this response

    I tried = "State" [State] Or Like "*"

    This didn't work either. Do you have any other suggestions?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The where part of the query should be
    Code:
    WHERE <Table>.State LIKE [State?]
    with <Table> being the name of the table where the [State] column comes from. Now if you type an asterisk (*) in the "Enter Parameter Value" window the query will display all states.
    Have a nice day!

  5. #5
    Join Date
    Mar 2011
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    The where part of the query should be
    Code:
    WHERE <Table>.State LIKE [State?]
    with <Table> being the name of the table where the [State] column comes from. Now if you type an asterisk (*) in the "Enter Parameter Value" window the query will display all states.
    I am not doing it in SQL view I was doing it in design view and in the table area under "criteria" I put your statement and it turned it into this

    "WHERE"<"BillToState">".State" Like [State?]

    I don't get any data now if I put the state "CA" or not. I know I am doing something wrong.


  6. #6
    Join Date
    Mar 2011
    Posts
    5
    So I think it would be easier if I showed you the SQL View that I am using so you could help me in this.

    SELECT Orders.agent, Orders.CustomerID, Orders.BillToFirst, Orders.BillToLast, Orders.BillToAddress, Orders.BillToCity, Orders.BillToState, Orders.BillToZip, Leads.PhoneDay, Orders.OrderDate, Items.Description, Items.Qty, Orders.OrderStatus
    FROM Leads INNER JOIN (Orders INNER JOIN Items ON (Orders.OrderID = Items.OrderId) AND (Orders.CustomerID = Items.CustomerID)) ON Leads.XTELELINK = Items.CustomerID
    WHERE (((Orders.agent)>=[Enter Begining Agent ID] And (Orders.agent)<=[Enter End Agent ID]) AND ((Orders.OrderDate)>=[Enter Start date] And (Orders.OrderDate)<=[Enter End Date]))
    ORDER BY Orders.agent, Orders.OrderDate;

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I was talking about a query and it should have turned out as:
    Code:
    WHERE BillToState.State Like [State?]
    But this was only true for a query in Access.

    Now, in the table area under "criteria" simply replace the equal sign you had originally with Like, so = becomes Like.
    Have a nice day!

  8. #8
    Join Date
    Mar 2011
    Posts
    5
    Quote Originally Posted by Sinndho View Post
    I was talking about a query and it should have turned out as:
    Code:
    WHERE BillToState.State Like [State?]
    But this was only true for a query in Access.

    Now, in the table area under "criteria" simply replace the equal sign you had originally with Like, so = becomes Like.
    Well, this worked Like [State?] and I guess the problem that I was having was that if I wanted to see all the States I had to put * to work.

    Thank you for all your help.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •