Parameter Query

[Guest]

I am trying to replicate the an example of a parameter query I got from
Microsoft Office Assistance that uses more than one parameter.
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx?

This is what I have for mine...


SELECT tblPetInfo.PetID, tblPetInfo.Name, tblPetInfo.Breed,
tblAdopter.LastName, tblAdopter.FirstName
FROM tblPetInfo INNER JOIN tblAdopter ON tblPetInfo.Name = tblAdopter.PetName
WHERE (((tblAdopter.AdoptionDate) Between [Enter Start Date:] And [Enter End
Date:]))
GROUP BY tblPetInfo.PetID, tblPetInfo.Name, tblPetInfo.Breed,
tblAdopter.LastName, tblAdopter.FirstName
HAVING (((tblPetInfo.Breed)=[Enter Breed:]))
ORDER BY tblPetInfo.Breed;

I can enter the parameters but it is not coming back with any results,
results that I know I should be getting back. I have it set up just like the
example. Is there a different way I should be doing it? Ultimately I would
like to create a form to run a report from the query like in the example
document.

Thanks!

 

[Guest]

Verify that tblAdopter.AdoptionDate is a DateTime datatype field and you are
using a recognizable date input at the prompts.

Verify the spelling of breed you want to select and that table contains
breed data in the same manner as you are entering. If it is using a lookup
table and thus storing numbers but you are entering narrative it will not
work.

If all of the above is ok then open the query in design view and save it
with a different name. Click on menu VIEW - SQL View. Edit it like this --
SELECT tblPetInfo.PetID, tblPetInfo.Name, tblPetInfo.Breed,
tblAdopter.LastName, tblAdopter.FirstName
FROM tblPetInfo INNER JOIN tblAdopter ON tblPetInfo.Name = tblAdopter.PetName
WHERE (((tblAdopter.AdoptionDate) Between [Enter Start Date:] And [Enter End
Date:])) And (((tblPetInfo.Breed)=[Enter Breed:]))
GROUP BY tblPetInfo.PetID, tblPetInfo.Name, tblPetInfo.Breed,
tblAdopter.LastName, tblAdopter.FirstName
ORDER BY tblPetInfo.Breed;

Save. Try to run. If not run then edit by removing --
And (((tblPetInfo.Breed)=[Enter Breed:]))

If not run then close without saving, open again in design view and edit by
removing --
(((tblAdopter.AdoptionDate) Between [Enter Start Date:] And [Enter End
Date:])) And

 

[John Spencer]

Access may not be understanding your entries for the date parameters. You can
fix that in a couple ways. One method is to wrap the parameters in conversion
functions to force Access to see them as dates. The second way is to declare
your parameters

SELECT tblPetInfo.PetID, tblPetInfo.Name, tblPetInfo.Breed,
tblAdopter.LastName, tblAdopter.FirstName
FROM tblPetInfo INNER JOIN tblAdopter ON tblPetInfo.Name = tblAdopter.PetName
WHERE (((tblAdopter.AdoptionDate) Between CDate([Enter Start Date:]) And
CDate([Enter End Date:])))
GROUP BY tblPetInfo.PetID, tblPetInfo.Name, tblPetInfo.Breed,
tblAdopter.LastName, tblAdopter.FirstName
HAVING (((tblPetInfo.Breed)=[Enter Breed:]))
ORDER BY tblPetInfo.Breed;

Or the way I prefer.

Parameters [Enter Start Date:] DateTime,
[Enter End Date:] DateTime,
[Enter Breed:] Text (255);
SELECT tblPetInfo.PetID, tblPetInfo.Name, tblPetInfo.Breed,
tblAdopter.LastName, tblAdopter.FirstName
FROM tblPetInfo INNER JOIN tblAdopter ON tblPetInfo.Name = tblAdopter.PetName
WHERE (((tblAdopter.AdoptionDate) Between [Enter Start Date:]
And [Enter End Date:]))
GROUP BY tblPetInfo.PetID, tblPetInfo.Name, tblPetInfo.Breed,
tblAdopter.LastName, tblAdopter.FirstName
HAVING (((tblPetInfo.Breed)=[Enter Breed:]))
ORDER BY tblPetInfo.Breed;