Query for combo box returning no result

[gmazza via AccessMonster.com]

Hi there,
I have a query for a combo box. I need certain values to show in the combo
box based on the query but I can't seem to get it working.

My form (PatientData) is based on Table 3.
I have a label on my form that is being grabbed from a value in Table 1. Each
value also has another column with it, that being the SequenceNo.
I need the combo to show values in Table 2 with the same SequenceNo as in
Table 1.

So here is a small example:
Table 1 - Columns: SequenceNo CriteriaValue
1
Cough Day 1
2
Cough Day 2

Table 2 - Columns: CriteriaOptionId SequenceNo
CriteriaOptionValue
1
1 yes
2
1 no

Label on form: Cough Day 1 combo box - want to show yes and no

I just don't know how to write the sql.
I have this but its not showing anything:
SELECT CriteriaOption.CriteriaOptionValueText, CriteriaOption.SequenceNo
FROM Criteria INNER JOIN CriteriaOption ON Criteria.SequenceNo =
CriteriaOption.SequenceNo
WHERE (((CriteriaOption.SequenceNo) In (Select SequenceNo from Criteria where
CriteriaValue = Forms!PatientData.Label1)));

Label1 is Cough Day 1

Hope this makes sense, any help is appreciated!

 

[KARL DEWEY]

Don't think you can use a label. Use a text box and color it like a label.