By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,780 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,780 IT Pros & Developers. It's quick & easy.

Difficult SQL Query

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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******@bellsouth.net> wrote in message
news:IT*******************@bignews3.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.

Nov 13 '05 #2

P: n/a
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))

Nov 13 '05 #3

P: n/a
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******@bellsouth.net> wrote in message
news:GU******************@bignews6.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))

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.