Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    4

    Unanswered: Parameter Question

    I have a form and a Query based on a form. I have 3 different columns in my query. One is City, second is citycode, and the third is Cell Phone Provider. Now each city has a citycode and vice versa, however, not each city has a cell phone provider. Which means there are some boxes that are blank. My problem is that when I search for all cities that begin with say, "A", it only brings up the cities that have a value for the cell phone provider, and the ones that are blank don't come up. Could someone help me out?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You don't really provide us with much information, and what you do provide is sort of murky.
    I have a form and a Query based on a form
    How do you base a Query on a Form? A Form based on a Query I understand. Then you give us no idea of how your Query is constructed. Don't mean to be critical, just pointing out that in order to give you help we need to know what you're doing/what you've tried already! Having said that, this should do what you need:

    In design view select the table(s) you want your Query based on. Drag each field (City, CityCode, CellProvider) onto the Query grid. Under Criteria for the City field type in the following:

    Like [Enter Cities First Letter] & "*"

    This tells the Query to return any record with a City that begins with the entered parameter. This has been tested in Access 2000 and is so basic it should work in any version. It will return all appropriate cities, with or without a cell phone provider. Let us know if you have any problems!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by glpkid
    I have a form and a Query based on a form. I have 3 different columns in my query. One is City, second is citycode, and the third is Cell Phone Provider. Now each city has a citycode and vice versa, however, not each city has a cell phone provider. Which means there are some boxes that are blank. My problem is that when I search for all cities that begin with say, "A", it only brings up the cities that have a value for the cell phone provider, and the ones that are blank don't come up. Could someone help me out?

    I think you problem explain in the other way around, u cant create a query base on a form. i agree with Missinglinq

    i think u need a left join. for your example, probably u have 2 tables:
    city table (Which has fields like cityName, citycode).
    phone provider table(which has fields like providerName, cityName).

    Now, create a query using design view, select both table into your view. if they r not link, link them with relationship, the link will be city.cityName = phoneprovider.cityName, then a link line will shown, then select the link, right click on it, select the join properties, then there are 3 option u can select, choose 'Include all records from your city table only', then u will get the records with service provider city query out.

    then when u select out the cityName with phone provider only, to get cityName starting with A, will just like Missinglinq said, use a WHERE clause, WHERE cityName LIKE 'A*', but i prefer to use 'A%' because last time i use application to do this, 'A*' wont work, but 'A%' does. However, i tried, if u do it on query, 'A*' does work.

    Hope this helps.


    Example of my query
    SELECT
    FROM PaymentEnquiries LEFT JOIN Failure ON PaymentEnquiries.index = Failure.indexInPaymentEnqRec;
    Last edited by mkggoh; 06-15-06 at 05:00.

Posting Permissions

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