Multimple User Input in Query using Criteria

[mukeshhtrivedi]

Hi Gurus,

I have created simple query as

SELECT QUOTES.DATE, QUOTES.QUOTENO, QUOTES.SALESREP, QuoteItem.ITEM,
QUOTES.[JOB NAME], QUOTES.CUSTOMER, QUOTES.AMOUNT, QUOTES.[JOB NO],
QUOTES.DivisionID, QUOTES.STATUS
FROM QUOTES INNER JOIN QuoteItem ON QUOTES.QUOTENO = QuoteItem.QUOTENO
WHERE (((QUOTES.DATE) Between [enter the start date] And [enter the end
date]) AND ((QUOTES.SALESREP)=[enter the SALESREP]) AND
((QuoteItem.ITEM)=[enter the ITEM]))
ORDER BY QuoteItem.ITEM;

Now what I want to do is when I run this query it should let me input
more than one ITEM so that when I want to get report for more than one
ITEM. For exaple I want to see report of three ITEMs it should ask me
in pop up box 3 times so that I can input and IF I input just one item
than it should give me report of one item (presently I am able to input
one ITEM and it works).

Thanks in advance.

[Tom van Stiphout]

On 17 Jan 2007 13:12:55 -0800, mu************@gmail.com wrote:

You could do something like this (using Northwind sample app):
SELECT Customers.*
FROM Customers
WHERE (((Customers.CustomerID)=[Give CustomerID1])) OR
(((Customers.CustomerID)=[Give CustomerID2]));

That's pretty ugly though. Much more elegant would be to use an IN
clause:
SELECT Customers.*
FROM Customers
WHERE (((Customers.CustomerID) IN ([Give CustomerIDs]));

Then you could enter as many comma-separated values as you wanted.
However, that doesn't work. MSFT is apparently unwilling to support
that. Next best thing is for you to compose this SQL string yourself,
and use it for your form or report.

Another suggestion is to use a form rather than all these popups. Then
for example the SalesRepID can come from a dropdown list of all
salesreps, and there is no chance of mistakes.

-Tom.

>Hi Gurus,

I have created simple query as

SELECT QUOTES.DATE, QUOTES.QUOTENO, QUOTES.SALESREP, QuoteItem.ITEM,
QUOTES.[JOB NAME], QUOTES.CUSTOMER, QUOTES.AMOUNT, QUOTES.[JOB NO],
QUOTES.DivisionID, QUOTES.STATUS
FROM QUOTES INNER JOIN QuoteItem ON QUOTES.QUOTENO = QuoteItem.QUOTENO
WHERE (((QUOTES.DATE) Between [enter the start date] And [enter the end
date]) AND ((QUOTES.SALESREP)=[enter the SALESREP]) AND
((QuoteItem.ITEM)=[enter the ITEM]))
ORDER BY QuoteItem.ITEM;

Now what I want to do is when I run this query it should let me input
more than one ITEM so that when I want to get report for more than one
ITEM. For exaple I want to see report of three ITEMs it should ask me
in pop up box 3 times so that I can input and IF I input just one item
than it should give me report of one item (presently I am able to input
one ITEM and it works).

Thanks in advance.

[mukeshhtrivedi]

Thanks Tom for oyur prompt response.

I am not expert in MS Access so I don't know how to create form for
report. So I just wish to get syntax for criteria which can let me
input more than one time. Can you palease guide me how can I make this
possible in following.

((QuoteItem.ITEM)=[enter the ITEM]))

Thanks

Tom van Stiphout wrote:

On 17 Jan 2007 13:12:55 -0800, mu************@gmail.com wrote:

You could do something like this (using Northwind sample app):
SELECT Customers.*
FROM Customers
WHERE (((Customers.CustomerID)=[Give CustomerID1])) OR
(((Customers.CustomerID)=[Give CustomerID2]));

That's pretty ugly though. Much more elegant would be to use an IN
clause:
SELECT Customers.*
FROM Customers
WHERE (((Customers.CustomerID) IN ([Give CustomerIDs]));

Then you could enter as many comma-separated values as you wanted.
However, that doesn't work. MSFT is apparently unwilling to support
that. Next best thing is for you to compose this SQL string yourself,
and use it for your form or report.

Another suggestion is to use a form rather than all these popups. Then
for example the SalesRepID can come from a dropdown list of all
salesreps, and there is no chance of mistakes.

-Tom.

Hi Gurus,

I have created simple query as

SELECT QUOTES.DATE, QUOTES.QUOTENO, QUOTES.SALESREP, QuoteItem.ITEM,
QUOTES.[JOB NAME], QUOTES.CUSTOMER, QUOTES.AMOUNT, QUOTES.[JOB NO],
QUOTES.DivisionID, QUOTES.STATUS
FROM QUOTES INNER JOIN QuoteItem ON QUOTES.QUOTENO = QuoteItem.QUOTENO
WHERE (((QUOTES.DATE) Between [enter the start date] And [enter the end
date]) AND ((QUOTES.SALESREP)=[enter the SALESREP]) AND
((QuoteItem.ITEM)=[enter the ITEM]))
ORDER BY QuoteItem.ITEM;

Now what I want to do is when I run this query it should let me input
more than one ITEM so that when I want to get report for more than one
ITEM. For exaple I want to see report of three ITEMs it should ask me
in pop up box 3 times so that I can input and IF I input just one item
than it should give me report of one item (presently I am able to input
one ITEM and it works).

Thanks in advance.

[Tom van Stiphout]

On 18 Jan 2007 06:52:53 -0800, mu************@gmail.com wrote:

Isn't that what I did in my first suggestion: use an OR clause to
string several parts together. Perhaps this is clearer:
WHERE (((QUOTES.DATE) Between [enter the start date] And [enter the
end date]) AND ((QUOTES.SALESREP)=[enter the SALESREP]) AND
(((QuoteItem.ITEM)=[enter first ITEM])) OR ((QuoteItem.ITEM)=[enter
second ITEM])))
etc.

-Tom.

>Thanks Tom for oyur prompt response.

I am not expert in MS Access so I don't know how to create form for
report. So I just wish to get syntax for criteria which can let me
input more than one time. Can you palease guide me how can I make this
possible in following.

((QuoteItem.ITEM)=[enter the ITEM]))

Thanks

Tom van Stiphout wrote:

>On 17 Jan 2007 13:12:55 -0800, mu************@gmail.com wrote:

You could do something like this (using Northwind sample app):
SELECT Customers.*
FROM Customers
WHERE (((Customers.CustomerID)=[Give CustomerID1])) OR
(((Customers.CustomerID)=[Give CustomerID2]));

That's pretty ugly though. Much more elegant would be to use an IN
clause:
SELECT Customers.*
FROM Customers
WHERE (((Customers.CustomerID) IN ([Give CustomerIDs]));

Then you could enter as many comma-separated values as you wanted.
However, that doesn't work. MSFT is apparently unwilling to support
that. Next best thing is for you to compose this SQL string yourself,
and use it for your form or report.

Another suggestion is to use a form rather than all these popups. Then
for example the SalesRepID can come from a dropdown list of all
salesreps, and there is no chance of mistakes.

-Tom.

>Hi Gurus,

I have created simple query as

SELECT QUOTES.DATE, QUOTES.QUOTENO, QUOTES.SALESREP, QuoteItem.ITEM,
QUOTES.[JOB NAME], QUOTES.CUSTOMER, QUOTES.AMOUNT, QUOTES.[JOB NO],
QUOTES.DivisionID, QUOTES.STATUS
FROM QUOTES INNER JOIN QuoteItem ON QUOTES.QUOTENO = QuoteItem.QUOTENO
WHERE (((QUOTES.DATE) Between [enter the start date] And [enter the end
date]) AND ((QUOTES.SALESREP)=[enter the SALESREP]) AND
((QuoteItem.ITEM)=[enter the ITEM]))
ORDER BY QuoteItem.ITEM;

Now what I want to do is when I run this query it should let me input
more than one ITEM so that when I want to get report for more than one
ITEM. For exaple I want to see report of three ITEMs it should ask me
in pop up box 3 times so that I can input and IF I input just one item
than it should give me report of one item (presently I am able to input
one ITEM and it works).

Thanks in advance.