Parameter query returning all records

[Nina Tryggvason]

Hi

I have a table for a list of legal documents - one of the feilds is for the city the document pertains to. this field is filled in as a drop down where the city names are in another table.


I have several queries that I need to do that I want to obtain a list of records for specific cities

one query just returns the client name, annual rent and the city fields

in design view, I have put under city, the criteria [Enter Site Code and Name]


when I run the query, the prompt pops up and when you enter the city name

the resulting table is all 368 records, with the prompted city name standing in for the name in the actual record

instead of returning the 40 records that has the particular city in the field for




I don't know what I've done wrong - the prompts for a date range work just fine

but I don't know if the table field being typed in or being filled in via a lookup table makes a difference

 

[Nina Tryggvason]

oooo the field in the database that's the lookup contains the site code and name,

the field would say V100 Vancouver

and that's what you enter in the prompt

 

[boblarson]

Go to VIEW > SQL View and then copy and paste that here.

 

[Nina Tryggvason]

SELECT AIMS.[Customer Legal Name], AIMS.[Rent - Annual]
FROM AIMS
WHERE (((AIMS.[Site Codes])=[Enter Site Code and Name]));

 

[boblarson]

Try changing it to this:

SELECT AIMS.[Customer Legal Name], AIMS.[Rent - Annual]
FROM AIMS
WHERE (((AIMS.[Site Codes])=' & [Enter Site Code and Name] & '));

Although I would tend to use a form for input instead of a parameter prompt. Of course I don't let users see queries directly either. I let them have access to tables and queries through the use of forms, which you can use to limit what they can do or hurt. If you give them direct access to queries or tables, then you have very little control over what they might put in or do.

 

[Nina Tryggvason]

I copied and pasted that over the code in the SLQ view and saved it

but when I ran it, it didn't prompt me for the city and it returned a table with an empty record


did I do that wrong?


the database is only going to be used by 2 people, so not as worried about preventing mucking up by others.

 

[boblarson]

Sorry, try this

SELECT AIMS.[Customer Legal Name], AIMS.[Rent - Annual]
FROM AIMS
WHERE (((AIMS.[Site Codes])= [Enter Site Code and Name] & "*"));

 

[boblarson]

Actually, the tough part here is that you need to type in what you want and if it isn't typed just correctly, then you might not get what you want. That is another good reason to use a form (with a combo box or list box to select the item). You can present them with the actual items and they select and it gets the right stuff on the first try.

 

[Nina Tryggvason]

I copied and pasted into SLQ view, saved and ran it

Yes, typos are a hazard - I keep a cheat sheet of the codes and city names on my wall

I am getting the prompt now, but am getting the empty return table as a result of the query


I am not sure how to use the form to get query results

I designed a form with just the three fields as a Datasheet, and it just returns all the records

(at least this one returns all the records with the correct city, instead of replacing the city with the prompt data)

I'd rather be able to get the city specific records in one step, then get all records limited to th selected fields and then have to filtre/extract the specific records that I want.

 

[boblarson]

I am getting the prompt now, but am getting the empty return table as a result of the query

Yes, it is probably that you haven't typed the name exactly as it is in the table.

Find attached a sample.

 

[Nina Tryggvason]

I probably should have mentioned that I am using Access 2000

I checked the typing twice before running, and before that, made sure the city I was searching for was listed in the database



that the field in the table is a look up to another table shouldn't matter, right? it should act the same as if the field was typed in?

 

[Nina Tryggvason]

I even went into the table that contains the codes and copied a city

ran the query, pasted the city into the prompt

and got 1 empty record


it's weird that the Between Date And Date queries work

and not the site ones.


I'll have to think of a work around to getting the records, probably a 2 step process.

thank you for trying to help

 

[boblarson]

I probably should have mentioned that I am using Access 2000

Here's an Access 2000 version

 

[Nina Tryggvason]

Here's an Access 2000 version

THANKS

that's amazing

 

[Nina Tryggvason]

Here's an Access 2000 version

Okay, I made the form, wrote the macro, wrote the query and connected them as per the example

I am wondering if it's not working because the feild I am trying to base the query around is not entered (meaning typed uniquely each field) data, but data that is selected from a pick list and resides in another table in the database.


If I point the unbound combo box to the AIMS table and the "site" field - the values returned are the ID number from the SITE table


I can get the list of sites by pointing to the SITE table, but when I run the query, it returns all 368 records, instead of the records that are site specific.



am I just going to have to suck it up and add a new field and just type the site info to get this to work?

the table is not going to grow any more.