Difficult SQL Query

[Chris Belcher]

I've written a Query that gets info from an Option Group and some combo
boxes that returns the correct data if,in the last "AND" of the "WHERE"
I compare [AI Detail].[Assignee Short] to "RS"
When I try to compare [AI Detail].[Assignee Short] to [Forms]![Action
Item Review]![cboAssignee], a combo box populated with RS it returns
nothing. I would think that both of the following queries would be
equal. Where is my logic failing?

I've looked at this for 2 days and either haven't found a reference to
the problem or didn't recognize it when I saw it.

*************** *************** *************** *************** ***********
SQL with "RS"
*************** *************** *************** *************** ***********
SELECT DISTINCT [AI Master].[AI Key], [AI Master].[Assign Date], [AI
Master].[Expected Date], [AI Master].CompleteDate, [AI Master].[AI Title]
FROM [AI Master] INNER JOIN [AI Detail] ON [AI Master].[AI Key] = [AI
Detail].[AI Key]
WHERE ((([AI Master].[Expected Date]) Between [Forms]![Action Item
Review]![S] And [Forms]![Action Item Review]![E]) AND
((IIf([Forms]![Action Item Review]![AIStatusOptions]=2,[CompleteDate] Is
Null,(IIf([Forms]![Action Item
Review]![AIStatusOptions]=3,[CompleteDate] Is Not Null,[AI Master]![AI
Key] Is Not Null))))<>False ) AND (([AI Detail].[Assignee Short])="RS"))
ORDER BY [AI Master].[AI Key] DESC;

*************** *************** *************** *************** **********
SQL that references the Combo Box containing RS
*************** *************** *************** *************** ***********
SELECT DISTINCT [AI Master].[AI Key], [AI Master].[Assign Date], [AI
Master].[Expected Date], [AI Master].CompleteDate, [AI Master].[AI Title]
FROM [AI Master] INNER JOIN [AI Detail] ON [AI Master].[AI Key] = [AI
Detail].[AI Key]
WHERE ((([AI Master].[Expected Date]) Between [Forms]![Action Item
Review]![S] And [Forms]![Action Item Review]![E]) AND
((IIf([Forms]![Action Item Review]![AIStatusOptions]=2,[CompleteDate] Is
Null,(IIf([Forms]![Action Item
Review]![AIStatusOptions]=3,[CompleteDate] Is Not Null,[AI Master]![AI
Key] Is Not Null))))<>False ) AND (([AI Detail].[Assignee
Short])=[Forms]![Action Item Review]![cboAssignee]))
ORDER BY [AI Master].[AI Key] DESC;
I'm a Newbie! If there's a better mousetrap I'll stick my fingers in it!
Thanks in advance.

[Douglas J. Steele]

You can't do things like

((IIf([Forms]![Action Item Review]![AIStatusOptions]=2,[CompleteDate] Is
Null,(IIf([Forms]![Action Item Review]![AIStatusOptions]=3,[CompleteDate] Is
Not Null,[AI Master]![AI Key] Is Not Null))))<>False )

in SQL. An IIf statement can only return a value: it can't return a phrase
that's supposed to be part of the SQL statement.

You'll need to use something like:

(([Forms]![Action Item Review]![AIStatusOptions]=2 AND [CompleteDate] Is
Null)
OR ([Forms]![Action Item Review]![AIStatusOptions]=3 AND [CompleteDate] Is
Not Null)
OR ([Forms]![Action Item Review]![AIStatusOptions] NOT IN (2, 3) AND [AI
Master]![AI Key] Is Not Null))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Chris Belcher" <ch******@bells outh.net> wrote in message
news:IT******** ***********@big news3.bellsouth .net...

I've written a Query that gets info from an Option Group and some combo
boxes that returns the correct data if,in the last "AND" of the "WHERE"
I compare [AI Detail].[Assignee Short] to "RS"
When I try to compare [AI Detail].[Assignee Short] to [Forms]![Action
Item Review]![cboAssignee], a combo box populated with RS it returns
nothing. I would think that both of the following queries would be
equal. Where is my logic failing?

I've looked at this for 2 days and either haven't found a reference to
the problem or didn't recognize it when I saw it.

*************** *************** *************** *************** ***********
SQL with "RS"
*************** *************** *************** *************** ***********
SELECT DISTINCT [AI Master].[AI Key], [AI Master].[Assign Date], [AI
Master].[Expected Date], [AI Master].CompleteDate, [AI Master].[AI Title]
FROM [AI Master] INNER JOIN [AI Detail] ON [AI Master].[AI Key] = [AI
Detail].[AI Key]
WHERE ((([AI Master].[Expected Date]) Between [Forms]![Action Item
Review]![S] And [Forms]![Action Item Review]![E]) AND
((IIf([Forms]![Action Item Review]![AIStatusOptions]=2,[CompleteDate] Is
Null,(IIf([Forms]![Action Item
Review]![AIStatusOptions]=3,[CompleteDate] Is Not Null,[AI Master]![AI
Key] Is Not Null))))<>False ) AND (([AI Detail].[Assignee Short])="RS"))
ORDER BY [AI Master].[AI Key] DESC;

*************** *************** *************** *************** **********
SQL that references the Combo Box containing RS
*************** *************** *************** *************** ***********
SELECT DISTINCT [AI Master].[AI Key], [AI Master].[Assign Date], [AI
Master].[Expected Date], [AI Master].CompleteDate, [AI Master].[AI Title]
FROM [AI Master] INNER JOIN [AI Detail] ON [AI Master].[AI Key] = [AI
Detail].[AI Key]
WHERE ((([AI Master].[Expected Date]) Between [Forms]![Action Item
Review]![S] And [Forms]![Action Item Review]![E]) AND
((IIf([Forms]![Action Item Review]![AIStatusOptions]=2,[CompleteDate] Is
Null,(IIf([Forms]![Action Item
Review]![AIStatusOptions]=3,[CompleteDate] Is Not Null,[AI Master]![AI
Key] Is Not Null))))<>False ) AND (([AI Detail].[Assignee
Short])=[Forms]![Action Item Review]![cboAssignee]))
ORDER BY [AI Master].[AI Key] DESC;
I'm a Newbie! If there's a better mousetrap I'll stick my fingers in it!
Thanks in advance.

[Chris Belcher]

Thanks Doug. Actually the IIf's evaluated correctly (Now I don't really
know why other than I'm checking for the existance of a date entry. but
I'm still stumped... Let me simplify it for another shot at the question.

SELECT [AI Detail].[AI Key], [AI Detail].[Assignee Short]
FROM [AI Detail]
WHERE [AI Detail].[Assignee Short]=[Forms]![Action Item
Review]![cbxAssignee];

Does not evaluate to the same results as even though the combo box
contains JV

SELECT [AI Detail].[AI Key], [AI Detail].[Assignee Short]
FROM [AI Detail]
WHERE [AI Detail].[Assignee Short]= "JV";


Douglas J. Steele wrote:

You can't do things like

((IIf([Forms]![Action Item Review]![AIStatusOptions]=2,[CompleteDate] Is
Null,(IIf([Forms]![Action Item Review]![AIStatusOptions]=3,[CompleteDate] Is
Not Null,[AI Master]![AI Key] Is Not Null))))<>False )

in SQL. An IIf statement can only return a value: it can't return a phrase
that's supposed to be part of the SQL statement.

You'll need to use something like:

(([Forms]![Action Item Review]![AIStatusOptions]=2 AND [CompleteDate] Is
Null)
OR ([Forms]![Action Item Review]![AIStatusOptions]=3 AND [CompleteDate] Is
Not Null)
OR ([Forms]![Action Item Review]![AIStatusOptions] NOT IN (2, 3) AND [AI
Master]![AI Key] Is Not Null))

[Douglas J. Steele]

You sure the combo box contains JV, and not, say "JV " or " JV "?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Chris Belcher" <ch******@bells outh.net> wrote in message
news:GU******** **********@bign ews6.bellsouth. net...

Thanks Doug. Actually the IIf's evaluated correctly (Now I don't really
know why other than I'm checking for the existance of a date entry. but
I'm still stumped... Let me simplify it for another shot at the question.

SELECT [AI Detail].[AI Key], [AI Detail].[Assignee Short]
FROM [AI Detail]
WHERE [AI Detail].[Assignee Short]=[Forms]![Action Item
Review]![cbxAssignee];

Does not evaluate to the same results as even though the combo box
contains JV

SELECT [AI Detail].[AI Key], [AI Detail].[Assignee Short]
FROM [AI Detail]
WHERE [AI Detail].[Assignee Short]= "JV";


Douglas J. Steele wrote:

You can't do things like

((IIf([Forms]![Action Item Review]![AIStatusOptions]=2,[CompleteDate] Is
Null,(IIf([Forms]![Action Item Review]![AIStatusOptions]=3,[CompleteDate] Is Not Null,[AI Master]![AI Key] Is Not Null))))<>False )

in SQL. An IIf statement can only return a value: it can't return a phrase that's supposed to be part of the SQL statement.

You'll need to use something like:

(([Forms]![Action Item Review]![AIStatusOptions]=2 AND [CompleteDate] Is
Null)
OR ([Forms]![Action Item Review]![AIStatusOptions]=3 AND [CompleteDate] Is Not Null)
OR ([Forms]![Action Item Review]![AIStatusOptions] NOT IN (2, 3) AND [AI
Master]![AI Key] Is Not Null))