Thread: Parameter Question
06-14-06, 15:46 #1Registered User
- Join Date
- May 2006
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?
06-14-06, 18:19 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
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
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
06-15-06, 04:55 #3Registered User
Originally Posted by glpkid
- Join Date
- Jun 2006
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
FROM PaymentEnquiries LEFT JOIN Failure ON PaymentEnquiries.index = Failure.indexInPaymentEnqRec;
Last edited by mkggoh; 06-15-06 at 05:00.