Query criteria problem

M

Matt Dawson

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
 
K

Klatuu

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.
 
M

Matt Dawson

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...
 
K

Klatuu

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...
 
M

Matt Dawson

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...
 
K

Klatuu

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...
 

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

Counts 2
Cannot determine casue of overflow error 1
Need to Make Query Run Faster - SLQ? 3
query error 12
Too Few parameters. Expected 2. (3061) - Northwind DB - Please Help 7
Delete Query 3
no criteria 3
Query help 5

Top