sql within sql referencing a value from a form

[Guest]

Hello

I'm trying to run a SQL query that contains a nested query which takes a
value from a forms combobox

SELECT PATIENT_DETAILS.PAT_NO, PATIENT_DETAILS.PAT_ID,
PATIENT_DETAILS.PAT_NAME, PATIENT_DETAILS.PAT_ADDRESS,
PATIENT_DETAILS.PAT_CITY, PATIENT_DETAILS.PAT_COUNTRY,
PATIENT_DETAILS.PAT_GENDER, PATIENT_DETAILS.PAT_DoB, PATIENT_DETAILS.PAT_ZIP,
PATIENT_DETAILS.PAT_PHNUM
FROM PATIENT_DETAILS
WHERE ((([PATIENT_DETAILS].[E_ID])=(SELECT [ePATIENT].[E_ID] FROM [ePATIENT]
WHERE [ePATIENT].[E_CHRONIC_DISEASES]=[Forms]![Search]![cmbdisease])));

i want only the listed columns after the first select statement from my
patient_details table,
but it must only show the fields where the E_ID column in my patients
details table is the same as the E_ID column from my ePATIENT table
where ePatient.E_CHRONIC_DISEASES is equal to the value passed from the
combobox called "cmbdisease" from the form called "Search"

when i run the query i am using a button to load it from my search form, so
the combobox is selected and the search form is present once i click the
button to load the query.

what happens is it returns all the columns i requested from the table i
requested but with no values.

I checked by removing the 2nd select statement and replacing it with an E_ID
number which does exhist, and this works it return the right field values
associated with the number

but with the select statement i returns all columns with no values.

so to narrow the search down theres obviously something wrong in the 2nd
select statement.

Please help, if possible

 

[John Spencer]

I would try modifying the WHERE clause so it uses IN and not EQUAL.

SELECT PATIENT_DETAILS.PAT_NO, PATIENT_DETAILS.PAT_ID,
PATIENT_DETAILS.PAT_NAME, PATIENT_DETAILS.PAT_ADDRESS,
PATIENT_DETAILS.PAT_CITY, PATIENT_DETAILS.PAT_COUNTRY,
PATIENT_DETAILS.PAT_GENDER, PATIENT_DETAILS.PAT_DoB
, PATIENT_DETAILS.PAT_ZIP
, PATIENT_DETAILS.PAT_PHNUM
FROM PATIENT_DETAILS
WHERE ((([PATIENT_DETAILS].[E_ID]) IN (SELECT [ePATIENT].[E_ID] FROM
[ePATIENT]
WHERE [ePATIENT].[E_CHRONIC_DISEASES]=[Forms]![Search]![cmbdisease])));

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Okay changed that "=" sign into "IN" and same result

 

[John Spencer]

Then I suspect that what you are passing into the query with the reference
to the form control - [Forms]![Search]![cmbdisease] - is not what you think
or the values stored in the field - [ePATIENT].[E_CHRONIC_DISEASES] - are
not what you think.

SELECT PATIENT_DETAILS.PAT_NO, PATIENT_DETAILS.PAT_ID,
PATIENT_DETAILS.PAT_NAME, PATIENT_DETAILS.PAT_ADDRESS,
PATIENT_DETAILS.PAT_CITY, PATIENT_DETAILS.PAT_COUNTRY,
PATIENT_DETAILS.PAT_GENDER, PATIENT_DETAILS.PAT_DoB
, PATIENT_DETAILS.PAT_ZIP
, PATIENT_DETAILS.PAT_PHNUM
FROM PATIENT_DETAILS
WHERE ((([PATIENT_DETAILS].[E_ID]) IN (SELECT [ePATIENT].[E_ID] FROM
[ePATIENT]
WHERE [ePATIENT].[E_CHRONIC_DISEASES]=[Forms]![Search]![cmbdisease])));

To test the query try replacing [Forms]![Search]![cmbdisease] with a known
valid value. If it returns the expected records then you should look at the
combobox and make sure the bound column of the combobox contains the data
you want to send to the query. If the query still fails to give you valid
results, then you will need to look at the ePatient table and make sure the
E_Chronic_Diseases is storing what you think it is.

Have you used a combobox as the display control on the field in the table?
A lookup on a field implemented this way usually hides the real value that
is being stored and displays information from a different column of the
lookup table. For example, it may be storing a number and displaying the
text description of the disease.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..