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]

Re: Difficult SQL Query

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" <chrisbnp@bells outh.net> wrote in message
news:ITQMc.3876 6$Yw3.37509@big news3.bellsouth .net...[color=blue]
> 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.[/color]

[Chris Belcher]

Re: Difficult SQL Query

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:[color=blue]
> 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))
>[/color]

[Douglas J. Steele]

Re: Difficult SQL Query

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" <chrisbnp@bells outh.net> wrote in message
news:GUTMc.2007 5$GT3.7274@bign ews6.bellsouth. net...[color=blue]
> 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:[color=green]
> > You can't do things like
> >
> > ((IIf([Forms]![Action Item Review]![AIStatusOptions]=2,[CompleteDate] Is
> > Null,(IIf([Forms]![Action Item[/color][/color]
Review]![AIStatusOptions]=3,[CompleteDate] Is[color=blue][color=green]
> > 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[/color][/color]
phrase[color=blue][color=green]
> > 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][/color][/color]
Is[color=blue][color=green]
> > Not Null)
> > OR ([Forms]![Action Item Review]![AIStatusOptions] NOT IN (2, 3) AND [AI
> > Master]![AI Key] Is Not Null))
> >[/color][/color]