select query criteria

W

Wendy

Hello All,
with previous help from this forum, i have a combo box "Topic_MCQ_Sub_ID"
containing values from table "tblTopic_Sub" and also populates with a value
"<All>" via the UNION SELECT.


My Combo Box now looks like:
<ALL>
GI Physiology
PT Anatomy
FA Flactomoy

Now I'd like my subform to filter data based on the selection. It works
fine if I select either GI Physiology or PT Anatomy etc. But if i select
<All>, my subform returns 0 results. Can you please suggest where i may be
going wrong. Many thanks

SELECT tblTopic_MCQ.Topic_MCQ_ID, tblTopic_MCQ.Topic_MCQ_Main_ID,
tblTopic_MCQ.Topic_MCQ_Sub_ID, tblTopic_MCQ.Topic_MCQ_Description,
tblTopic_MCQ.Topic_MCQ_Answer_A, tblTopic_MCQ.Topic_MCQ_Answer_B,
tblTopic_MCQ.Topic_MCQ_Answer_C, tblTopic_MCQ.Topic_MCQ_Answer_D,
tblTopic_MCQ.Topic_MCQ_Answer_E, tblTopic_MCQ.[Topic_MCQ_ Answer_ True],
Right([Topic_MCQ_ Answer_ True],1) & ")" AS Topic_Answer_Short,
tblTopic_MCQ.Topic_MCQ_Order, tblTopic_MCQ.Topic_MCQ_Author,
tblTopic_MCQ.Topic_MCQ_Active, IIf([Topic_MCQ_ Answer_ True] Like
"*_A",[Topic_MCQ_Answer_A],IIf([Topic_MCQ_ Answer_ True] Like
"*_B",[Topic_MCQ_Answer_B],IIf([Topic_MCQ_ Answer_ True] Like
"*_C",[Topic_MCQ_Answer_C],IIf([Topic_MCQ_ Answer_ True] Like
"*_D",[Topic_MCQ_Answer_D],IIf([Topic_MCQ_ Answer_ True] Like
"*_E",[Topic_MCQ_Answer_E],"Good Grief, ask Karen"))))) AS Answer
FROM tblTopic_MCQ
WHERE
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID])
AND
((tblTopic_MCQ.Topic_MCQ_Sub_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]))
OR
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND (([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null))
ORDER BY tblTopic_MCQ.Topic_MCQ_Order;
 
S

SteveS

In the afterupdate event of combo box "Topic_MCQ_Sub_ID" , add this line

(should be one line)

msgbox "Value = " & me.Topic_MCQ_Sub_ID & vbcrlf & "Null = " &
isnull(me.Topic_MCQ_Sub_ID)


then select the <ALL> option. What is displayed in the message box?
 
W

Wendy

Hi Steve,
Thanks for the reply. The message box displays the following:

Value = All
Null = False

...hope that makes sense to you!

Wendy



SteveS said:
In the afterupdate event of combo box "Topic_MCQ_Sub_ID" , add this line

(should be one line)

msgbox "Value = " & me.Topic_MCQ_Sub_ID & vbcrlf & "Null = " &
isnull(me.Topic_MCQ_Sub_ID)


then select the <ALL> option. What is displayed in the message box?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Wendy said:
Hello All,
with previous help from this forum, i have a combo box "Topic_MCQ_Sub_ID"
containing values from table "tblTopic_Sub" and also populates with a value
"<All>" via the UNION SELECT.


My Combo Box now looks like:
<ALL>
GI Physiology
PT Anatomy
FA Flactomoy

Now I'd like my subform to filter data based on the selection. It works
fine if I select either GI Physiology or PT Anatomy etc. But if i select
<All>, my subform returns 0 results. Can you please suggest where i may be
going wrong. Many thanks

SELECT tblTopic_MCQ.Topic_MCQ_ID, tblTopic_MCQ.Topic_MCQ_Main_ID,
tblTopic_MCQ.Topic_MCQ_Sub_ID, tblTopic_MCQ.Topic_MCQ_Description,
tblTopic_MCQ.Topic_MCQ_Answer_A, tblTopic_MCQ.Topic_MCQ_Answer_B,
tblTopic_MCQ.Topic_MCQ_Answer_C, tblTopic_MCQ.Topic_MCQ_Answer_D,
tblTopic_MCQ.Topic_MCQ_Answer_E, tblTopic_MCQ.[Topic_MCQ_ Answer_ True],
Right([Topic_MCQ_ Answer_ True],1) & ")" AS Topic_Answer_Short,
tblTopic_MCQ.Topic_MCQ_Order, tblTopic_MCQ.Topic_MCQ_Author,
tblTopic_MCQ.Topic_MCQ_Active, IIf([Topic_MCQ_ Answer_ True] Like
"*_A",[Topic_MCQ_Answer_A],IIf([Topic_MCQ_ Answer_ True] Like
"*_B",[Topic_MCQ_Answer_B],IIf([Topic_MCQ_ Answer_ True] Like
"*_C",[Topic_MCQ_Answer_C],IIf([Topic_MCQ_ Answer_ True] Like
"*_D",[Topic_MCQ_Answer_D],IIf([Topic_MCQ_ Answer_ True] Like
"*_E",[Topic_MCQ_Answer_E],"Good Grief, ask Karen"))))) AS Answer
FROM tblTopic_MCQ
WHERE
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID])
AND
((tblTopic_MCQ.Topic_MCQ_Sub_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]))
OR
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND (([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null))
ORDER BY tblTopic_MCQ.Topic_MCQ_Order;
Click to expand...
Click to expand...
 
S

SteveS

Hi Wendy,

The problem is in the WHERE clause. It is looking for two conditions:

the field [Topic_MCQ_Main_ID] equal to the control "Topic_MCQ_Main_ID" *AND*
the field [Topic_MCQ_Sub_ID] equal to the control "Topic_MCQ_Sub_ID"

*OR*

the field [Topic_MCQ_Main_ID] equal to the control "Topic_MCQ_Main_ID" *AND*
the CONTROL (combo box) "Topic_MCQ_Sub_ID" equal to NULL.


The combo box "Topic_MCQ_Sub_ID" is returning a vlaue, "GI Physiology" or
"ALL".

There are no entries of "ALL" in the field [Topic_MCQ_Sub_ID].



In the query, try changing the last part of the WHERE clause from:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null

to:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) = "ALL"


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Wendy said:
Hi Steve,
Thanks for the reply. The message box displays the following:

Value = All
Null = False

..hope that makes sense to you!

Wendy



SteveS said:
In the afterupdate event of combo box "Topic_MCQ_Sub_ID" , add this line

(should be one line)

msgbox "Value = " & me.Topic_MCQ_Sub_ID & vbcrlf & "Null = " &
isnull(me.Topic_MCQ_Sub_ID)


then select the <ALL> option. What is displayed in the message box?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Wendy said:
Hello All,
with previous help from this forum, i have a combo box "Topic_MCQ_Sub_ID"
containing values from table "tblTopic_Sub" and also populates with a value
"<All>" via the UNION SELECT.


My Combo Box now looks like:
<ALL>
GI Physiology
PT Anatomy
FA Flactomoy

Now I'd like my subform to filter data based on the selection. It works
fine if I select either GI Physiology or PT Anatomy etc. But if i select
<All>, my subform returns 0 results. Can you please suggest where i may be
going wrong. Many thanks

SELECT tblTopic_MCQ.Topic_MCQ_ID, tblTopic_MCQ.Topic_MCQ_Main_ID,
tblTopic_MCQ.Topic_MCQ_Sub_ID, tblTopic_MCQ.Topic_MCQ_Description,
tblTopic_MCQ.Topic_MCQ_Answer_A, tblTopic_MCQ.Topic_MCQ_Answer_B,
tblTopic_MCQ.Topic_MCQ_Answer_C, tblTopic_MCQ.Topic_MCQ_Answer_D,
tblTopic_MCQ.Topic_MCQ_Answer_E, tblTopic_MCQ.[Topic_MCQ_ Answer_ True],
Right([Topic_MCQ_ Answer_ True],1) & ")" AS Topic_Answer_Short,
tblTopic_MCQ.Topic_MCQ_Order, tblTopic_MCQ.Topic_MCQ_Author,
tblTopic_MCQ.Topic_MCQ_Active, IIf([Topic_MCQ_ Answer_ True] Like
"*_A",[Topic_MCQ_Answer_A],IIf([Topic_MCQ_ Answer_ True] Like
"*_B",[Topic_MCQ_Answer_B],IIf([Topic_MCQ_ Answer_ True] Like
"*_C",[Topic_MCQ_Answer_C],IIf([Topic_MCQ_ Answer_ True] Like
"*_D",[Topic_MCQ_Answer_D],IIf([Topic_MCQ_ Answer_ True] Like
"*_E",[Topic_MCQ_Answer_E],"Good Grief, ask Karen"))))) AS Answer
FROM tblTopic_MCQ
WHERE
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID])
AND
((tblTopic_MCQ.Topic_MCQ_Sub_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]))
OR
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND (([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null))
ORDER BY tblTopic_MCQ.Topic_MCQ_Order;
Click to expand...
Click to expand...
Click to expand...
 
W

Wendy

Hi Steve
have modified the last part of the WHERE clause to the following:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) = "ALL"))

but the results are still the same. When I select "All" no records are
displayed.

Cheers
Wendy



SteveS said:
Hi Wendy,

The problem is in the WHERE clause. It is looking for two conditions:

the field [Topic_MCQ_Main_ID] equal to the control "Topic_MCQ_Main_ID" *AND*
the field [Topic_MCQ_Sub_ID] equal to the control "Topic_MCQ_Sub_ID"

*OR*

the field [Topic_MCQ_Main_ID] equal to the control "Topic_MCQ_Main_ID" *AND*
the CONTROL (combo box) "Topic_MCQ_Sub_ID" equal to NULL.


The combo box "Topic_MCQ_Sub_ID" is returning a vlaue, "GI Physiology" or
"ALL".

There are no entries of "ALL" in the field [Topic_MCQ_Sub_ID].



In the query, try changing the last part of the WHERE clause from:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null

to:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) = "ALL"


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Wendy said:
Hi Steve,
Thanks for the reply. The message box displays the following:

Value = All
Null = False

..hope that makes sense to you!

Wendy



SteveS said:
In the afterupdate event of combo box "Topic_MCQ_Sub_ID" , add this line

(should be one line)

msgbox "Value = " & me.Topic_MCQ_Sub_ID & vbcrlf & "Null = " &
isnull(me.Topic_MCQ_Sub_ID)


then select the <ALL> option. What is displayed in the message box?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Hello All,
with previous help from this forum, i have a combo box "Topic_MCQ_Sub_ID"
containing values from table "tblTopic_Sub" and also populates with a value
"<All>" via the UNION SELECT.


My Combo Box now looks like:
<ALL>
GI Physiology
PT Anatomy
FA Flactomoy

Now I'd like my subform to filter data based on the selection. It works
fine if I select either GI Physiology or PT Anatomy etc. But if i select
<All>, my subform returns 0 results. Can you please suggest where i may be
going wrong. Many thanks

SELECT tblTopic_MCQ.Topic_MCQ_ID, tblTopic_MCQ.Topic_MCQ_Main_ID,
tblTopic_MCQ.Topic_MCQ_Sub_ID, tblTopic_MCQ.Topic_MCQ_Description,
tblTopic_MCQ.Topic_MCQ_Answer_A, tblTopic_MCQ.Topic_MCQ_Answer_B,
tblTopic_MCQ.Topic_MCQ_Answer_C, tblTopic_MCQ.Topic_MCQ_Answer_D,
tblTopic_MCQ.Topic_MCQ_Answer_E, tblTopic_MCQ.[Topic_MCQ_ Answer_ True],
Right([Topic_MCQ_ Answer_ True],1) & ")" AS Topic_Answer_Short,
tblTopic_MCQ.Topic_MCQ_Order, tblTopic_MCQ.Topic_MCQ_Author,
tblTopic_MCQ.Topic_MCQ_Active, IIf([Topic_MCQ_ Answer_ True] Like
"*_A",[Topic_MCQ_Answer_A],IIf([Topic_MCQ_ Answer_ True] Like
"*_B",[Topic_MCQ_Answer_B],IIf([Topic_MCQ_ Answer_ True] Like
"*_C",[Topic_MCQ_Answer_C],IIf([Topic_MCQ_ Answer_ True] Like
"*_D",[Topic_MCQ_Answer_D],IIf([Topic_MCQ_ Answer_ True] Like
"*_E",[Topic_MCQ_Answer_E],"Good Grief, ask Karen"))))) AS Answer
FROM tblTopic_MCQ
WHERE
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID])
AND
((tblTopic_MCQ.Topic_MCQ_Sub_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]))
OR
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND (([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null))
ORDER BY tblTopic_MCQ.Topic_MCQ_Order;
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

Wendy,

I hate to keep asking questions, but since I can't see your mdb, I guess i
have to.

What is the bound column for the combo box "Topic_MCQ_Sub_ID"?

I read your previous posts and it looks like the combo box has 3 columns:

Topic_Sub_Description, Topic_Sub_Main_ID, Topic_Sub_Active


If the bound column is 1, then the criteria would be the description column.
When I had you add the message box, did it show "All" or "(All)"?


If the bound column is 2, then the ID column would be the criteria. The "Is
NULL" should have worked.



Have you tried:

IsNull([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID])

this uses the VBA IsNull() function.


If you want, make a copy of the mdb(s), DELETE ALL SENSITIVE INFO, do a
repair & compile, zip it (WinZip) and email it to me.... I will look at it as
soon as I can..


Hang in there...we'll get this sooner or later.
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Wendy said:
Hi Steve
have modified the last part of the WHERE clause to the following:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) = "ALL"))

but the results are still the same. When I select "All" no records are
displayed.

Cheers
Wendy



SteveS said:
Hi Wendy,

The problem is in the WHERE clause. It is looking for two conditions:

the field [Topic_MCQ_Main_ID] equal to the control "Topic_MCQ_Main_ID" *AND*
the field [Topic_MCQ_Sub_ID] equal to the control "Topic_MCQ_Sub_ID"

*OR*

the field [Topic_MCQ_Main_ID] equal to the control "Topic_MCQ_Main_ID" *AND*
the CONTROL (combo box) "Topic_MCQ_Sub_ID" equal to NULL.


The combo box "Topic_MCQ_Sub_ID" is returning a vlaue, "GI Physiology" or
"ALL".

There are no entries of "ALL" in the field [Topic_MCQ_Sub_ID].



In the query, try changing the last part of the WHERE clause from:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null

to:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) = "ALL"


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Wendy said:
Hi Steve,
Thanks for the reply. The message box displays the following:

Value = All
Null = False

..hope that makes sense to you!

Wendy



:

In the afterupdate event of combo box "Topic_MCQ_Sub_ID" , add this line

(should be one line)

msgbox "Value = " & me.Topic_MCQ_Sub_ID & vbcrlf & "Null = " &
isnull(me.Topic_MCQ_Sub_ID)


then select the <ALL> option. What is displayed in the message box?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Hello All,
with previous help from this forum, i have a combo box "Topic_MCQ_Sub_ID"
containing values from table "tblTopic_Sub" and also populates with a value
"<All>" via the UNION SELECT.


My Combo Box now looks like:
<ALL>
GI Physiology
PT Anatomy
FA Flactomoy

Now I'd like my subform to filter data based on the selection. It works
fine if I select either GI Physiology or PT Anatomy etc. But if i select
<All>, my subform returns 0 results. Can you please suggest where i may be
going wrong. Many thanks

SELECT tblTopic_MCQ.Topic_MCQ_ID, tblTopic_MCQ.Topic_MCQ_Main_ID,
tblTopic_MCQ.Topic_MCQ_Sub_ID, tblTopic_MCQ.Topic_MCQ_Description,
tblTopic_MCQ.Topic_MCQ_Answer_A, tblTopic_MCQ.Topic_MCQ_Answer_B,
tblTopic_MCQ.Topic_MCQ_Answer_C, tblTopic_MCQ.Topic_MCQ_Answer_D,
tblTopic_MCQ.Topic_MCQ_Answer_E, tblTopic_MCQ.[Topic_MCQ_ Answer_ True],
Right([Topic_MCQ_ Answer_ True],1) & ")" AS Topic_Answer_Short,
tblTopic_MCQ.Topic_MCQ_Order, tblTopic_MCQ.Topic_MCQ_Author,
tblTopic_MCQ.Topic_MCQ_Active, IIf([Topic_MCQ_ Answer_ True] Like
"*_A",[Topic_MCQ_Answer_A],IIf([Topic_MCQ_ Answer_ True] Like
"*_B",[Topic_MCQ_Answer_B],IIf([Topic_MCQ_ Answer_ True] Like
"*_C",[Topic_MCQ_Answer_C],IIf([Topic_MCQ_ Answer_ True] Like
"*_D",[Topic_MCQ_Answer_D],IIf([Topic_MCQ_ Answer_ True] Like
"*_E",[Topic_MCQ_Answer_E],"Good Grief, ask Karen"))))) AS Answer
FROM tblTopic_MCQ
WHERE
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID])
AND
((tblTopic_MCQ.Topic_MCQ_Sub_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]))
OR
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND (([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null))
ORDER BY tblTopic_MCQ.Topic_MCQ_Order;
Click to expand...
Click to expand...
Click to expand...
 
W

Wendy

Hi Steve,
Glad you're asking the questions!

--What is the bound column for the combo box "Topic_MCQ_Sub_ID"? = 1
--When I had you add the message box, did it show "All" or "(All)"? = "All"

--If the bound column is 2, then the ID column would be the criteria. The "Is
NULL" should have worked. = <sniff> still no luck :-(
Click to expand...
--IsNull([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) =Ditto

have compacted, zipped and emailed. Hopefully i got your email address
correct, it's only a small db (1mb zipped)

thank you for your help to date.

cheers

wendy







SteveS said:
Wendy,

I hate to keep asking questions, but since I can't see your mdb, I guess i
have to.

What is the bound column for the combo box "Topic_MCQ_Sub_ID"?

I read your previous posts and it looks like the combo box has 3 columns:

Topic_Sub_Description, Topic_Sub_Main_ID, Topic_Sub_Active


If the bound column is 1, then the criteria would be the description column.
When I had you add the message box, did it show "All" or "(All)"?


If the bound column is 2, then the ID column would be the criteria. The "Is
NULL" should have worked.



Have you tried:

IsNull([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID])

this uses the VBA IsNull() function.


If you want, make a copy of the mdb(s), DELETE ALL SENSITIVE INFO, do a
repair & compile, zip it (WinZip) and email it to me.... I will look at it as
soon as I can..


Hang in there...we'll get this sooner or later.
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Wendy said:
Hi Steve
have modified the last part of the WHERE clause to the following:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) = "ALL"))

but the results are still the same. When I select "All" no records are
displayed.

Cheers
Wendy



SteveS said:
Hi Wendy,

The problem is in the WHERE clause. It is looking for two conditions:

the field [Topic_MCQ_Main_ID] equal to the control "Topic_MCQ_Main_ID" *AND*
the field [Topic_MCQ_Sub_ID] equal to the control "Topic_MCQ_Sub_ID"

*OR*

the field [Topic_MCQ_Main_ID] equal to the control "Topic_MCQ_Main_ID" *AND*
the CONTROL (combo box) "Topic_MCQ_Sub_ID" equal to NULL.


The combo box "Topic_MCQ_Sub_ID" is returning a vlaue, "GI Physiology" or
"ALL".

There are no entries of "ALL" in the field [Topic_MCQ_Sub_ID].



In the query, try changing the last part of the WHERE clause from:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null

to:

(([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) = "ALL"


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Hi Steve,
Thanks for the reply. The message box displays the following:

Value = All
Null = False

..hope that makes sense to you!

Wendy



:

In the afterupdate event of combo box "Topic_MCQ_Sub_ID" , add this line

(should be one line)

msgbox "Value = " & me.Topic_MCQ_Sub_ID & vbcrlf & "Null = " &
isnull(me.Topic_MCQ_Sub_ID)


then select the <ALL> option. What is displayed in the message box?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Hello All,
with previous help from this forum, i have a combo box "Topic_MCQ_Sub_ID"
containing values from table "tblTopic_Sub" and also populates with a value
"<All>" via the UNION SELECT.


My Combo Box now looks like:
<ALL>
GI Physiology
PT Anatomy
FA Flactomoy

Now I'd like my subform to filter data based on the selection. It works
fine if I select either GI Physiology or PT Anatomy etc. But if i select
<All>, my subform returns 0 results. Can you please suggest where i may be
going wrong. Many thanks

SELECT tblTopic_MCQ.Topic_MCQ_ID, tblTopic_MCQ.Topic_MCQ_Main_ID,
tblTopic_MCQ.Topic_MCQ_Sub_ID, tblTopic_MCQ.Topic_MCQ_Description,
tblTopic_MCQ.Topic_MCQ_Answer_A, tblTopic_MCQ.Topic_MCQ_Answer_B,
tblTopic_MCQ.Topic_MCQ_Answer_C, tblTopic_MCQ.Topic_MCQ_Answer_D,
tblTopic_MCQ.Topic_MCQ_Answer_E, tblTopic_MCQ.[Topic_MCQ_ Answer_ True],
Right([Topic_MCQ_ Answer_ True],1) & ")" AS Topic_Answer_Short,
tblTopic_MCQ.Topic_MCQ_Order, tblTopic_MCQ.Topic_MCQ_Author,
tblTopic_MCQ.Topic_MCQ_Active, IIf([Topic_MCQ_ Answer_ True] Like
"*_A",[Topic_MCQ_Answer_A],IIf([Topic_MCQ_ Answer_ True] Like
"*_B",[Topic_MCQ_Answer_B],IIf([Topic_MCQ_ Answer_ True] Like
"*_C",[Topic_MCQ_Answer_C],IIf([Topic_MCQ_ Answer_ True] Like
"*_D",[Topic_MCQ_Answer_D],IIf([Topic_MCQ_ Answer_ True] Like
"*_E",[Topic_MCQ_Answer_E],"Good Grief, ask Karen"))))) AS Answer
FROM tblTopic_MCQ
WHERE
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID])
AND
((tblTopic_MCQ.Topic_MCQ_Sub_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]))
OR
(((tblTopic_MCQ.Topic_MCQ_Main_ID)=[Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Main_ID]) AND (([Forms]![frmTopic_MCQ_Mainform]![Topic_MCQ_Sub_ID]) Is Null))
ORDER BY tblTopic_MCQ.Topic_MCQ_Order;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Option Buttons to Pass Criteria to Query 6
IIf statement in query criteria 3
Switching the select criteria between a Form field and ALL 0

Top