Multiple Criteria Query using a form

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I am having trouble building a query that has mulitple
criterias that are inputted via a form
i.e. I want the query to return documents that were sent
on a particular day, by a particualar person and for the
attention of a particular group. Every time I use the
criteria builder but the query returns no results. What
is the code for this.

Cheers

Nick
 
Hi Nick

use the query builder to build the query again then go into SQL view (first
icon on the left of the toolbar - click on drop down arrow) & copy & paste
the content into a post for us to have a look at.

Cheers
JulieD
 
SELECT Deliverables.ID, Deliverables.[Date Received],
Deliverables.Subject, Deliverables.[Message Summary],
Deliverables.From, Deliverables.FAO, Deliverables.Owner,
Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery,
Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.ID)=[Forms]![Deliverables]![ID] Or
(Deliverables.ID) Is Null) AND ((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received] Or
(Deliverables.[Date Received]) Is Null) AND
((Deliverables.From)=[Forms]![Deliverables]![From] Or
(Deliverables.From) Is Null) AND ((Deliverables.FAO)=
[Forms]![Deliverables]![FAO] Or (Deliverables.FAO) Is
Null));

Thanks for this Julia
 
Hi Nick

bit concerned that your ID field in the deliverables table could be null
!?!?

however, here's a query that should work for you - there might be a neater
solution, but this one should work - , notice i've omitted the ID field from
the where clause:

SELECT Deliverables.ID, Deliverables.[Date Received], Deliverables.Subject,
Deliverables.[Message Summary], Deliverables.From, Deliverables.FAO,
Deliverables.Owner, Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery, Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.[Date Received])=[Forms]![Deliverables]![Date
Received]) AND ((Deliverables.From)=[Forms]![Deliverables]![From]) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR (((Deliverables.[Date
Received]) Is Null) AND ((Deliverables.From)=[Forms]![Deliverables]![From])
AND ((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR
(((Deliverables.[Date Received])=[Forms]![Deliverables]![Date Received]) AND
((Deliverables.From) Is Null) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR (((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received]) AND
((Deliverables.From)=[Forms]![Deliverables]![From]) AND ((Deliverables.FAO)
Is Null));

Hope this helps
Cheers
JulieD

Nick said:
SELECT Deliverables.ID, Deliverables.[Date Received],
Deliverables.Subject, Deliverables.[Message Summary],
Deliverables.From, Deliverables.FAO, Deliverables.Owner,
Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery,
Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.ID)=[Forms]![Deliverables]![ID] Or
(Deliverables.ID) Is Null) AND ((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received] Or
(Deliverables.[Date Received]) Is Null) AND
((Deliverables.From)=[Forms]![Deliverables]![From] Or
(Deliverables.From) Is Null) AND ((Deliverables.FAO)=
[Forms]![Deliverables]![FAO] Or (Deliverables.FAO) Is
Null));

Thanks for this Julia
-----Original Message-----
Hi Nick

use the query builder to build the query again then go into SQL view (first
icon on the left of the toolbar - click on drop down arrow) & copy & paste
the content into a post for us to have a look at.

Cheers
JulieD





.
Click to expand...
Click to expand...
 
I have pasted the SQL in but it still is returning no
records. With this code do all the fields have to have an
entry inputted? It looks that way but still there is a
problem

cheers for your help
-----Original Message-----
Hi Nick

bit concerned that your ID field in the deliverables table could be null
!?!?

however, here's a query that should work for you - there might be a neater
solution, but this one should work - , notice i've omitted the ID field from
the where clause:

SELECT Deliverables.ID, Deliverables.[Date Received], Deliverables.Subject,
Deliverables.[Message Summary], Deliverables.From, Deliverables.FAO,
Deliverables.Owner, Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery, Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.[Date Received])=[Forms]! [Deliverables]![Date
Received]) AND ((Deliverables.From)=[Forms]! [Deliverables]![From]) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR (((Deliverables.[Date
Received]) Is Null) AND ((Deliverables.From)=[Forms]! [Deliverables]![From])
AND ((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR
(((Deliverables.[Date Received])=[Forms]![Deliverables]! [Date Received]) AND
((Deliverables.From) Is Null) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR (((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received]) AND
((Deliverables.From)=[Forms]![Deliverables]![From]) AND ((Deliverables.FAO)
Is Null));

Hope this helps
Cheers
JulieD

SELECT Deliverables.ID, Deliverables.[Date Received],
Deliverables.Subject, Deliverables.[Message Summary],
Deliverables.From, Deliverables.FAO, Deliverables.Owner,
Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery,
Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.ID)=[Forms]![Deliverables]![ID] Or
(Deliverables.ID) Is Null) AND ((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received] Or
(Deliverables.[Date Received]) Is Null) AND
((Deliverables.From)=[Forms]![Deliverables]![From] Or
(Deliverables.From) Is Null) AND ((Deliverables.FAO)=
[Forms]![Deliverables]![FAO] Or (Deliverables.FAO) Is
Null));

Thanks for this Julia
-----Original Message-----
Hi Nick

use the query builder to build the query again then go into SQL view (first
icon on the left of the toolbar - click on drop down arrow) & copy & paste
the content into a post for us to have a look at.

Cheers
JulieD


I am having trouble building a query that has mulitple
criterias that are inputted via a form
i.e. I want the query to return documents that were sent
on a particular day, by a particualar person and for the
attention of a particular group. Every time I use the
criteria builder but the query returns no results. What
is the code for this.

Cheers

Nick


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Nick

how are you running the query? the form needs to be open when you are
running the query
i based a report on the query and put a button on the form to create the
report once the selections were made and it worked fine for me.

give this a go, if it doesn't work, compact & zip the db & email it to me
and i'll have a look

Cheers
JulieD


Nick said:
I have pasted the SQL in but it still is returning no
records. With this code do all the fields have to have an
entry inputted? It looks that way but still there is a
problem

cheers for your help
-----Original Message-----
Hi Nick

bit concerned that your ID field in the deliverables table could be null
!?!?

however, here's a query that should work for you - there might be a neater
solution, but this one should work - , notice i've omitted the ID field from
the where clause:

SELECT Deliverables.ID, Deliverables.[Date Received], Deliverables.Subject,
Deliverables.[Message Summary], Deliverables.From, Deliverables.FAO,
Deliverables.Owner, Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery, Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.[Date Received])=[Forms]! [Deliverables]![Date
Received]) AND ((Deliverables.From)=[Forms]! [Deliverables]![From]) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR (((Deliverables.[Date
Received]) Is Null) AND ((Deliverables.From)=[Forms]! [Deliverables]![From])
AND ((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR
(((Deliverables.[Date Received])=[Forms]![Deliverables]! [Date Received]) AND
((Deliverables.From) Is Null) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR (((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received]) AND
((Deliverables.From)=[Forms]![Deliverables]![From]) AND ((Deliverables.FAO)
Is Null));

Hope this helps
Cheers
JulieD

SELECT Deliverables.ID, Deliverables.[Date Received],
Deliverables.Subject, Deliverables.[Message Summary],
Deliverables.From, Deliverables.FAO, Deliverables.Owner,
Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery,
Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.ID)=[Forms]![Deliverables]![ID] Or
(Deliverables.ID) Is Null) AND ((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received] Or
(Deliverables.[Date Received]) Is Null) AND
((Deliverables.From)=[Forms]![Deliverables]![From] Or
(Deliverables.From) Is Null) AND ((Deliverables.FAO)=
[Forms]![Deliverables]![FAO] Or (Deliverables.FAO) Is
Null));

Thanks for this Julia

-----Original Message-----
Hi Nick

use the query builder to build the query again then go
into SQL view (first
icon on the left of the toolbar - click on drop down
arrow) & copy & paste
the content into a post for us to have a look at.

Cheers
JulieD


message
I am having trouble building a query that has mulitple
criterias that are inputted via a form
i.e. I want the query to return documents that were
sent
on a particular day, by a particualar person and for
the
attention of a particular group. Every time I use the
criteria builder but the query returns no results. What
is the code for this.

Cheers

Nick


.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Julie

I have tried to email you but for some reason your email
address is not being accepted, have you another one i
could use

many Thanks

Nick
-----Original Message-----
Hi Nick

how are you running the query? the form needs to be open when you are
running the query
i based a report on the query and put a button on the form to create the
report once the selections were made and it worked fine for me.

give this a go, if it doesn't work, compact & zip the db & email it to me
and i'll have a look

Cheers
JulieD


I have pasted the SQL in but it still is returning no
records. With this code do all the fields have to have an
entry inputted? It looks that way but still there is a
problem

cheers for your help
-----Original Message-----
Hi Nick

bit concerned that your ID field in the deliverables table could be null
!?!?

however, here's a query that should work for you -
Click to expand...
there
might be a neater
solution, but this one should work - , notice i've omitted the ID field from
the where clause:

SELECT Deliverables.ID, Deliverables.[Date Received], Deliverables.Subject,
Deliverables.[Message Summary], Deliverables.From, Deliverables.FAO,
Deliverables.Owner, Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery, Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.[Date Received])=[Forms]! [Deliverables]![Date
Received]) AND ((Deliverables.From)=[Forms]! [Deliverables]![From]) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR (((Deliverables.[Date
Received]) Is Null) AND ((Deliverables.From)=[Forms]! [Deliverables]![From])
AND ((Deliverables.FAO)=[Forms]![Deliverables]! [FAO])) OR
(((Deliverables.[Date Received])=[Forms]!
Click to expand...
[Deliverables]!
[Date Received]) AND
((Deliverables.From) Is Null) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR (((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received]) AND
((Deliverables.From)=[Forms]![Deliverables]![From])
Click to expand...
AND
((Deliverables.FAO)
Is Null));

Hope this helps
Cheers
JulieD

SELECT Deliverables.ID, Deliverables.[Date Received],
Deliverables.Subject, Deliverables.[Message Summary],
Deliverables.From, Deliverables.FAO, Deliverables.Owner,
Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery,
Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.ID)=[Forms]![Deliverables]!
Click to expand...
[ID]
Or
(Deliverables.ID) Is Null) AND ((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received] Or
(Deliverables.[Date Received]) Is Null) AND
((Deliverables.From)=[Forms]![Deliverables]![From] Or
(Deliverables.From) Is Null) AND ((Deliverables.FAO) =
[Forms]![Deliverables]![FAO] Or (Deliverables.FAO) Is
Null));

Thanks for this Julia

-----Original Message-----
Hi Nick

use the query builder to build the query again then go
into SQL view (first
icon on the left of the toolbar - click on drop down
arrow) & copy & paste
the content into a post for us to have a look at.

Cheers
JulieD


message
I am having trouble building a query that has mulitple
criterias that are inputted via a form
i.e. I want the query to return documents that were
sent
on a particular day, by a particualar person and for
the
attention of a particular group. Every time I use the
criteria builder but the query returns no
Click to expand...
results.
What
is the code for this.

Cheers

Nick


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Nick

you need to take the "remove this" out from my email address.
if that still doesn't work, reverse the order of this email address and use
it com.techie@hartley


Cheers
JulieD

Hi Julie

I have tried to email you but for some reason your email
address is not being accepted, have you another one i
could use

many Thanks

Nick
-----Original Message-----
Hi Nick

how are you running the query? the form needs to be open when you are
running the query
i based a report on the query and put a button on the form to create the
report once the selections were made and it worked fine for me.

give this a go, if it doesn't work, compact & zip the db & email it to me
and i'll have a look

Cheers
JulieD


I have pasted the SQL in but it still is returning no
records. With this code do all the fields have to have an
entry inputted? It looks that way but still there is a
problem

cheers for your help
-----Original Message-----
Hi Nick

bit concerned that your ID field in the deliverables
table could be null
!?!?

however, here's a query that should work for you - there
might be a neater
solution, but this one should work - , notice i've
omitted the ID field from
the where clause:

SELECT Deliverables.ID, Deliverables.[Date Received],
Deliverables.Subject,
Deliverables.[Message Summary], Deliverables.From,
Deliverables.FAO,
Deliverables.Owner, Deliverables.[Due Date],
Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery,
Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.[Date Received])=[Forms]!
[Deliverables]![Date
Received]) AND ((Deliverables.From)=[Forms]!
[Deliverables]![From]) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR
(((Deliverables.[Date
Received]) Is Null) AND ((Deliverables.From)=[Forms]!
[Deliverables]![From])
AND ((Deliverables.FAO)=[Forms]![Deliverables]! [FAO])) OR
(((Deliverables.[Date Received])=[Forms]! [Deliverables]!
[Date Received]) AND
((Deliverables.From) Is Null) AND
((Deliverables.FAO)=[Forms]![Deliverables]![FAO])) OR
(((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received]) AND
((Deliverables.From)=[Forms]![Deliverables]![From]) AND
((Deliverables.FAO)
Is Null));

Hope this helps
Cheers
JulieD

message
SELECT Deliverables.ID, Deliverables.[Date Received],
Deliverables.Subject, Deliverables.[Message Summary],
Deliverables.From, Deliverables.FAO,
Deliverables.Owner,
Deliverables.[Due Date], Deliverables.Status,
Deliverables.Comment, Deliverables.Delivery,
Deliverables.Attachments
FROM Deliverables
WHERE (((Deliverables.ID)=[Forms]![Deliverables]! [ID]
Or
(Deliverables.ID) Is Null) AND ((Deliverables.[Date
Received])=[Forms]![Deliverables]![Date Received] Or
(Deliverables.[Date Received]) Is Null) AND
((Deliverables.From)=[Forms]![Deliverables]![From] Or
(Deliverables.From) Is Null) AND ((Deliverables.FAO) =
[Forms]![Deliverables]![FAO] Or (Deliverables.FAO) Is
Null));

Thanks for this Julia

-----Original Message-----
Hi Nick

use the query builder to build the query again then go
into SQL view (first
icon on the left of the toolbar - click on drop down
arrow) & copy & paste
the content into a post for us to have a look at.

Cheers
JulieD


message
I am having trouble building a query that has
mulitple
criterias that are inputted via a form
i.e. I want the query to return documents that were
sent
on a particular day, by a particualar person and for
the
attention of a particular group. Every time I use
the
criteria builder but the query returns no results.
What
is the code for this.

Cheers

Nick


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top