Newbie needs help with Parameter Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there, I created a parameter query that would prompt for a "country code"
to be inputted to gather results. Data from this is based on a query that
contains staff info and the country where they own real estate, namely: NM,
KO, PH, FR and ALL (for all countries).
The problem that I am encountering is this: I can pull results by entering
"ALL" in the prompt, but couldn't get any when I entered the other codes. I
am not sure why my results are blank when I know for a fact that there are
records tied to those countries coded other than "ALL".
I am not sure if it has something to do with the properties of that field.

Please help!

Thanks, Marie
 
Hi there, I created a parameter query that would prompt for a "country code"
to be inputted to gather results. Data from this is based on a query that
contains staff info and the country where they own real estate, namely: NM,
KO, PH, FR and ALL (for all countries).
The problem that I am encountering is this: I can pull results by entering
"ALL" in the prompt, but couldn't get any when I entered the other codes. I
am not sure why my results are blank when I know for a fact that there are
records tied to those countries coded other than "ALL".
I am not sure if it has something to do with the properties of that field.

Please help!

Thanks, Marie
Click to expand...

There's something wrong with the query, it would seem.

We cannot see the query, so we obviously cannot tell what's wrong.

Please open the query in SQL view and post it here.


John W. Vinson[MVP]
 
As requested, here's the SQL:

SELECT EMPL_TBL.INFO AS [Staff Name], PRPTY_TBL.CNTRYCOD AS Country,
EMPLSTATUS_TBL.STATUS AS [Staff Status]
WHERE (((PRPTY_TBL.CNTRYCOD) Between [Enter Country] And [Enter Country])
AND ((EMPLSTATUS_TBL.STATUS)=Active))

ORDER BY EMPL_TBL.INFO;
 
As requested, here's the SQL:

SELECT EMPL_TBL.INFO AS [Staff Name], PRPTY_TBL.CNTRYCOD AS Country,
EMPLSTATUS_TBL.STATUS AS [Staff Status]
WHERE (((PRPTY_TBL.CNTRYCOD) Between [Enter Country] And [Enter Country])
AND ((EMPLSTATUS_TBL.STATUS)=Active))
Click to expand...

This makes no sense. It will prompt *once* Enter Country, and then
return all records "between" CO and CO (say), since if you use a
parameter twice in a query, it just prompts once and uses the entered
value repeatedly. BETWEEN is used with *two different* arguments, to
select a range (of dates, of numbers, or an alphabetical range). And
there is absolutely nothing in this query that would deal with ALL.

I would suggest

SELECT EMPL_TBL.INFO AS [Staff Name], PRPTY_TBL.CNTRYCOD AS Country,
EMPLSTATUS_TBL.STATUS AS [Staff Status]
WHERE
(PRPTY_TBL.CNTRYCOD = [Enter Country] OR [Enter Country] = "ALL")
AND EMPLSTATUS_TBL.STATUS="Active";


John W. Vinson[MVP]
 
Thanks John! It worked. I told you, I am new at this..

John Vinson said:
As requested, here's the SQL:

SELECT EMPL_TBL.INFO AS [Staff Name], PRPTY_TBL.CNTRYCOD AS Country,
EMPLSTATUS_TBL.STATUS AS [Staff Status]
WHERE (((PRPTY_TBL.CNTRYCOD) Between [Enter Country] And [Enter Country])
AND ((EMPLSTATUS_TBL.STATUS)=Active))
Click to expand...

This makes no sense. It will prompt *once* Enter Country, and then
return all records "between" CO and CO (say), since if you use a
parameter twice in a query, it just prompts once and uses the entered
value repeatedly. BETWEEN is used with *two different* arguments, to
select a range (of dates, of numbers, or an alphabetical range). And
there is absolutely nothing in this query that would deal with ALL.

I would suggest

SELECT EMPL_TBL.INFO AS [Staff Name], PRPTY_TBL.CNTRYCOD AS Country,
EMPLSTATUS_TBL.STATUS AS [Staff Status]
WHERE
(PRPTY_TBL.CNTRYCOD = [Enter Country] OR [Enter Country] = "ALL")
AND EMPLSTATUS_TBL.STATUS="Active";


John W. Vinson[MVP]
Click to expand...
 
Back
Top