Query criteria problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have a query with the following SQL:

SELECT [Non-Acceptance].[Request ID], [Non-Acceptance].[First Name],
[Non-Acceptance].Surname, [Non-Acceptance].[Invoiced?],
[Non-Acceptance].[Agent ID] AS [Assigned To], [Non-Acceptance].[Serial ID],
[Non-Acceptance].Country, IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO, [Non-Acceptance].[Customer Problem]
FROM PO RIGHT JOIN (IP RIGHT JOIN [Non-Acceptance] ON IP.[Serial ID] =
[Non-Acceptance].[Serial ID]) ON PO.[Serial ID] = [Non-Acceptance].[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

I also need to filter by assigned to so that the query only shows the blank
Agent ID's and not those that have been assigned (Assigned To: Agent ID).
However, when I enter No into this criteria it cuts out more records than it
should and brings up only those records with a N in the IP field.

Does anyone know how to make this work?

Many Thanks,
Matt
 
Since you have an OR in your WHERE Clause, you will need to add the AgentID
filtering to both sides of the OR as an AND.
 
SELECT [Non-Acceptance].[Request ID], [Non-Acceptance].[First Name],
[Non-Acceptance].Surname, [Non-Acceptance].[Invoiced?],
[Non-Acceptance].[Agent ID] AS [Assigned To], [Non-Acceptance].[Serial ID],
[Non-Acceptance].Country, IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO, [Non-Acceptance].[Customer Problem]
FROM PO RIGHT JOIN (IP RIGHT JOIN [Non-Acceptance] ON IP.[Serial ID] =
[Non-Acceptance].[Serial ID]) ON PO.[Serial ID] = [Non-Acceptance].[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) AND
(([Non-Acceptance].[Agent ID])="IsNull") OR (((IIf(IsNull([PO].[Serial
ID]),'N','Y'))="Y")) AND (([Non-Acceptance].[Agent ID])="IsNull")

Is that what you were implying? This brings up no records at all if i work
with this!

Thanks,
Matt

Klatuu said:
Since you have an OR in your WHERE Clause, you will need to add the AgentID
filtering to both sides of the OR as an AND.

Matt Dawson said:
I currently have a query with the following SQL:

SELECT [Non-Acceptance].[Request ID], [Non-Acceptance].[First Name],
[Non-Acceptance].Surname, [Non-Acceptance].[Invoiced?],
[Non-Acceptance].[Agent ID] AS [Assigned To], [Non-Acceptance].[Serial ID],
[Non-Acceptance].Country, IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO, [Non-Acceptance].[Customer Problem]
FROM PO RIGHT JOIN (IP RIGHT JOIN [Non-Acceptance] ON IP.[Serial ID] =
[Non-Acceptance].[Serial ID]) ON PO.[Serial ID] = [Non-Acceptance].[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

I also need to filter by assigned to so that the query only shows the blank
Agent ID's and not those that have been assigned (Assigned To: Agent ID).
However, when I enter No into this criteria it cuts out more records than it
should and brings up only those records with a N in the IP field.

Does anyone know how to make this work?

Many Thanks,
Matt
Click to expand...
Click to expand...
 
Just a Syntax problem, I think. No variable, (Unless intentionally populated
that way) will return a value of "IsNull"

(([Non-Acceptance].[Agent ID]) Is Null)


In SQL, the syntax is SomeField Is Null
or
SomeField Is Not Null

Don't confuse VBA with SQL where the syntax would be

IsNull(SomeField)
or

Not IsNull(SomeField)

Matt Dawson said:
SELECT [Non-Acceptance].[Request ID], [Non-Acceptance].[First Name],
[Non-Acceptance].Surname, [Non-Acceptance].[Invoiced?],
[Non-Acceptance].[Agent ID] AS [Assigned To], [Non-Acceptance].[Serial ID],
[Non-Acceptance].Country, IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO, [Non-Acceptance].[Customer Problem]
FROM PO RIGHT JOIN (IP RIGHT JOIN [Non-Acceptance] ON IP.[Serial ID] =
[Non-Acceptance].[Serial ID]) ON PO.[Serial ID] = [Non-Acceptance].[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) AND
(([Non-Acceptance].[Agent ID])="IsNull") OR (((IIf(IsNull([PO].[Serial
ID]),'N','Y'))="Y")) AND (([Non-Acceptance].[Agent ID])="IsNull")

Is that what you were implying? This brings up no records at all if i work
with this!

Thanks,
Matt

Klatuu said:
Since you have an OR in your WHERE Clause, you will need to add the AgentID
filtering to both sides of the OR as an AND.

Matt Dawson said:
I currently have a query with the following SQL:

SELECT [Non-Acceptance].[Request ID], [Non-Acceptance].[First Name],
[Non-Acceptance].Surname, [Non-Acceptance].[Invoiced?],
[Non-Acceptance].[Agent ID] AS [Assigned To], [Non-Acceptance].[Serial ID],
[Non-Acceptance].Country, IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO, [Non-Acceptance].[Customer Problem]
FROM PO RIGHT JOIN (IP RIGHT JOIN [Non-Acceptance] ON IP.[Serial ID] =
[Non-Acceptance].[Serial ID]) ON PO.[Serial ID] = [Non-Acceptance].[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

I also need to filter by assigned to so that the query only shows the blank
Agent ID's and not those that have been assigned (Assigned To: Agent ID).
However, when I enter No into this criteria it cuts out more records than it
should and brings up only those records with a N in the IP field.

Does anyone know how to make this work?

Many Thanks,
Matt
Click to expand...
Click to expand...
Click to expand...
 
Klatuu,

Sorry, you have confused me now. WHat is the actual SQL for that line i will
need then.

Matt

Klatuu said:
Just a Syntax problem, I think. No variable, (Unless intentionally populated
that way) will return a value of "IsNull"

(([Non-Acceptance].[Agent ID]) Is Null)


In SQL, the syntax is SomeField Is Null
or
SomeField Is Not Null

Don't confuse VBA with SQL where the syntax would be

IsNull(SomeField)
or

Not IsNull(SomeField)

Matt Dawson said:
SELECT [Non-Acceptance].[Request ID], [Non-Acceptance].[First Name],
[Non-Acceptance].Surname, [Non-Acceptance].[Invoiced?],
[Non-Acceptance].[Agent ID] AS [Assigned To], [Non-Acceptance].[Serial ID],
[Non-Acceptance].Country, IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO, [Non-Acceptance].[Customer Problem]
FROM PO RIGHT JOIN (IP RIGHT JOIN [Non-Acceptance] ON IP.[Serial ID] =
[Non-Acceptance].[Serial ID]) ON PO.[Serial ID] = [Non-Acceptance].[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) AND
(([Non-Acceptance].[Agent ID])="IsNull") OR (((IIf(IsNull([PO].[Serial
ID]),'N','Y'))="Y")) AND (([Non-Acceptance].[Agent ID])="IsNull")

Is that what you were implying? This brings up no records at all if i work
with this!

Thanks,
Matt

Klatuu said:
Since you have an OR in your WHERE Clause, you will need to add the AgentID
filtering to both sides of the OR as an AND.

:

I currently have a query with the following SQL:

SELECT [Non-Acceptance].[Request ID], [Non-Acceptance].[First Name],
[Non-Acceptance].Surname, [Non-Acceptance].[Invoiced?],
[Non-Acceptance].[Agent ID] AS [Assigned To], [Non-Acceptance].[Serial ID],
[Non-Acceptance].Country, IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO, [Non-Acceptance].[Customer Problem]
FROM PO RIGHT JOIN (IP RIGHT JOIN [Non-Acceptance] ON IP.[Serial ID] =
[Non-Acceptance].[Serial ID]) ON PO.[Serial ID] = [Non-Acceptance].[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

I also need to filter by assigned to so that the query only shows the blank
Agent ID's and not those that have been assigned (Assigned To: Agent ID).
However, when I enter No into this criteria it cuts out more records than it
should and brings up only those records with a N in the IP field.

Does anyone know how to make this work?

Many Thanks,
Matt
Click to expand...
Click to expand...
Click to expand...
 
I think this will do it, but I can't test it here.
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) AND
(([Non-Acceptance].[Agent ID]) Is Null) OR (((IIf(IsNull([PO].[Serial
ID]),'N','Y'))="Y")) AND (([Non-Acceptance].[Agent ID]) Is Null")


Matt Dawson said:
Klatuu,

Sorry, you have confused me now. WHat is the actual SQL for that line i will
need then.

Matt

Klatuu said:
Just a Syntax problem, I think. No variable, (Unless intentionally populated
that way) will return a value of "IsNull"

(([Non-Acceptance].[Agent ID]) Is Null)


In SQL, the syntax is SomeField Is Null
or
SomeField Is Not Null

Don't confuse VBA with SQL where the syntax would be

IsNull(SomeField)
or

Not IsNull(SomeField)

Matt Dawson said:
SELECT [Non-Acceptance].[Request ID], [Non-Acceptance].[First Name],
[Non-Acceptance].Surname, [Non-Acceptance].[Invoiced?],
[Non-Acceptance].[Agent ID] AS [Assigned To], [Non-Acceptance].[Serial ID],
[Non-Acceptance].Country, IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO, [Non-Acceptance].[Customer Problem]
FROM PO RIGHT JOIN (IP RIGHT JOIN [Non-Acceptance] ON IP.[Serial ID] =
[Non-Acceptance].[Serial ID]) ON PO.[Serial ID] = [Non-Acceptance].[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) AND
(([Non-Acceptance].[Agent ID])="IsNull") OR (((IIf(IsNull([PO].[Serial
ID]),'N','Y'))="Y")) AND (([Non-Acceptance].[Agent ID])="IsNull")

Is that what you were implying? This brings up no records at all if i work
with this!

Thanks,
Matt

:

Since you have an OR in your WHERE Clause, you will need to add the AgentID
filtering to both sides of the OR as an AND.

:

I currently have a query with the following SQL:

SELECT [Non-Acceptance].[Request ID], [Non-Acceptance].[First Name],
[Non-Acceptance].Surname, [Non-Acceptance].[Invoiced?],
[Non-Acceptance].[Agent ID] AS [Assigned To], [Non-Acceptance].[Serial ID],
[Non-Acceptance].Country, IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP,
IIf(IsNull(PO.[Serial ID]),'N','Y') AS PO, [Non-Acceptance].[Customer Problem]
FROM PO RIGHT JOIN (IP RIGHT JOIN [Non-Acceptance] ON IP.[Serial ID] =
[Non-Acceptance].[Serial ID]) ON PO.[Serial ID] = [Non-Acceptance].[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

I also need to filter by assigned to so that the query only shows the blank
Agent ID's and not those that have been assigned (Assigned To: Agent ID).
However, when I enter No into this criteria it cuts out more records than it
should and brings up only those records with a N in the IP field.

Does anyone know how to make this work?

Many Thanks,
Matt
Click to expand...
Click to expand...
Click to expand...
 
Back
Top